SQL: How to make table name in stored procedure dynamic

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.

  • How to pass an array into a SQL Server stored procedure
  • How do I grant access to SQL Server Agent to be able to write/modify system files?
  • Select letters where they are not preceeded or followed by letters e.g. FIND Ag or *Ag but not SAG or MAGNET
  • How to select all records from one table that do not exist in another table?
  • Getting a Dynamically-Generated Pivot-Table into a Temp Table
  • Excel import to SQL Server using distributed queries
  • 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.

  • Recursive COUNT Query (SQL Server)
  • When no 'Order by' is specified, what order does a query choose for your record set?
  • Is there and alternative to LIKE statement in T-SQL?
  • SQL Syntax to Pivot multiple tables
  • Group DateTime into 5,15,30 and 60 minute intervals
  • Get everything after and before certain character in SQL Server
  • One Solution collect form web for “SQL: How to make table name in stored procedure dynamic”

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.