存储过程:把表名变成表变量 [英] Stored Procedure: turn Table name into Table Variable
问题描述
有很多类似的问题,但是我并没有完全找到我想要的.由于在存储过程中使用动态 SQL 会很快变得麻烦,我想将表名 (Varchar) 传递给存储过程,将该表名转换为表变量,然后在该过程的其余部分使用该表变量.我无法弄清楚这个代码.
There are quite a few similar Questions, however i didn't quite find what i was looking for. Since using dynamic SQL in a stored procedure can quickly get cumbersome, I want to pass a table name (Varchar) to a stored procedure, turn that Tablename into a Tablevariable and afterwards work with this Tablevariable for the rest of the procedure. I can't figure out the code for this.
我在 SQL Server 2008R2 上使用 SSMS.目前我的代码看起来与此类似.我滞后中间部分从@TableName Varchar 变量创建@Table 表变量
I'm working in SSMS on a SQL Server 2008R2. Currently my code looks similar to this. I lag the middle part to create the @Table Table Variable from the @TableName Varchar Variable
CREATE Procedure [dbo].StoredProc(@Tablename Varchar)
AS
Begin
Declare @Table Table (ColA Varchar, ColB Float)
Declare @result float
-- Something like Insert @Table Select * From @Tablename using Dynamic sql or sth. similar
Select @result = Select sum(ColB) From @Table
End
推荐答案
您可以通过以下方式组合动态 SQL 和临时表存储:
You can combine dynamic SQL and Temporary table storage the following way:
CREATE Procedure [dbo].StoredProc(@Tablename Varchar(100))
AS
Begin
create table #TempTbl (ColA Varchar(100), ColB Float);
Declare @result float
declare @dynSQL varchar(max);
select @dynSQL = 'insert into #TempTbl select
cast(val1 as varchar(100)) as ColA,
cast(val2 as float) as ColB from ' + COALESCE( @Tablename, 'NULL');
-- Tablename should contain schema name, 'dbo.' for example
exec( @dynSQL );
Select @result = sum(ColB) From #TempTbl
drop table #TempTbl;
return @Result;
End
这篇关于存储过程:把表名变成表变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!