我想将表格的行数分配给一个变量。
DECLARE @ROW_COUNT nvarchar(1000);
SET @sql_row_count = 'SELECT COUNT(*) FROM ' + @TABLE_NAME;
EXEC sp_executesql @sql_row_count, @ROW_COUNT OUTPUT;
SET @ROW_COUNT = cast(@ROW_COUNT as int);
SELECT @ROW_COUNT;
@ROW_COUNT 返回空值。
感谢帮助。
最佳答案
对现有查询的评论
@ROW_COUNT
应该是整数。如果您将其定义为整数,则无需使用 CAST()
。@ROW_COUNT
分配给 COUNT(*)
。QUOTENAME()
以避免sql 注入(inject)。sp_executesql
定义参数。修改查询如下
DECLARE @ROW_COUNT INT;
DECLARE @sql_row_count NVARCHAR(MAX)
SET @sql_row_count = 'SELECT @ROW_COUNT = COUNT(*) FROM ' + QUOTENAME(@TABLE_NAME);
-- print out for verification
PRINT @sql_row_count
EXEC sp_executesql @sql_row_count, N'@ROW_COUNT INT OUTPUT', @ROW_COUNT OUTPUT;
-- SET @ROW_COUNT = cast(@ROW_COUNT as int);
SELECT @ROW_COUNT;
https://stackoverflow.com/questions/69279528/