Creating a variable to use as a table identifier

[thenaturalselector]

Hey All,

So i am writing a procedure and many of the tables i need to reference end with a date or interger, for example:

dbo.activity_equation_201106

I am wondering if there is a way to replace the '201106' on the end of the table name with a variable so that i can easily change all tables being referenced by just changing the variable value.
Any thoughts??

 

[gmmastros]

This is possible if you write dynamic SQL.

A better approach would be to properly normalize your tables. I doubt there is any good reason for having separate tables with numbers and/or dates on the end of them. Instead, that data should be another column in the table and used as filter criteria.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

George, Normally I would agree there is no reason for a date on a table name, however, our new phone system is setup to use a table with a date. They did not ask me because they use a dedicated server they setup specifically for their software/SQL server. Needless to say I am not a happy camper as I was instructed to pull data from their tables. So third party is one reason.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I understand that there are times when you don't have control of table structures. I mentioned it here because it is the better solution and I don't know if thenaturalselector has the ability or authority to change things.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[thenaturalselector]

yah, i agree with you george, it would make my life easier. i cant change the way these tables are created but i do have the ability to create new tables within a different database. perhaps i should just create a table that aggregates these monthly tables and use that in my procedure?

 

[sparkbyte]

Could you use a select statement to get a list of the tabels you want then use a JOIN or UNION on them?

Thanks

John Fuhrman