Executing SELECT statement dynamically

[ninelgorb]

I need to execute the following select statement dynamically:

SELECT @RC = count(*)
FROM @sTable
WHERE Calldate = @sCalldate

When I write it like this I get errors..

EXEC ('SELECT ' + @RC + ' = count(*)
FROM ' + @sTable +
' WHERE Calldate = ' + @sCalldate)

IS there any way I can do this to get the count into a variable?

Thanks,
Ninel

 

[mrdenny]

Create a global temp table and insert the count into that, then select the value from the global temp table after the dynamic SQL has been executed.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[ninelgorb]

Thanks. That did it.

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[amrita418]

You do not need to create temp tables for things like this, use sp_executesql instead.

Sample Code

DECLARE @query nvarchar(200),
        @rc int 
-- declare other variables

SET     @query = N'SELECT @rc = count(*) FROM ' + @sTable + ' WHERE Calldate = ' + @sCalldate

EXEC    sp_executesql @query, N'@rc int OUTPUT', @rc OUTPUT

PRINT   'Count is ' + convert(varchar, @rc)

Regards,
AA