Table Name as parameter

Question

Post reply

Table Name as parameter

mjbkm

Ten Centuries

Points: 1104
More actions
February 7, 2012 at 3:09 pm

#252506

Is it possible to have a dynamic table name? --- Where I can do a select query based on what year the user selects? (This specific project has 1 table per year)
For example:
Parameter: @Year
SELECT dbo.tblTransac@Year.*
FROM dbo.tblTransac@Year
So the user picks 2011, I want to pull all records from the tblTransac2011 table.
Thank you in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Not unless you can code using Dynamic-SQL or a series of IF clauses!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If you go the dynamic SQL route, read up on SQL Injection first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mjbkm Ten Centuries Points: 1104 More actions · Answer 3

Do you see any problems with this?

ALTER PROCEDURE [dbo].[SP_MOR_TestingWorking2](

@fiscalyear char(8)

)

AS

BEGIN

DECLARE @sql NVARCHAR(100)

IF ((ISNUMERIC(@fiscalyear) = 1) AND @fiscalyear >=20012002 AND @fiscalyear <=20112012)

BEGIN

SET

@sql = 'SELECT * FROM tblMOR_LU_AdCpGp' + @fiscalyear

EXEC (@SQL)

END

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Other than it been an ugly solution?

Any reason why partitioned views/partitioned tables weren't considered?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mjbkm Ten Centuries Points: 1104 More actions · Answer 5

Even with the if statement?

IF ((ISNUMERIC(@fiscalyear) = 1) AND @fiscalyear >=20012002 AND @fiscalyear <=20112012)

How would it be possible?

Thank you

mjbkm Ten Centuries Points: 1104 More actions · Answer 6

ok, your original post changed??

In this instance I don't have a choice with the table design.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Personally, I'd rather separate queries, or at most something like this:

IF (@fiscalyear = '201120011')

SELECT <column list> FROM tblMOR_LU_AdCpGp201120011

IF (@fiscalyear = '201020011')

SELECT <column list> FROM tblMOR_LU_AdCpGp201020011

....

Otherwise you have to account for and handle the errors when somehow something like 20152698 gets passed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Table Name as parameter

Cookies on SQLServerCentral