0

What am I missing here? If I run a print @sql statement and run that statement directly in SSMS I get the value that I am after. However, if I run the dynamic sql and try to access Print @value1 outside the dynamic sql no value is returned.

How can I access the value of @value1 from outside my dynamic sql? In my syntax below, it always returns nothing, as if the value does not exist!

Declare @Name varchar(100), @value1 varchar(100), @sql nvarchar(max)
Set @Name = 'Jalen'
set @sql = 'Declare @value1 varchar(100) Select @value1 = id 
            from openquery(192.168.500.300,''Select id 
             from massmailer where empname in ('''''+@Name+N''''')'')'
Print @value1
EXEC sp_executesql @SQL

I tried to modify my Exec statement to:

EXEC sp_executesql @SQL, @retvalOUT=@value1 OUTPUT;

This gives me an error of:

Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

3

Community Wiki answer originally left as question comments

You need:

EXEC sp_executesql
    @sql, 
    N'@value1 varchar(100) OUTPUT',
    @value1=@value1 OUTPUT;

An online demonstration of the technique: http://rextester.com/GDL49591

0

Paul White's answer is correct, but I'd also add that in the original post, the @sql is not defined correctly. The correct script should be

Declare @Name varchar(100), @value1 varchar(100), @sql nvarchar(max)
Set @Name = 'Jalen'
set @sql = ' Select @value1 = id 
            from openquery(192.168.500.300,''Select id 
             from massmailer where empname in ('''''+@Name+N''''')'')'


EXEC sp_executesql
    @sql, 
    N'@value1 varchar(100) OUTPUT',
    @value1=@value1 OUTPUT;
print @value1;

i.e. notice inside @sql, there is no 'declare @value1 varchar(100)'. If you keep it there, you will have this error

Msg 134, Level 15, State 1, Line 7

The variable name '@value1' has already been declared. Variable names must be unique within a query batch or stored procedure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.