Search code examples
sqlsql-servert-sqlsp-executesql

sp_executesql not showing desired result

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!!

Solution

  • 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.