SQL getting value of dynamic query into a variable

Question

I am selecting the COUNT of something in a SQL Server 2016 database using a dynamic SQL query and want that value placed into a variable. Below is the SQL statement which I was sure was correct, but the value I'm getting is NULL (I have tested the query and it returns a value). Any tips?

DECLARE @Output INT 

SELECT @SqlCommand = 'SELECT COUNT(ServerName) FROM ' + @TableReference + ' WITH (NOLOCK) WHERE ServerName = ''' + @PackageEndPoint + '''' 

EXEC sp_executesql @SqlCommand, N'@Output INT OUTPUT', @Output = @Output OUTPUT

SELECT @StagingRecordCount = @Output

Ed Bangga · Accepted Answer · 2019-10-17 00:57:53Z

2

You need to assign your @Output variable to your result count.

@Output = COUNT(ServerName)

complete script

DECLARE @Output INT 

SELECT @SqlCommand = 'SELECT @Output = COUNT(ServerName) FROM ' + @TableReference + ' WITH (NOLOCK) WHERE ServerName = ''' + @PackageEndPoint + ''''

EXEC sp_executesql @SqlCommand, N'@Output INT OUTPUT',@Output = @Output OUTPUT
SELECT  @StagingRecordCount = @Output
SELECT @StagingRecordCount

answered Oct 17, 2019 at 0:57

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

SQL getting value of dynamic query into a variable

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