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?
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" <joe@.hotmail.com> 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?
>