T-SQL - Using parameters as parts of tablename

Question

I am trying to use T-SQL (in SQL Server 2012) stored procedures to pass a parameter to be part of table name but am getting an error.

Example

CREATE PROCEDURE test_proc 
     @year NVARCHAR(4)  
AS
BEGIN
    UPDATE Tablename@yearABC
    .....

They if I call the procedure with the parameter value 2017, I want it to update Tablename2017ABC

Can anyone explain how use stored procedures (or other T-SQL) to use the parameter as part of a table name?

Thanks

Why do you have the same data in differently names tables? It's a common misconception that it's a good idea to archive data off into yearly tables. Just use adequate indexing on a single table and save yourself the effort — Nick.Mc, Aug 31 '17 at 01:44
This is a strong indication that you should have **ONE** table with a column for `year` as the first part of the primary key, and use table partitions if you really need to spread this out over multiple files or disks. — Joel Coehoorn, Aug 31 '17 at 04:38

score 1 · Answer 1 · answered Aug 31 '17 at 01:40

You could do something along these lines...

DECLARE 
    @year NCHAR(4) = N'2017',
    @DeBug BIT = 1; -- 0=execute & 1=print sql

DECLARE @sql NVARCHAR(4000) = N'
UPDATE tny SET
    tny.ColumnName = ''Something''
FROM
    dbo.TableName' + @year + N' tny
WHERE 
    tny.OtherColumn = ''SomethingElse'';';

IF @DeBug = 1
BEGIN
    PRINT(@sql);    
END;
ELSE
BEGIN
    EXEC sys.sp_executesql @sql
END;

T-SQL - Using parameters as parts of tablename

1 Answers1