1

This is my SQL syntax and (this is only a snippet unfortunately I am stuck running dynamic SQL in this instance) - but my issue is at the very end of my statement when I do the Select @numreturned what is returned is the SQL statement NOT the actual number that is returned when I run the Exec(@numreturned)

What must I alter in order to return the NUMBER instead of the SQL Statement?

Declare @numreturned varchar(max), 
@fulllocation varchar(max) = 'nagadishi.mirama.dbo.Humizan'

Set @numreturned = 'Select Count(*) FROM '+@fulllocation+' '
                   +'WHERE ([Nishan] IS NULL) '

Print @numreturned
Exec(@numreturned)

Select @numreturned
0
4

You could do this with sp_executesql and assigning the variable to an output parameter

DECLARE @numreturned  INT;
DECLARE @sql NVARCHAR(MAX);
DECLARE @fulllocation NVARCHAR(MAX) = 'nagadishi.mirama.dbo.Humizan';

SET @sql = N'SELECT @numreturned = COUNT(*) FROM '+@fulllocation+' '
                   +N'WHERE ([Nishan] IS NULL)';

EXEC sys.sp_executesql @sql, N'@numreturned INT OUTPUT', @numreturned = @numreturned OUTPUT;
SELECT @numreturned;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.