Stacey Fontenot
asked on
issuse obtaining output with sp_executesql
I am building a stored procedure calls a scalar-valued function that I created. Example
dbo.retun_alpha( column name, parameter) , I have got to the point where I am trying to insert a value in the parameter section based on a table but am having issue get the value 01 into the function)
I am trying to use SP_ECECUTESQL below is the code
DECLARE @SQLString nvarchar(max);
DECLARE @Parm nvarchar(max);
DECLARE @Parm1 nvarchar(max);
declare @args varchar(max);
set @Parm = 'P'+ cast( @I4 as varchar(10))+' from FROM M_TABLE_VALIDATION_RULES WHERE TABLE_VALIDATION_RULES_ID = ' + cast( @TABLE_VALIDATION_RULES_ID as varchar(max)) + ')';
set @Parm1 = N'@Value nvarchar(max) OUTPUT'
set @SQLString = '(SELECT @Value = ' + @Parm
EXEC sp_executesql @SQLString, @Parm1, @Value OUTPUT
set @args = @Value
I am getting
Msg 137, Level 15, State 2, Procedure DATA_CLEANSE, Line 110 [Batch Start Line 7]
Must declare the scalar variable "@Value".
dbo.retun_alpha( column name, parameter) , I have got to the point where I am trying to insert a value in the parameter section based on a table but am having issue get the value 01 into the function)
I am trying to use SP_ECECUTESQL below is the code
DECLARE @SQLString nvarchar(max);
DECLARE @Parm nvarchar(max);
DECLARE @Parm1 nvarchar(max);
declare @args varchar(max);
set @Parm = 'P'+ cast( @I4 as varchar(10))+' from FROM M_TABLE_VALIDATION_RULES WHERE TABLE_VALIDATION_RULES_ID = ' + cast( @TABLE_VALIDATION_RULES_ID
set @Parm1 = N'@Value nvarchar(max) OUTPUT'
set @SQLString = '(SELECT @Value = ' + @Parm
EXEC sp_executesql @SQLString, @Parm1, @Value OUTPUT
set @args = @Value
I am getting
Msg 137, Level 15, State 2, Procedure DATA_CLEANSE, Line 110 [Batch Start Line 7]
Must declare the scalar variable "@Value".
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER