Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
T-SQL Syntax for return values from stored procedures

Answered T-SQL Syntax for return values from stored procedures

  • Friday, March 30, 2012 10:50 PM
     
      Has Code
    Sign In to Vote
    0

    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

     

All Replies

  • Friday, March 30, 2012 11:11 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    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

     
  • Monday, April 02, 2012 1:54 PM
     
     
    Sign In to Vote
    0
    Thank you very much!