从名称作为变量传递的表中进行选择 [英] Selecting from a table where the name is passed as a variable
问题描述
我正在尝试编写一个简单的存储过程,它采用三个参数数据库名称一"、数据库名称二"和表名称".然后sql会对每个数据库中定义的表进行行计数并存储.
I am trying to write a simple stored proc which takes three arguments 'database name one', 'database name two' and 'table name'. The sql will then perform a row count for the defined table in each database and store it.
零碎地工作 我遇到了你做不到的第一个问题
Working on it piecemeal I have hit the first problem in that you can't do
select * from @tablename
我知道您可以通过 exec
命令使用动态 sql,但这并不理想,因为我无法返回值.
I know you can use dynamic sql with the exec
command but this is not ideal as I can't return values.
下面的例子看起来应该可以,但没有.
The following example looks like it should work but doesn't.
declare @tablename as nvarchar(500)
declare @sqlstring as nvarchar(500)
declare @parmdefinition as nvarchar(500)
declare @numrows as bigint
set @tablename = N'dummy_customer'
set @parmdefinition = N'@tablenameIN nvarchar(500), @numrowsOUT as bigint OUTPUT'
select @sqlstring = 'select @numrowsOUT = count(*) from @tablenameIN'
select @sqlstring
exec sp_executesql @sqlstring, @parmdefinition, @tablenameIN = @tablename, @numrowsOUT = @numrows OUTPUT
select @numrows
给出的错误信息是
Msg 1087, Level 16, State 1, Line 1
Must declare the table variable "@tablenameIN".
目前使用的是 SQL Server 2008 SP2.
Currently using SQL Server 2008 SP2.
我们这样做是因为我们正在进行迁移,而客户想要一份报告,该报告显示源数据库和目标数据库中每个表的行数.由于有许多表能够使用 sp_MSForEachTable 来调用存储过程似乎是理想的.
We're doing this because we are doing a migration and the customer wants a report which shows the row count for each table in the source and destination database. As there are many tables being able to use sp_MSForEachTable to call the stored proc seems ideal.
供将来参考的最终解决方案是
The final solution for future reference is
declare @tablename as nvarchar(500)
declare @sqlstring as nvarchar(500)
declare @parmdefinition as nvarchar(500)
declare @numrows as bigint
set @tablename = N'dummy_customers'
set @parmdefinition = N'@tablename nvarchar(500), @numrowsOUT as bigint OUTPUT'
select @sqlstring = 'select @numrowsOUT = count(*) from ' + quotename(@tablename)
exec sp_executesql @sqlstring, @parmdefinition, @tablename = @tablename, @numrowsOUT = @numrows OUTPUT
select @numrows
推荐答案
你必须使用动态 sql,并将表名连接到 SQL 字符串中,然后通过 sp_executsql 执行:
You'd have to use dynamic sql, and concatenate the table name into the SQL string to then execute via sp_executsql:
select @sqlstring = 'select @numrowsOUT = count(*) from ' + QUOTENAME(@tablename)
EXECUTE sp_executesql ....
这篇关于从名称作为变量传递的表中进行选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!