Output parameters for Stored Proc | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Output parameters for Stored Proc

When i run the below SProc i get: Even thou i defined it as an ‘OUTPUT’
Server: Msg 201, Level 16, State 4, Procedure SP_Server, Line 0
Procedure ‘SP_Server’ expects parameter ‘@ServerOutput’, which was not supplied. Create Procedure SP_Server @ServerOutput varchar(100) OUTPUT AS Declare @Payor varchar(50),
@importstampString varchar (50),
@importstamp varchar(50),
@sqlstatement varchar(100),
@server varchar(50) Set @payor = ‘aet’
Set @Importstamp = ‘AET_2004-12-12 12:00:00’ Select @ImportstampString = substring(@Importstamp, 1, 14)
— Concatenating the SQL Command to find the Server Location
Set @SQLStatement = ‘Select Server from ‘ + @payor + ‘_global_log’
+ ‘ where substring(process_desc, 1, 14) = ‘
+ @ImportstampString — Capturing the Server Location
Exec @ServerOutput = @SQLStatement

When you execute the proc you still need to pass a variable name for it to return the results to. So: DECLARE @strOutput varchar(100) EXEC SP_server @strOutput OUTPUT PRINT @strOutput

Create Procedure SP_Server
@ServerOutput varchar(100) OUTPUT
AS
BEGIN
Declare @Payor varchar(50),
@importstampString varchar (50),
@importstamp varchar(50),
@sqlstatement nvarchar(100),
@server varchar(50) Set @payor = ‘aet’
Set @Importstamp = ‘AET_2004-12-12 12:00:00’ Select @ImportstampString = substring(@Importstamp, 1, 14) — Concatenating the SQL Command to find the Server Location
Set @SQLStatement = ‘Select @ServerOutput = Server from ‘ + @payor + ‘_global_log’
+ ‘ where substring(process_desc, 1, 14) = ‘
+ @ImportstampString — Capturing the Server Location
exec sp_executesql @SQLStatement, N’@ServerOutput varchar(100) OUTPUT’, @ServerOutput OUTPUT
END — Execute the SP
DECLARE @strOutput varchar(100)
EXEC SP_server @strOutput OUTPUT
PRINT @strOutput
KH
Also refer
http://www.nigelrivett.net/SQLTsql/sp_executesql.html Madhivanan Failing to plan is Planning to fail
]]>