Set EXECUTE sp_executesql result into a variable in sql

Question

I have to set a dynamic sql query result to a variable. My sql query is :

DECLARE @ResultString NVARCHAR(MAX)
DECLARE @Qry NVARCHAR(MAX)  

SET @Qry='SELECT Test FROM MTest22Dec WHERE ID = 1'      
EXECUTE sp_executesql @Qry, N'@Result NVARCHAR(MAX) OUTPUT', @Result=@ResultString OUTPUT
PRINT @ResultString

But @ResultString is printing empty string although there is record in database table.

What is wrong in this query?

thanks

Answer 1

You need to assign the result of select to variable inside Dynamic statement.

Change you query like this.

DECLARE @Result NVARCHAR(MAX)
DECLARE @Qry NVARCHAR(MAX)

SET @Qry='SELECT @Result = Test FROM MTest22Dec WHERE ID = 1'

EXECUTE Sp_executesql @Qry, N'@Result NVARCHAR(MAX) OUTPUT', @Result OUTPUT

PRINT @Result