We help IT Professionals succeed at work.
Get Started

issuse obtaining output with sp_executesql

130 Views
Last Modified: 2017-05-03
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".
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE