SQL: How to make table name in stored procedure dy

问题:

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.

回答1:

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