问题描述
这可能是一个简单的答案，但我已经盯着它太久了......
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 连接，但这是我想要的要点.谁能看到我做错了什么?或者提出替代方案?仅供参考 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 查询语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！