SQL Server 2008 get data from unified tables

Question

I've a stored proc like:

CREATE PROCEDURE [dbo].[GetData]
    @code varchar(10)
AS
BEGIN
    SET NOCOUNT ON;

    --code goes here
END

the proc reads data from one of n tables, based on @code passed. So I've a map linking codes with the actual table names, ex.

Code:"A" => dbo.JAN_SALES   
Code:"B"=> dbo.FEB_SALES

All tables have the same structure. I know how to get it done by using 'red' sql, wonder if there's more elegant/performant way of doing that with SQL Server 2008?

Edit 1 - Red sql is the sql, which gets built by concatenating its parts and executed by calling something like exec('select A. B, C from ' + @myTable).

If all the tables have the same structure, is it possible to have a single table with a "type" column (or "month" column, based on your table names)? — Tim Medora
I can't change the DB structure. The data is splitted by a number of physical tables due to a reason. — user572559
Just to be clear to all who may run into this red sql is dynamic sql correct? — Kuberchaun

G21 G21 · Accepted Answer · 2012-03-28T19:16:35

Your question seems to be clear on what needs to be done, and there are no much more posibilities than creating the T-SQL code by
1) adding IF blocks evaluating the @code parameter; or
2) using Dynamic Sql ( dynamic sql = "red" ). Please note the dynamic sql is strongly discouraged for production environments.

BTW - On SQL Server 2008 you can use *sp_executesql* proprietary stored procedure. Microsoft's MSDN describes how it works here.

SQL Server 2008 get data from unified tables

1 Answers