Assign T-SQL variables in dynamic SQL statement?

microsoft.public.sqlserver.programming

17 Sep 2005 6:11 PM

Joe

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?

17 Sep 2005 6:32 PM

Adam Machanic

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
--

Show quote

"Joe" <j**@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?
>
>