asked on

TSQL - How to declare table name

Hi Guys,

I have table on db which is updated every day based on today date(prefix 'error' + today date)
SELECT * FROM error20170208

How to fix declaration below?

DECLARE @TODAY_DATE	VARCHAR(13); select @TODAY_DATE = ('error' + convert(varchar, GETDATE() ,112));
SELECT * FROM @TODAY_DATE

Open in new window

DECLARE @TODAY_DATE	VARCHAR(13); set @TODAY_DATE = (select 'error' + convert(varchar, GETDATE() ,112));
SELECT * FROM @TODAY_DATE

Open in new window

Error: Must declare the table variable "@TODAY_DATE".

Thank you!

Vitor Montalvão

Did you check the MSDN article for the table variable?

So, for your example:

DECLARE @MYTABLE table(  
    @TODAY_DATE	VARCHAR(15));  


INSERT INTO @MYTABLE
select 'error' + convert(varchar, GETDATE() ,112);

SELECT * FROM @MYTABLE

Open in new window

Darius

ASKER

@ Vitor,
got error: Incorrect syntax near '@TODAY_DATE'.

Pawan Kumar

Hi Darius,
The above comment will not work.

Please try below tested options -

DECLARE @TODAY_DATE	AS TABLE (TODAY_DATE VARCHAR(13))
INSERT INTO @TODAY_DATE
SELECT CONCAT('Error',CONVERT(VARCHAR, GETDATE() ,112))
SELECT * FROM @TODAY_DATE
Go

Open in new window

DECLARE @TODAY_DATE	VARCHAR(13) 
SELECT @TODAY_DATE = CONCAT('Error',CONVERT(VARCHAR, GETDATE() ,112))
SELECT @TODAY_DATE
GO

Open in new window

OUTPUT

/*------------------------
DECLARE @TODAY_DATE	VARCHAR(13) 
SELECT @TODAY_DATE = CONCAT('Error',CONVERT(VARCHAR, GETDATE() ,112))
SELECT @TODAY_DATE
GO

DECLARE @TODAY_DATE	AS TABLE (TODAY_DATE VARCHAR(13))
INSERT INTO @TODAY_DATE
SELECT CONCAT('Error',CONVERT(VARCHAR, GETDATE() ,112))
SELECT * FROM @TODAY_DATE
Go
------------------------*/

-------------
Error20170208

(1 row(s) affected)


(1 row(s) affected)
TODAY_DATE
-------------
Error20170208

(1 row(s) affected)

Open in new window

Hope it helps!

Vitor Montalvão

Ah, that's because I forgot to remove the '@'

DECLARE @MYTABLE table(  
    TODAY_DATE VARCHAR(15));  

INSERT INTO @MYTABLE
select 'error' + convert(varchar, GETDATE() ,112);

SELECT * FROM @MYTABLE

Open in new window

Darius

ASKER

@Pawan,

error:
'CONCAT' is not a recognized built-in function name.

Pawan Kumar

Ok, So you are not using SQL 2012. Please use below-

ADDED SQL SERVER 2008 as Topic

DECLARE @TODAY_DATE	VARCHAR(13) 
SELECT @TODAY_DATE = 'Error' + CONVERT(VARCHAR, GETDATE() ,112)
SELECT @TODAY_DATE
GO

Open in new window

---

DECLARE @TODAY_DATE	AS TABLE (TODAY_DATE VARCHAR(13))
INSERT INTO @TODAY_DATE
SELECT 'Error' + CONVERT(VARCHAR, GETDATE() ,112)
SELECT * FROM @TODAY_DATE
Go

Open in new window

Hope it helps!

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

@Author -

If you try above you will below error-

Msg 208, Level 16, State 1, Line 1
Invalid object name 'error20170208'.

Pawan Kumar

Hi,
If you still want to go for dynamic string (I dont prefer) you can use below-

DECLARE @SQL AS VARCHAR(MAX)=''
SET @SQL = ' 
DECLARE @TODAY_DATE	AS TABLE (TODAY_DATE VARCHAR(13))
INSERT INTO @TODAY_DATE
SELECT ''Error'' + CONVERT(VARCHAR, GETDATE() ,112)
SELECT * FROM @TODAY_DATE ;
'
EXEC(@SQL)

Open in new window

Output
-------
TODAY_DATE
Error20170208

Hope it helps!

Darius

ASKER

Thank you guys,
Provided examples works on:

@Vitor,

DECLARE @MYTABLE table(  
    TODAY_DATE VARCHAR(15));  
INSERT INTO @MYTABLE
select 'error' + convert(varchar, GETDATE() ,112);
SELECT * FROM @MYTABLE

Open in new window

@Pawan

DECLARE @TODAY_DATE	AS TABLE (TODAY_DATE VARCHAR(13))
INSERT INTO @TODAY_DATE
SELECT 'Error' + CONVERT(VARCHAR, GETDATE() ,112)
SELECT * FROM @TODAY_DATE
Go

Open in new window

But this is it goes to error when I tried to use where clause
Error: Invalid column name

it is working when I use:
SELECT * FROM error20170208
where message = 'message'

Vitor Montalvão

I guess only Scott understood properly your question.
Can you provide the error20170208 table structure?

Darius

ASKER

Table structure

Pawan Kumar

Are you looking for this-

DECLARE @SQL AS VARCHAR(MAX)=''

SET @SQL = ' 
DECLARE @Error' + CONVERT(VARCHAR, GETDATE() ,112) + ' AS TABLE ([Message] VARCHAR(13))
INSERT INTO @Error' + CONVERT(VARCHAR, GETDATE() ,112) + '
SELECT ''Error' + CONVERT(VARCHAR, GETDATE() ,112) + '''' + '
SELECT * FROM @Error' + CONVERT(VARCHAR, GETDATE() ,112) ;

EXEC(@SQL)

Open in new window

OUTPUT

Message
Error20170208

Hope it helps!

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

The above will not work, Syntax Error.

Please Try below-

Value in the message column can you changed while inserting data. I have inserted message in the message column.

DECLARE @SQL AS VARCHAR(MAX)=''

SET @SQL = ' 
DECLARE @Error' + CONVERT(VARCHAR, GETDATE() ,112) + ' AS TABLE ([Message] VARCHAR(13))
INSERT INTO @Error' + CONVERT(VARCHAR, GETDATE() ,112) + '
SELECT ''Message' + '''' + '
SELECT * FROM @Error' + CONVERT(VARCHAR, GETDATE() ,112) + ' WHERE Message = ''message'' ';

EXEC(@SQL)

Open in new window

Output

Message
---------------
Message

Hope it helps!

Darius

ASKER

Thank you for helping all of YOU.

I need to go!
I'll be back later

Vitor Montalvão

The above will not work, Syntax Error.

Which syntax error? Do you have the table Error20170208 created?
Darius, please confirm if it works in your environment.

Darius

ASKER

@ Vitor

Example working!

DECLARE @TODAY_DATE      VARCHAR(13); 
select @TODAY_DATE = ('error' + convert(varchar, GETDATE() ,112));
EXEC('SELECT * FROM ' + @TODAY_DATE + ' WHERE Message = ''message''')

Open in new window

I will review other examples later

Thank you!!!

Pawan Kumar

Now I am also bit confused, Why Darius got invalid column issue if the table is there.?

Pawan Kumar

You can try this also-
No need to set the variable, you can do that in the declare itself.

--
DECLARE @M AS VARCHAR(13) = CONVERT(VARCHAR, GETDATE() ,112)
EXEC('SELECT * FROM error' + @M + ' WHERE Message = ''message''') 
--

Open in new window

Darius

ASKER

@Pawan,

--
DECLARE @M AS VARCHAR(13) = CONVERT(VARCHAR, GETDATE() ,112)
EXEC('SELECT * FROM error' + @M + ' WHERE Message = ''message''') 
--

Open in new window

issues fired:
Incorrect syntax near the keyword 'CONVERT'.
Must declare the scalar variable "@M".

Darius

ASKER

@Scott
@Vitor

DECLARE @TODAY_DATE      VARCHAR(13); 
select @TODAY_DATE = ('error' + convert(varchar, GETDATE() ,112));
EXEC('SELECT * FROM ' + @TODAY_DATE + ' WHERE 
		message = ''message1''
	or  message = ''message2''
	or  message = ''message3'' 
	order by writetime desc 
	')

Open in new window

This working!

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Darius

ASKER

@Pawan,
Thank You!

DECLARE @M AS VARCHAR(13);
SET @M = CONVERT(VARCHAR, GETDATE() ,112)
EXEC('SELECT * FROM error' + @M + ' WHERE 
	message = ''message1''
	or  message = ''message2''
	or  message = ''message3'' 
	order by writetime desc  
')

Open in new window

Pawan Kumar

Glad it worked.!

Darius

ASKER

Much appreciated!
Thank You