How to get values using Dynamic SQL

Question

I have a query as below, which executes as per requirement.

DECLARE @count varchar(20)
SET @count = (SELECT COUNT(*) FROM emp)
PRINT 'Employee Count: '+@count
SELECT @count

But, if I want to do the same with the use if dynamic SQL i am not getting my desired result. My dynamic SQL code is as below:

DECLARE @count varchar(10)
DECLARE @sqlQuery varchar(500)
SET @sqlQuery = 'SET '+@count +'= (SELECT COUNT(*) FROM emp)'
EXEC (@sqlQuery)
PRINT 'Employee Count: '+@count
SELECT @count

This code gives me a NULL as output.

What should be done? Where have I gone wrong?

Devart · Accepted Answer · 2014-01-13 11:49:45Z

5

Try to add the output parameter for -

DECLARE 
      @SQL NVARCHAR(500)
    , @OutputNum VARCHAR(10)

SET @SQL = N'SELECT @OUT = COUNT(1) FROM dbo.emp'

EXEC sys.sp_executesql
        @SQL
    , N'@OUT INT OUTPUT'
    , @OUT = @OutputNum OUTPUT

PRINT 'Employee Count: '+ @OutputNum
SELECT @OutputNum

Output -

Employee Count: 103

answered Jan 13, 2014 at 11:49

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get values using Dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related