I created a cursor based stored procedure in a database and wish to set the value of a temp table field to the output of a stored procedure in another database. However the compiler does not like my definition when I try to save(execute) the stored procedure.
Incorrect syntax near the keyword 'exec'.
Msg 102, Level 15, State 1, Procedure ProjectAccount_Rollup, Line 205
Incorrect syntax near ')'.
Set TS_Actuals =(exec [PMA_Reporting].[dbo].[aRDC_GetProjectTSActualsForFY]@ProjID, @FY, @acctValue) WHERE CURRENT OF Acct_Cursor;
The method I am using is straight from MS examples with the exception of using fully qualified names. What is the issue?
That is invalid syntax.
You need to store the return value in a scalar variable, then you can use it in the UPDATE statement:
exec [PMA_Reporting].[dbo].[aRDC_GetProjectTSActualsForFY]@ProjID, @FY, @acctValue
What is the result of the stored procedure?
Once you figure out the output from the sproc, you can program like this:
Set TS_Actuals =@SprocResultValue WHERE CURRENT OF Acct_Cursor;
- Edited by Kalman TothModerator Tuesday, July 16, 2013 8:42 PM update
Thanks Kalman, that still produced the same error message. I am using SS 2008 R2 if that matters. I can write the external query directly in my SP using fully qualified names and it works so I may just stick with it. Though I am going to try Antoine's answer too.
I tried to utilize the function method as mentioned but are not sure how to properly write it. If I just call the function without any select statement the compiler doesn't like that and throws a multipart-identifier error.
Set TS_Actuals = (Select [PMA_Reporting].[dbo].[GetProjectTSActualsForFY(@ProjID, @FY, @acctValue)]From [PMA_Reporting].[dbo]) WHERE CURRENT OF Acct_Cursor;
It is the same syntax you would use for a getdate, charindex, ABS or any other call to a microsoft function. The only difference being you must absolutely specify the schema of the function and in your case, the database.
Set TS_Actuals=[PMA_Reporting].[dbo].[aRDC_GetProjectTSActualsForFY](@ProjID, @FY, @acctValue)
WHERE CURRENTOF Acct_Cursor;