Set the result of EXECUTE sp_executesql in a variable in sql

Question

I need to set the dynamic result of sql query to a variable. My sql query:

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 prints an empty line, although there is an entry in the database table.

What is wrong with this query?

thanks

Answer 1

You should set the output variable somewhere, for example.

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

Answer 2

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

Modify the query this way.

 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 

Answer 3

Try the following:

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

Answer 4

Try the following:

 Declare @Query NVARCHAR(100) Declare @ResultString varchar(50) set @Query =N'select @ResultString=Test from dbo.MTest22Dec where id = @id' exec sp_executesql @Query, N'@id int,@ResultString varchar(20) output', @id = 1,@ ResultString=@ResultString output print @ResultString