Question for an expert Transact SQL GURU!
-
Has anyone ever figured out a way to use "sp_executesql" on a stored procedure that returns an output parameter. It seems as though when you execute "sp_executesql" SQL server compiles the syntax using a different profile. I can't seem to access any variables declared in the SQL string (even using an optional declaration parameter) nor can I access any variables declared within a stored procedure from within the "sp_executesql" string. It just doesn't seem possible. Anyone got any creative ideas? example: sp_executesql "exec sp_name @varname output" or: sp_executesql "exec sp_name @varname output", @varname int ~LizardWiz()
-
Has anyone ever figured out a way to use "sp_executesql" on a stored procedure that returns an output parameter. It seems as though when you execute "sp_executesql" SQL server compiles the syntax using a different profile. I can't seem to access any variables declared in the SQL string (even using an optional declaration parameter) nor can I access any variables declared within a stored procedure from within the "sp_executesql" string. It just doesn't seem possible. Anyone got any creative ideas? example: sp_executesql "exec sp_name @varname output" or: sp_executesql "exec sp_name @varname output", @varname int ~LizardWiz()
acording to the SQLServer BOL, that can't be done, variables used within the query being executed by sp_executesql are local to the query therefore they can't be accessed by the query that executed it in the first place i hope i made myself clear with that :|
-
Has anyone ever figured out a way to use "sp_executesql" on a stored procedure that returns an output parameter. It seems as though when you execute "sp_executesql" SQL server compiles the syntax using a different profile. I can't seem to access any variables declared in the SQL string (even using an optional declaration parameter) nor can I access any variables declared within a stored procedure from within the "sp_executesql" string. It just doesn't seem possible. Anyone got any creative ideas? example: sp_executesql "exec sp_name @varname output" or: sp_executesql "exec sp_name @varname output", @varname int ~LizardWiz()
Actually you can do it. :cool: What you are missing is the that it needs to be an nvarchar. this is from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q262499 \CREATE PROCEDURE Myproc @parm varchar(10), @parm1OUT varchar(30) OUTPUT, @parm2OUT varchar(30) OUTPUT AS SELECT @parm1OUT='parm 1' + @parm SELECT @parm2OUT='parm 2' + @parm GO DECLARE @SQLString NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @parmIN VARCHAR(10) DECLARE @parmRET1 VARCHAR(30) DECLARE @parmRET2 VARCHAR(30) SET @parmIN=' returned' SET @SQLString=N'EXEC Myproc @parm, @parm1OUT OUTPUT, @parm2OUT OUTPUT' SET @ParmDefinition=N'@parm varchar(10), @parm1OUT varchar(30) OUTPUT, @parm2OUT varchar(30) OUTPUT' EXECUTE sp_executesql @SQLString, @ParmDefinition, @parm=@parmIN, @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2" go drop procedure Myproc Barbara, MCP
-
Actually you can do it. :cool: What you are missing is the that it needs to be an nvarchar. this is from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q262499 \CREATE PROCEDURE Myproc @parm varchar(10), @parm1OUT varchar(30) OUTPUT, @parm2OUT varchar(30) OUTPUT AS SELECT @parm1OUT='parm 1' + @parm SELECT @parm2OUT='parm 2' + @parm GO DECLARE @SQLString NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @parmIN VARCHAR(10) DECLARE @parmRET1 VARCHAR(30) DECLARE @parmRET2 VARCHAR(30) SET @parmIN=' returned' SET @SQLString=N'EXEC Myproc @parm, @parm1OUT OUTPUT, @parm2OUT OUTPUT' SET @ParmDefinition=N'@parm varchar(10), @parm1OUT varchar(30) OUTPUT, @parm2OUT varchar(30) OUTPUT' EXECUTE sp_executesql @SQLString, @ParmDefinition, @parm=@parmIN, @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2" go drop procedure Myproc Barbara, MCP
-
Actually you can do it. :cool: What you are missing is the that it needs to be an nvarchar. this is from http://support.microsoft.com/default.aspx?scid=kb;EN-US;q262499 \CREATE PROCEDURE Myproc @parm varchar(10), @parm1OUT varchar(30) OUTPUT, @parm2OUT varchar(30) OUTPUT AS SELECT @parm1OUT='parm 1' + @parm SELECT @parm2OUT='parm 2' + @parm GO DECLARE @SQLString NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @parmIN VARCHAR(10) DECLARE @parmRET1 VARCHAR(30) DECLARE @parmRET2 VARCHAR(30) SET @parmIN=' returned' SET @SQLString=N'EXEC Myproc @parm, @parm1OUT OUTPUT, @parm2OUT OUTPUT' SET @ParmDefinition=N'@parm varchar(10), @parm1OUT varchar(30) OUTPUT, @parm2OUT varchar(30) OUTPUT' EXECUTE sp_executesql @SQLString, @ParmDefinition, @parm=@parmIN, @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2" go drop procedure Myproc Barbara, MCP
-
Has anyone ever figured out a way to use "sp_executesql" on a stored procedure that returns an output parameter. It seems as though when you execute "sp_executesql" SQL server compiles the syntax using a different profile. I can't seem to access any variables declared in the SQL string (even using an optional declaration parameter) nor can I access any variables declared within a stored procedure from within the "sp_executesql" string. It just doesn't seem possible. Anyone got any creative ideas? example: sp_executesql "exec sp_name @varname output" or: sp_executesql "exec sp_name @varname output", @varname int ~LizardWiz()
kinda nasty... but check this out http://www.sqlmag.com/Forums/messageview.cfm?catid=22&threadid=722