SQL：如何使表名在存储过程中动态 [英] SQL: How to make table name in stored procedure dynamic

问题描述

我对SQL Server很新，希望有人能够帮助我（我正在使用QL Server 2008）。

以下是一个按照预期工作的小程序。

现在我想使用相同的过程更新多个表，因为所有这些表具有完全相同的列名和列格式，唯一的区别是第二部分我在下面添加了 XXX 的表名称。有人可以告诉我这是怎么做到动态的，还可以给我一些解释吗？
我不能在这里提供更多，因为我不知道如何处理这个 - 除了可能声明 @sql nvarchar（max）并包装整个查询在$ code> SET @sql = N'...'之前执行它。

我的存储过程：

  CREATE PROCEDURE [dbo] Cal_UpdateTeam] 
 @team nvarchar（100），
 @teamID int，
 @notes nvarchar（1000），
 @log nvarchar（100），
 @admin varchar （50）
 AS 
 BEGIN 
 SET NOCOUNT ON; 
 
 BEGIN 
 
如果不存在
（
 SELECT * 
从Cal_XXX 
 WHERE teamID = @teamID 
 ）
 INSERT INTO Cal_XXX 
（
 team，
 teamID，
 notes，
 log，
 admin 
）
 SELECT @team，
 @teamID，
 @notes，
 @log，
 @admin 
 ELSE 
更新Cal_XXX 
 SET team = @team，
 teamID = @teamID，
 notes = @notes，
 log = @log，
 admin = @admin 
 WHERE teamID = @teamID 
 
 END 
 END 
  

非常感谢任何提示和建议，迈克。

解决方案

你应该将你的sql查询包装在一个nvarchar中，然后像下面的例子那样执行该查询：

  declare @sql nvarchar（max）
 declare @TableName nvarchar（max）
 set @TableName ='mytable'
 set @sql ='Select * from '+ @TableName 
执行sp_executesql @sql 
  

I am pretty new to SQL Server and hope someone here can help me with this (I'm using QL Server 2008).

The following is a small procedure that works as intended.

Now I would like to use the same procedure to update multiple tables as all these tables have exactly the same column names and column formatting, the only difference is the 2nd part of the table name for which I added XXX below.

Can someone tell me how this could be made dynamic and also provide me some explanations on this ? I cannot provide much more here as I wasn't sure about how to approach this - other than probably declaring @sql nvarchar(max) and wrapping the whole query in SET @sql = N'...' before executing it.

My stored procedure:

    CREATE PROCEDURE [dbo].[Cal_UpdateTeam]
        @team nvarchar(100),
        @teamID int,
        @notes nvarchar(1000),
        @log nvarchar(100),
        @admin varchar(50)
    AS
    BEGIN
        SET NOCOUNT ON;

        BEGIN   

        IF NOT EXISTS 
        (
                SELECT  * 
                FROM    Cal_XXX
                WHERE   teamID = @teamID
        )
        INSERT INTO Cal_XXX
        (
                team,
                teamID,
                notes,
                log,
                admin
        )
        SELECT  @team,
                @teamID,
                @notes,
                @log,
                @admin
        ELSE
                UPDATE  Cal_XXX
                SET     team = @team,
                        teamID = @teamID,
                        notes = @notes,
                        log = @log,
                        admin = @admin
                WHERE   teamID = @teamID

        END
END

Many thanks for any tips and advise on this, Mike.

解决方案

you should wrap your sql query in an nvarchar and then execute that query as in the below example :

    declare @sql nvarchar(max)
    declare @TableName nvarchar(max)
    set @TableName = 'mytable'
    set @sql = 'Select * from ' + @TableName
    Exec sp_executesql @sql

这篇关于SQL：如何使表名在存储过程中动态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！