SQL Server Stored Procedure Best Practices Question

[ease20022002]

Hi, I have been searching for weeks on attempting to find the best practices for running a process that was orignally developed in MS Access, that I had to deconstruct the entire database, implement a Star Schema, and then replace the processing portion.

Essentially, what I did was break-up all the parts that could be reused separately into different stored procedures b.c I don't know if they will be called separately in the future (I suspect they will). So basically I have one stored procedure that executes a bunch of remaining stored procedures. I also put in Try Catch error handling and Transaction mangement in each stored procedure. In each stored procedure listed, they all have the same Try Catch errorhandling and the same Transaction management.

Here is the SP that executes the entire process. If you guys could give me feed back on any practices that I should use (besides dynamic sql...lol, which I did change to the sp_executesql with QuoteName and dbo safeties) I would greatly appreciate it:

USE [FinancialDB]
GO
/****** Object:  StoredProcedure [dbo].[uspTimeDetailProcess]    Script Date: 10/13/2009 10:56:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspTimeDetailProcess]
AS
BEGIN

   --Holds info on whether or not this proc originated the
   --transaction.
   DECLARE @v_trans_started BIT

	BEGIN TRY
		PRINT 'Executing uspRunTimeDetailProcess.'

		SET @v_trans_started = 0
		IF @@TRANCOUNT = 0
		  BEGIN
		     BEGIN TRANSACTION;
		     SET @v_trans_started = 1
		  END
		ELSE
			
			SET @v_trans_started = 0
			 
			 Declare @SelectTable Varchar(20)
			 
			 Set @SelectTable = 'ExtractCognos'			 	
			 
			 EXEC uspTimeDetailDeleteWeek --Deletes the week's records from the TimeDetail table that are in the ExtractCognos table
			 EXEC uspExtractCognosTrim --Trims all fields from import (Move to ETL package)
			 EXEC uspExtractCognosQuotes --Removes quotes from all fields (Move to ETL package) 		 	
			 EXEC uspExtractCognosCreateProject --Creates missing Project Codes 
			 EXEC uspExtractCognosCreatePASDProject --Creates missing PASD Project Codes
			 EXEC uspExtractCognosCreateProgram --Creates Program codes
			 EXEC uspExtractCognosNZ --Removes all Nulls, varchar = 0 length strings, Date = 1/1/9999 and Numeric = 0	
			 EXEC uspExtractCognosResTypChargeCostFTECPs --Calculates Resource Type, Chargeable, Cost, FTEs and Contract Programmers
 			 
-- 			 The following SP Inserts new values from ExtractCognos tbl into Dimension tables (Star Schema) 
			 EXEC uspInsertDimensionValues	'DimYears', 'Year', @SelectTable, 'Year' 			
			 EXEC uspInsertDimensionValues	'DimMonths', 'Month', @SelectTable, 'Month' 			
			 EXEC uspInsertDimensionValues	'DimWeeks', 'Week', @SelectTable, 'Week'	
			 EXEC uspInsertDimensionValues	'DimPrograms', 'Program', @SelectTable, 'Program'	
			 EXEC uspInsertDimensionValues	'DimProjects', 'Project', @SelectTable, 'Project'	
			 EXEC uspInsertDimensionValues	'DimProjectDescriptions', 'ProjectDescription', @SelectTable, 'ProjectDescription'	
			 EXEC uspInsertDimensionValues	'DimUserRespCodes', 'UserRespCode', @SelectTable, 'UserRespCode'	
			 EXEC uspInsertDimensionValues	'DimResources', 'Resource', @SelectTable, 'Resource'	
			 EXEC uspInsertDimensionValues	'DimShortNames', 'ShortName', @SelectTable, 'ShortName'	
			 EXEC uspInsertDimensionValues	'DimCompCodes', 'CompCode', @SelectTable, 'CompCode'	
			 EXEC uspInsertDimensionValues	'DimResourceSources', 'ResourceSource', @SelectTable, 'ResourceSource'	
			 EXEC uspInsertDimensionValues	'DimResourceTypes', 'ResourceType', @SelectTable, 'ResourceType'	
			 EXEC uspInsertDimensionValues	'DimResourceRoles', 'ResourceRole', @SelectTable, 'ResourceRole'	
			 EXEC uspInsertDimensionValues	'DimActivities', 'Activity', @SelectTable, 'Activity'	
			
			 EXEC uspTimeDetailInsertExtractCognos --Inserts ExtractCognos records with Foreignkeys into the FactTimeDetail table			

		IF @v_trans_started = 1
			BEGIN
				SET @v_trans_started = 0
				COMMIT TRANSACTION;
			END

   END TRY

   BEGIN CATCH

      IF @v_trans_started = 1
         BEGIN
            SET @v_trans_started = 0
            ROLLBACK TRANSACTION;
         END
      RAISERROR ('Error in uspRunTimeDetailProcess!',16,1)

   END CATCH
END

Thanks a bunch for your guys help. This forum is awesome.

FYI, the entire process works and balances.

Paul

 

[RiverGuy]

I would recommend that you do not wrap a big ol' transaction around everything. Get more granular with your transactions and design them so each logical piece that should succeed or fail together is inside it's own transaction.

 

[ease20022002]

Hi, RiverGuy. Thanks for the reply. I need this entire transaction to fail b.c it will be initiated by an ETL package and any errors that arrise in the process, I want the entire transaction to fail and have everything rolled back. I do have the granularity you suggest as each SP that is exectued has the same transaction code, but there are conditionals to avoid any transaction managment if the SP didn't begin the process. And each SP does have groups of T-SQL that manage updates, inserts, etc., that are grouped together, for the most part.

Thanks for the response. I think your statement is defnitely relevant for other processes I am going to develop in this db.

Thanks again.
Paul

 

[RiverGuy]

I'm sure you've already gone through the pros and cons of your approach. There are a few different practices or schools of though in dimensional modeling.

I've seen scenarios where each dimension table was loaded one row at a time. If there was a problem with a row, it would be redirected to a special investigation table, which would then attempt to be reloaded on the next run.

The methodology I used is to load each dimension at a time. If I have an issue with a supplier dimension for example, I don't want to not load my customer dimension.

Then the methodology you are using is to not load any dimensions if one of them fails. Besides the obvious, the other issue I see with that is that you can end up with locks being held too long on your tables. However, this is often not a concern if you are doing nightly loads and no one is using the system at this time anyways.

 

[ease20022002]

Thanks again. I will definitely take what you have said into consideration. If the ETL packages were more complex, what you state would be a huge issue with what I am doing. If you have time and there is anything else you can think of, I appreciate the comments.

Paul