append the current year to a table name in a SQL request SQL Server 2012

Neil Thompson
Neil Thompson used Ask the Experts™
on
Hi

I'm trying to automate a lookup on a table based on the current year, after some help as this doesn't seem to work. I want the year appended to the table name

example: FROM t_refuse_garden_2019

SELECT count(green_uprn) AS a 
FROM t_refuse_garden_YEAR(getdate())
WHERE green_uprn  = '12345678' 
AND green_end_date is null

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You may execute the command from a variable:
DECLARE @cmd AS nvarchar(1000)
SET @cmd = 'SELECT count(green_uprn) AS a FROM t_refuse_garden_' + CAST(YEAR(getdate()) AS char(4)) + 
           ' WHERE green_uprn  = ''12345678'' AND green_end_date is null'

EXEC sp_executeSQL @cmd

Open in new window

The sp_executeSQL also allows to define parameters which you may use in WHERE clause.
More info: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017
Neil ThompsonSenior Systems Developer

Author

Commented:
Superb, many thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial