How to create a date parameter into a table name in SQL Server

Question

I have this parameters in SQL Server:

SET DATEFORMAT DMY

DECLARE @FECHA_ACT VARCHAR(10)
DECLARE @PERIODO  char(6)
SET @FECHA_ACT = CONVERT(CHAR(10),GETDATE(),112)
SET @PERIODO=cast(CONVERT(CHAR(6),GETDATE(),112) as int)

I want to create a table using :

TEMPDB..#abc_def_['+@FECHA_ACT+']

but I am getting this error when I tried to create the table:

> Database name 'TEMPDB' ignored, referencing object in tempdb.  
> Msg 102, Level 15, State 1, Line 15  
> Incorrect syntax near ''+@FECHA_ACT+''.  
> Database name 'TEMPDB' ignored, referencing object in tempdb.  
> Msg 102, Level 15, State 1, Line 19  
> Incorrect syntax near ''+@FECHA_ACT+'''.  
> Msg 102, Level 15, State 1, Line 32  
> Incorrect syntax near 'B'.

As the error tells you, you don't reference a temporary table with 3 part naming, just use #TableName. — Thom A
– Thom A ♦, Commented Nov 23, 2020 at 12:26
This, however, smells like an XY Problem; you can't use a variable to replace a literal (in this case the table's name). What are you actually trying to achieve here? — Thom A
– Thom A ♦, Commented Nov 23, 2020 at 12:27
I am creating a Temorary table like this: SELECT * INTO TEMPDB..#abc_def_['+@FECHA_ACT+''] FROM ....... I need to create this table weakly, so Every single time I am gonna execute I need to change date of table....example execution today: outcome tempdb..#abc_cdf_20201123 ( I wanted to concatenate date like a parameter) execution next monday outcome -> tempdb..#abc_cdf_20201130, That I need — jfcb
– jfcb, Commented Nov 23, 2020 at 12:33
Yes, but why do you want to do that at all? As I said, this smells like an XY Problem. — Thom A
– Thom A ♦, Commented Nov 23, 2020 at 12:34
a quick search will reveail this has been answered. stackoverflow.com/questions/20678725/… . — Jeremy
– Jeremy, Commented Nov 23, 2020 at 12:36

Andy Edgar · Accepted Answer · 2020-11-23 12:37:11Z

2

You need to create a dynamic query.

For example:

SET DATEFORMAT DMY

DECLARE @FECHA_ACT VARCHAR(10)
DECLARE @DynamicSQL nvarchar(1000)
SET @FECHA_ACT = CONVERT(CHAR(10),GETDATE(),112)
SET @DynamicSQL = N'CREATE TABLE abc_def_' + @FECHA_ACT + ' (id int)'
EXEC(@DynamicSQL)

See this post for more details.

answered Nov 23, 2020 at 12:37

Andy Edgar

1261 silver badge5 bronze badges

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

1 Comment

Thom A Over a year ago

This isn't particularly secure mind. @FECHA_ACT is only a varchar(10) here, fortunately, but longer values would open up a huge injection hole.

Collectives™ on Stack Overflow

How to create a date parameter into a table name in SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related