简单的动态TSQL查询语法 [英] Simple dynamic TSQL query syntax

问题描述

这可能是一个简单的答案，但我盯着它看了太久...

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

我有以下查询使用存储过程输入参数作为变量名并计算该表中的记录。我想将动态语句（@toStartStr）的结果检索到变量（@toStart）中。

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

现在，错误提示@toStart不能与字符串SELECT串联，但这是我想要的要点。有人可以看到我在做什么吗？还是建议替代方案？ FYI SQL 2008 R2。谢谢。

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.

推荐答案

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;

但是，如果您可以忽略，可以使用一种更轻松，更有效的方法当前进行中的交易（并且您使用的是SQL Server 2005或更高版本-提出问题时请指定版本！）。

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;

为完整起见，这是SQL Server 2000的解决方案，它也不需要任何特殊特权（只需连接并成为公共成员）：

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;

也就是说，如果您要使用计数来确定下一个ID可能是什么，或其他像那样，我认为您正在以错误的方式进行处理，因为可以删除行，并且如果是标识，则由于回滚可以跳过列的值。

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.

这篇关于简单的动态TSQL查询语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！