Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Return Value - Dynamic SQL

    I need to get a return value from a sproc. I know how to do it this way:
    Code:
    CREATE PROCEDURE A_SPROC (
         @RETVAL REAL OUTPUT 
    ) 
    AS 
    SET @RETVAL=(SELECT A_VALUE FROM A_TABLE)
    RETURN @RETVAL
    GO
    What if I am using dynamic SQL? How do I set @RETVAL?
    Inspiration Through Fermentation

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    You can do it if you use sp_executesql (which you should be doing anyway if you are using dynamic sql. It's better for many reasons, all outlined in the article below.)

    Before you put any dynamic sql code into production, please read and understand this article:

    http://sommarskog.se/dynamic_sql.html moderator edit: corrected the url

    finished? ok, here's how you do it:

    Code:
    declare @sql nvarchar(1000)
    declare @parms nvarchar(1000)
    declare @count int
    
    set @sql = N'select @cnt = count(*) from sysobjects'
    set @parms = N'@cnt int output'
    
    exec sp_executesql @stmt=@sql, @params=@parms, @cnt = @count output
    
    select @count as 'from dyn sql'
    Last edited by r937; 11-21-06 at 01:06.

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Very informative and helpful article. That get's me going in the right direction.
    Thanks
    Inspiration Through Fermentation

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's all this talk about dynamic SQL?

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc99 (
    	  @OrderId int
         	, @RETVAL REAL OUTPUT 
    ) 
    AS 
    SELECT @RETVAL = EmployeeId FROM Orders WHERE OrderId = @OrderId  
    RETURN
    GO
    
    DECLARE  @RETVAL REAL
    EXEC mySproc99 10248, @RETVAL OUTPUT
    SELECT @RETVAL
    GO
    
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    my understanding of the OP was that he wanted to know how to get an output value from a query executed with dynamic sql.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •