sp_executesql not showing desired result

Question

I am working on SQL in SQL Server and come across this problem where,

set @sqlString=N'select @max=MAX('+@columnName+') from @temp';
exec sp_executesql @sqlString,
             N'@temp as Table_Type readonly, @max nvarchar(max)', @temp ,@max;

I have tested this sql statement without parameters and it was working but like this it's showing null in @max. Please help me out where am i going wrong!!

it's a variable containing column name of the table.. and @temp is a temporary variable of same table type. — Sarthak, Apr 06 '15 at 06:11

score 4 · Accepted Answer · answered Apr 06 '15 at 06:09

You need to point that the max parameter is output. Try this:

set @sqlString=N'select @max=MAX('+@columnName+') from @temp';
exec sp_executesql @sqlString,
             N'@temp as Table_Type readonly, @max nvarchar(max) output', @temp ,@max output;

You can check this example as well.

sp_executesql not showing desired result

1 Answers1