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

Neil Thompson
Neil Thompson used Ask the Experts™

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

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:
Neil ThompsonSenior Systems Developer


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