SQL Server - Schema
Posted on 2014-03-12
I have two set of tables with the same name/structures/data but different schema; one set of tables with "TRP" schema and another set of tables with "DEV" schema. The TRP schema is for the production tables, and the DEV schema is for the testing tables.
I have a procedure to run using those tables. There are occasions where I need to run this procedure using the testing tables (tables with DEV schema). How do I specify in my procedure which set of tables to use on the top so that I don't have to go into every lines in the codes and change the schema of the table?
My procedure is very long, I'm just pasting here the first few lines of my procedure so that you can tell me where and how and can modify my procedure to use the DEV or the TRP set of tables in the procedure.
ALTER PROCEDURE [TRP].[FFS_validation_check]
-- Add the parameters for the stored procedure here
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--updating DealStatus on APC/DRG according to CTR
declare @intCheckCount smallint
-- reset TRP.Validation_check/detail
DELETE FROM TRP.Validation_Check where source = 'ffs'
DELETE FROM TRP.Validation_Check_detail where source = 'ffs'
--PRINT '1-- Check_if Year <> Year Plan/Act'
INSERT INTO [PSF_Gen].[TRP].[Validation_check](Insert_dt,[Source],[Check_point],[Rec_count])
SELECT getdate(), 'FFS', 'Check_01',
count(*) as Check_1 from (
SELECT 'Check_01' as Check_point_1, FFSContr_ID, Par_id, Trend_type, TypeOfContract, [Year], year(PlanEffDt) as Year_Plan, year(ActEffDt) as Year_Actual, DealStatus, max(updatedt) as UpdateDt
WHERE year(PlanEffDt) <> 2009 and (year(PlanEffDt) <> [Year] and year(ActEffDt) <> [Year])
and Par_id not in ('770324630 A','940535360 A','951816017 A','952282647 A','952294234 A','956064971 A')
GROUP BY FFSContr_ID, Par_id, Trend_type, TypeOfContract, [Year], year(PlanEffDt), year(ActEffDt), DealStatus
) x having count(*) <> 0