locked
Set local var with sp RRS feed

  • Question

  • I want to use a stored proc return value to populate a local variable.  I am using the declare below but I am getting a syntax error on the EXEC. Can someone tell me how to fix this?  Thanks.

    SET @WorkerLinkID = (EXEC mc_insPeopleLink @PersonID=@WrkPersonID, @Branch=@Branch, @PeopleStart=@StartDate, @CategoryCode=2);

    Friday, October 15, 2010 9:47 PM

Answers

  • If the the WorkerLinkID is being returned by a RETURN statement, the syntax is

    EXEC @WorkerLinkID = mc_insPeopleLink @PersonID=@WrkPersonID, @Branch=@Branch, @PeopleStart=@StartDate, @CategoryCode=2

    However, the convention is that the return values is only used to indicate success/failure, and data is passed through OUTPUT parameters.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Friday, October 15, 2010 10:09 PM
  • Take a look in the BOL Execute EXECUTE - BOL Reference

    EXECUTE @WorkerLinkID = mc_insPeopleLink @PersonID=@WrkPersonID, @Branch=@Branch, @PeopleStart=@StartDate, @CategoryCode=2
    
    The @WorkerLinkID must be an integer value as procedure can only return an integer. Usually SP returns a status or error code.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, October 15, 2010 10:11 PM

All replies

  • If the the WorkerLinkID is being returned by a RETURN statement, the syntax is

    EXEC @WorkerLinkID = mc_insPeopleLink @PersonID=@WrkPersonID, @Branch=@Branch, @PeopleStart=@StartDate, @CategoryCode=2

    However, the convention is that the return values is only used to indicate success/failure, and data is passed through OUTPUT parameters.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Friday, October 15, 2010 10:09 PM
  • Take a look in the BOL Execute EXECUTE - BOL Reference

    EXECUTE @WorkerLinkID = mc_insPeopleLink @PersonID=@WrkPersonID, @Branch=@Branch, @PeopleStart=@StartDate, @CategoryCode=2
    
    The @WorkerLinkID must be an integer value as procedure can only return an integer. Usually SP returns a status or error code.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, October 15, 2010 10:11 PM