0

I am trying to execute a SP inside another SP. Example below. declare @deal_1 as int declare @deal_2 as int

set @deal_1 = (EXEC [my second SP] @para1 = 'xxxxx' ) --this returns single value
set @deal_2 = (some other sub query)

select @deal_1, @deal_2

My question is above should return simple two column results but I can't get this to work. Unable to save due to errors or syntax issues.

Error I am getting is "Incorrect syntax near the keyword 'EXEC'."

BenW
  • 1,393
  • 1
  • 16
  • 26
  • For subquery you can use the `Select ` syntax to set the variable. – Mahesh Mar 04 '15 at 05:29
  • Looks right at first glance. What is the exact syntax error? – Jonathan Allen Mar 04 '15 at 05:31
  • What value does your `second sp` returns ? Try this syntax `EXEC @deal_1 = storedprocedureName 'InputParameter'` – Mahesh Mar 04 '15 at 05:32
  • Try `select @deal_1, @deal_2`. Did you really omit the `@` characters in your code? – shawnt00 Mar 04 '15 at 05:41
  • I am getting "Incorrect syntax near the keyword 'EXEC'." . Issues is not with the select but executing and assign the value back to the variable. It always return int value – BenW Mar 04 '15 at 05:54

1 Answers1

0

Your select statement is unable to bind the columns deal_1 and deal_2 and there are no tables to bind to (i.e. no FROM clause in your select). Perhaps you meant to select the values of the parameters (note the @ symbols)?

select @deal_1 as deal_1, @deal_2 as deal_2
lc.
  • 113,939
  • 20
  • 158
  • 187