Using sp_executesql to store a Scalar Result into a Variable

Question

I have followed the first answer found at this link: How to get sp_executesql result into a variable?

Declare @Num int;
Declare @DB_Name nvarchar(50);

Set @DB_Name = 'Database';
Declare @query nvarchar(100);
DECLARE @parm nvarchar(100);

Select @query = N'Select COUNT(Field) From ' + @DB_Name + '.dbo.Table';
Select @parm = N'@Num int OUT';

EXEC sp_executesql @query, @parm, @Num = @Num OUT;
Select @Num;

The output from the Exec statement provides me with the proper result in the Results Window, however, the last "Select @Num" statement returns NULL.

I need to use the value of the @Num Variable in the rest of my code.

Am I missing something?

Joseph

Try using a second variable for the output and then selecting that variable. — Jacob H, May 17 '17 at 17:51
Thank you Jacob, but I have tried this, but without success. — JF0001, May 17 '17 at 17:54

score 1 · Accepted Answer · answered May 17 '17 at 17:55

1

In your dynamic SQL you forgot to assign the query result to your @num variable

Select @query = N'Select @Num = COUNT(Field) From ' + @DB_Name + '.dbo.Table';

answered May 17 '17 at 17:55

shurik

775
9
19

Yes indeed. Thank you very much Shurik. – JF0001 May 17 '17 at 17:57

Using sp_executesql to store a Scalar Result into a Variable

1 Answers1