none
T-SQL Syntax for return values from stored procedures

    Question

  • Hi there, I cannot get this to work. Why is nothing print in the following SQL? Please note I use dynamic SQL in the stored procedure.

    if object_id('usp_Do_Some', 'P') is not null
          drop proc usp_Do_Some
    ;
    go
    
    create proc usp_Do_Some
        @RetVal as nvarchar(20) output
    as
        declare @SQLQuery as nvarchar(max)
        set @SQLQuery = ' select ' + @RetVal + ' = ''hello'';'
    
        exec (@SQLQuery) ;
    go
    
    declare @String as nvarchar(20)
    exec usp_Do_Some @RetVal = @String output;
    print @String
    
    

    Thanks in advance!

    Christian

    Friday, March 30, 2012 10:50 PM

Answers

  • Here is the corrected dynamic SQL syntax:

    IF Object_id('usp_Do_Some', 'P') IS NOT NULL 
      DROP PROC usp_do_some; 
    
    GO 
    
    CREATE PROC Usp_do_some @RetVal AS NVARCHAR(20) OUTPUT 
    AS 
      DECLARE @SQLQuery AS NVARCHAR(MAX) 
      DECLARE @ParmDefinition NVARCHAR(1024)= N'@pRetVal as nvarchar(20) output' 
    
      SET @SQLQuery = ' select @pRetVal   = ''hello'';' 
    
      EXEC Sp_executesql 
        @SQLQuery, 
        @ParmDefinition, 
        @pRetVal= @RetVal OUTPUT; 
    
    GO 
    
    DECLARE @String AS NVARCHAR(20) 
    
    EXEC Usp_do_some @RetVal = @String OUTPUT; 
    
    PRINT @String 
    
    -- hello 
    GO  

    Dynamic SQL article:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth SQL SERVER & BI TRAINING

    • Proposed as answer by Naomi NModerator Friday, March 30, 2012 11:13 PM
    • Marked as answer by chhenning Monday, April 02, 2012 1:52 PM
    Friday, March 30, 2012 11:11 PM
    Moderator

All replies

  • Here is the corrected dynamic SQL syntax:

    IF Object_id('usp_Do_Some', 'P') IS NOT NULL 
      DROP PROC usp_do_some; 
    
    GO 
    
    CREATE PROC Usp_do_some @RetVal AS NVARCHAR(20) OUTPUT 
    AS 
      DECLARE @SQLQuery AS NVARCHAR(MAX) 
      DECLARE @ParmDefinition NVARCHAR(1024)= N'@pRetVal as nvarchar(20) output' 
    
      SET @SQLQuery = ' select @pRetVal   = ''hello'';' 
    
      EXEC Sp_executesql 
        @SQLQuery, 
        @ParmDefinition, 
        @pRetVal= @RetVal OUTPUT; 
    
    GO 
    
    DECLARE @String AS NVARCHAR(20) 
    
    EXEC Usp_do_some @RetVal = @String OUTPUT; 
    
    PRINT @String 
    
    -- hello 
    GO  

    Dynamic SQL article:

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth SQL SERVER & BI TRAINING

    • Proposed as answer by Naomi NModerator Friday, March 30, 2012 11:13 PM
    • Marked as answer by chhenning Monday, April 02, 2012 1:52 PM
    Friday, March 30, 2012 11:11 PM
    Moderator
  • Thank you very much!
    Monday, April 02, 2012 1:54 PM