SQL Server 2005 - Pass In Name of Table to be Queried via Parameter

Question

Here's the situation. Due to the design of the database I have to work with, I need to write a stored procedure in such a way that I can pass in the name of the table to be queried against if at all possible. The program in question does its processing by jobs, and each job gets its own table created in the database, IE table-jobid1, table-jobid2, table-jobid3, etc. Unfortunately, there's nothing I can do about this design - I'm stuck with it.

However, now, I need to do data mining against these individualized tables. I'd like to avoid doing the SQL in the code files at all costs if possible. Ideally, I'd like to have a stored procedure similar to:

SELECT *
FROM @TableName AS tbl
WHERE @Filter

Is this even possible in SQL Server 2005? Any help or suggestions would be greatly appreciated. Alternate ways to keep the SQL out of the code behind would be welcome too, if this isn't possible.

Thanks for your time.

Answer 1

best solution I can think of is to build your sql in the stored proc such as:

@query = 'SELECT * FROM ' + @TableName + ' as tbl WHERE ' + @Filter

exec(@query)

not an ideal solution probably, but it works.

Answer 2

This would be a meaningless stored proc. Select from some table using some parameters? You are basically defining the entire query again in whatever you are using to call this proc, so you may as well generate the sql yourself.

the only reason I would do a dynamic sql writing proc is if you want to do something that you can change without redeploying your codebase. But, in this case, you are just SELECT *'ing. You can't define the columns, where clause, or order by differently since you are trying to use it for multiple tables, so there is no meaningful change you could make to it.

In short: it's not even worth doing. Just slop down your table specific sprocs or write your sql in strings (but make sure it's parameterized) in your code.

Answer 3

The best answer I can think of is to build a view that unions all the tables together, with an id column in the view telling you where the data in the view came from. Then you can simply pass that id into a stored proc which will go against the view. This is assuming that the tables you are looking at all have identical schema.

example:

create view test1 as

select * , 'tbl1' as src
 from job-1
union all
select * , 'tbl2' as src
 from job-2
 union all
 select * , 'tbl3' as src
 from job-3

Now you can select * from test1 where src = 'tbl3' and you will only get records from the table job-3