How to set a SQL variable with either EXEC or sp_executesql

Question

I am attempting to build a query on the fly that will set a variable. How would I do this?

Here is what I have so far

DECLARE @SQL as NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

set @SQL = 'set @maxRowCount = (select count(*) from Test)'
SET @ParmDefinition = N'@maxRowCount int';

EXECUTE sp_executesql @SQL, @ParmDefinition

When I run this I get the following error

Msg 8178, Level 16, State 1, Line 1
The parameterized query '(@maxRowCount int)set @maxRowCount = (select count(*) from docto' expects the parameter '@maxRowCount', which was not supplied.

I am trying to get @maxRowCount to be set to the total row count of the Test table

do you actually need to use dynamic sql here or is this just a simplified example? — Tanner
– Tanner, Commented Jun 24, 2015 at 15:20

FranzBe · Accepted Answer · 2015-06-24 15:41:57Z

2

This works, you need the 'OUTPUT' in the parameter definition and a place where the result goes to as well

DECLARE @SQL as NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @iRowCount INT
SET @SQL = 'set @maxRowCount = (select count(*) from Test)'
SET @ParmDefinition = N'@maxRowCount INT OUTPUT';

EXECUTE sp_executesql @SQL, @ParmDefinition, @maxRowCount = @iRowCount  OUTPUT

PRINT 'rowcount is:' + CONVERT(VARCHAR,@iRowCount)

answered Jun 24, 2015 at 15:41

FranzBe

363 bronze badges

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

Collectives™ on Stack Overflow

How to set a SQL variable with either EXEC or sp_executesql

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