0

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'.
Ozgur Sar
  • 2,067
  • 2
  • 11
  • 23
jfcb
  • 9
  • 4
  • As the error tells you, you don't reference a temporary table with 3 part naming, just use `#TableName`. – Thom A Nov 23 '20 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 Nov 23 '20 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 Nov 23 '20 at 12:33
  • Yes, but *why* do you want to do that at all? As I said, this smells like an [XY Problem](http://xyproblem.info). – Thom A Nov 23 '20 at 12:34
  • a quick search will reveail this has been answered. https://stackoverflow.com/questions/20678725/how-to-set-table-name-in-dynamic-sql-query . – Jeremy Nov 23 '20 at 12:36
  • Note, as well, that a temporary table can only references in the scope (or inner scopes) it was created in, and only persists for the duration of that scope. You would *have* to use a dynamic statement here, meaning that as soon as the dynamic statement ended, your temporary table would be dropped. – Thom A Nov 23 '20 at 12:41
  • Do you have any example of that?? – jfcb Nov 23 '20 at 12:55
  • *" Do you have any example of that??"* [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=83be74220e7b69e9e8cfa2048b4f7c98) – Thom A Nov 23 '20 at 12:59

1 Answers1

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.

Andy Edgar
  • 86
  • 1
  • 5
  • 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. – Thom A Nov 23 '20 at 12:40