none
execute a stored procedure from a seperate database

    Question

  • 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 ')'.

    Update @accounts 

    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?

    Thanks!

    Tuesday, July 16, 2013 8:07 PM

All replies

  • Beruken,  are you sure the exemple you saw does not use a function rather than a stored procedure.  A function can easily be called in an update statement but not a stored procedure.
    Tuesday, July 16, 2013 8:38 PM
  • 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:

    Update @accounts 
    Set TS_Actuals =@SprocResultValue WHERE CURRENT OF Acct_Cursor;

    UPDATE blog:

    http://www.sqlusa.com/articles2005/sqlupdate/


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration



    Tuesday, July 16, 2013 8:40 PM
    Moderator
  • Antoine, The example did not address my data assignment. I considered creating a function as you mentioned. I think I will try that now.


    • Edited by Beruken Tuesday, July 16, 2013 8:54 PM update
    Tuesday, July 16, 2013 8:42 PM
  • 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.

    K.

    Tuesday, July 16, 2013 8:53 PM
  • 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;

    Wednesday, July 17, 2013 12:15 PM
  • 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.

    Try this:

    Update @accounts

    Set TS_Actuals=[PMA_Reporting].[dbo].[aRDC_GetProjectTSActualsForFY](@ProjID, @FY, @acctValue)

    WHERE CURRENTOF Acct_Cursor;


    Wednesday, July 17, 2013 1:13 PM