Stored procedures - Dynamically Changing Tables.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored procedures - Dynamically Changing Tables.

Author

Topic

karthi2009
Starting Member

23 Posts

Posted - 2009-06-19 : 02:09:42

Hi Team,

We have 180 Stored procedures for reports. We are maintaining 2 tables with same structure - one table which holds current week data, and another table who holds archive data(till last week). When we give a Date of the current week, it should retrieve the records from first table. If we give past Dates, it should retrieve the records from Archive table.

I don't want to use dynamic queries in stored procedures.
Please suggest me a solution which can be obtained from a minimal changes in the stored procedures..

Thanks & Regards
Karthik

Karthikeyan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-19 : 02:21:12

select columns from newtable where date_col=@date
union all
select columns from archive_table where date_col=@date

Madhivanan

Failing to plan is Planning to fail

saran_d28
Starting Member

36 Posts

Posted - 2009-06-19 : 04:08:53

Create two stored procedures, one procedure use the fact table and another one use the archive table (dont change anything in the proc, Just rename the tables only).

create procedure redirect(@date1 date)
as
begin
if @date < '19-jun-2009'
exec sp_old
else
exec sp_new
end;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources