Assign T-SQL variables in dynamic SQL statement?

I have a dynamic SQL statement in which I need to assign values to
variables.

SELECT @querystring = 'SELECT @rows = COUNT(*)
@pages = COUNT(*) / @perpage
FROM utbl' + CAST(@tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'

EXEC(@querystring)

This doesn't work as it doesn't assign values to @rows and @pages that can
be accessed within the stored procedure.

Any suggestions?

Re: Assign T-SQL variables in dynamic SQL statement?

Use sp_executesql with output parameters:

DECLARE @rows INT
DECLARE @pages INT
DECLARE @querystring NVARCHAR(300)

SELECT @querystring = 'SELECT @rows = COUNT(*)
@pages = COUNT(*) / @perpage
FROM utbl' + CAST(@tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'

EXEC sp_executesql
@querystring,
N'@rows INT OUTPUT, @pages INT output, @perpage INT',
@rows OUTPUT, @pages OUTPUT, @perpage

PRINT @pages
PRINT @rows


--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--


"Joe" wrote in message
news:eNR78M7uFHA.3256@TK2MSFTNGP09.phx.gbl...
> I have a dynamic SQL statement in which I need to assign values to
> variables.
>
> SELECT @querystring = 'SELECT @rows = COUNT(*)
> @pages = COUNT(*) / @perpage
> FROM utbl' + CAST(@tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
>
> EXEC(@querystring)
>
> This doesn't work as it doesn't assign values to @rows and @pages that can
> be accessed within the stored procedure.
>
> Any suggestions?
>
>