Simple dynamic TSQL query syntax

Question

This may be an easy answer but I've been staring at it for too long...

I have the following query that takes a stored procedure input parameter as a variable name and counts the records in that table. I'd like to retrieve the results of the dynamic statement (@toStartStr) into a variable (@toStart).

-- @tempTableName = SProc input parameter
DECLARE @toStartStr nvarchar(150);
DECLARE @toStart int;
SET @toStartStr = 'SELECT @toStart = COUNT(ID) FROM ' + @tempTableName;
EXEC(@toStartStr);

Right now, an error suggests that @toStart cannot be concatenated with the string SELECT, but this is the gist of what I want. Can anyone see what I'm doing wrong? Or suggest an alternative? FYI SQL 2008 R2. Thanks.

Answer 1

DECLARE @sql NVARCHAR(255);

DECLARE @toStart INT;

SET @sql = N'SELECT @toStart = COUNT(ID) FROM ' + QUOTENAME(@tempTableName);

EXEC sp_executesql @sql, N'@toStart INT OUTPUT', @toStart OUTPUT;

PRINT @toStart;

However there is a much easier and more efficient way to do this, if you're okay with ignoring current in-flight transactions (and you're using SQL Server 2005 or better - please specify the version when asking questions!).

DECLARE @toStart INT;

SELECT @toStart = SUM(rows) 
  FROM sys.partitions
  WHERE [object_id] = OBJECT_ID(@tempTableName)
  AND index_id IN (0,1);

PRINT @toStart;

Just for completeness, here is a solution for SQL Server 2000, which also doesn't require any special privileges (just connect and member of public):

DECLARE @toStart INT;

SELECT @toStart = [rows] 
  FROM sysindexes
  WHERE id = OBJECT_ID(@tempTableName)
  AND indid IN (0,1);

PRINT @toStart;

That said, if you're using a count to determine what the next ID might be, or something like that, I think you're approaching this the wrong way, since rows can be deleted and if it's an identity column values can be skipped due to rollbacks.