T-SQL - Using parameters as parts of tablename [duplicate]

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
– Nick.Mc, Commented Aug 31, 2017 at 1: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
– Joel Coehoorn, Commented Aug 31, 2017 at 4:38

Jason A. Long · Accepted Answer · 2017-08-31 01:40:38Z

1

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;

answered Aug 31, 2017 at 1:40

Jason A. Long

4,4421 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL - Using parameters as parts of tablename [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related