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.

Bayne

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.

Camm

It's not always true that you are a sa user. I think admin rights are required to execute this query. Right? – Asleep

Nope, I just tested on a system where I do not have admin rights and it runs fine. – Kocher

@Kanavi No, admin rights are not required to view sys.partitions. Did you try it before telling me to remove the code? – Camm

SQL 2008 R2. And a record count is fine, as this is a static temporary table from a file import. – Bayne

Your SQL 2005+ solution worked perfectly. I'll update the question to make it more specific. – Bayne