CODEWITHSUNDEEP
sqlsql-server
Josh
Josh

Reputation: 3321

Error in dynamic execute with local variable

I'm getting the error :

Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'

for the following execute statement. I'm a little confused with this, after reading several posts on the website, I came to understand that I would need to pass @val as a variable since exec will execute the command as-is.

declare @val varchar(max); 
declare @query nvarchar(max);

set @query = 'set @val = (select ' + @cols + ' from temp where loan_number = ''72'')'
exec sp_executesql @query, '@val varchar(max) output', @val output

Conversely, if I executed the exec as a non-dynamic query as just exec(@query), I get the error Must declare the scalar variable "@val".

Upvotes: 1

Views: 132

Answers (1)

Thom A
Thom A

Reputation: 96016

IF the statement "Should the output of the query be a 1x1 table since I" is true, then you need to use a SELECT to set the value:

DECLARE @val varchar(MAX); 
DECALRE @query nvarchar(MAX);

SET @query = N'SELECT @val = ' + QUOTENAME(@cols) + N' FROM temp WHERE loan_number = ''72'');';
EXEC sp_executesql @query, N'@val varchar(MAX) OUTPUT', @val OUTPUT;

The fact that you have a variable called @cols implies that the variable will hold the names of many columns, not one.

Upvotes: 3

Related Questions