CODEWITHSUNDEEP
sql-servert-sqltablename
brunofl
brunofl

Reputation: 155

SQL Server : how to always query a table that has the current date in it's name

I am on a database that always creates a report table with a name of Table_Name_YYYYMMDD.

I would like to create a query that using a subquery like:

Select convert(varchar, getdate(), 112)

Could allow me to always get the results from table with the current date in the name.

The user has read-only access to the database.

I did try the following:

Declare @Tname varchar(50); 
Set @Tname = 'table_name_' + (Select convert(varchar, getdate(), 112));

Declare @Q varchar(200);
Set @Q = 'Select * From ' + @Tname;

Exec @Q;

But I get an error :

Must declare the scalar variable "@Q".

Thanks!

Upvotes: 0

Views: 1594

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Your question is not very clear... You seem to know, that the approach with value-named table names is a bad one. But you told us, that you have to stick to this third-party tool. Well, shit happens ;-)

Your code should work, but assumably you don't show everything and/or you tried to shorten it for brevity (which is great!), but - maybe - you shortened the error away...

Try this:

USE master;
GO
CREATE DATABASE testTbWithDate; --Some extra database to create tables
GO
USE testTbWithDate;
GO
CREATE TABLE SomeName_20190701(ID INT,SomeValue VARCHAR(100));
GO
CREATE TABLE SomeName_20190630(ID INT,SomeValue VARCHAR(100));
GO
--test values
INSERT INTO SomeName_20190630 VALUES(1,'this is 30th of June'); 
INSERT INTO SomeName_20190701 VALUES(1,'this is 1st of July');
GO

--Here starts the query

--You should always use (n)varchar together with a length parameter
--And you should not keep your length to small...
Declare @Tname varchar(50) =  --<-- 50 might be not enough...
   'SomeName_' + (Select convert(varchar(8), {d'2019-07-01'}, 112)); --I added "(8)"


Declare @Q varchar(max); --This was pretty small, I use "(max)"
Set @Q = 'Select * From ' + QUOTENAME(@Tname); --I use QOUTENAME to avoid invalid names and help (a little bit) against SQL injection

PRINT @Q; --always print out your command. Now you can copy and paste it into a query window and check the syntax.
Exec(@Q); --the execution

--Clean-Up

GO
USE master;
GO
DROP DATABASE testTbWithDate;

Upvotes: 1

Serg
Serg

Reputation: 22811

First, Exec @Q is the syntax for calling a stored proc. Use 

Exec (@Q);

to exec a statement.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521864

Using the table name itself to store somewhat transient information is not best practice. It would be better to just maintain a single report table, and then add a timestamp/datetime column for the date of the report (along with some kind of primary key).

Then, if you wanted to select all records pertaining to today's report, you could just use:

SELECT *
FROM ReportTable
WHERE dt = GETDATE();  -- or something similar to this

Upvotes: 1

Related Questions