Technical Article

Stored Procedures Inside Stored Procedures

,

One principle that I try to live by is doing my best to do away with redundant tasks.  I do this a lot when working with TSQL.   Over the years I have seen quite a bit of TSQL written by hundreds of developers and I can't tell  you how many times I have seen the same pieces of code copied and pasted over and over again.  This not only looks horrible when read but makes simple stored procedures extremely long and hard to read.  I'm a believer that extremely long code is written by extremely poor developers.  In my mind the shorter the code the better the developer.  But that is just what I think.

I was recently faced with the task of optimizing a process where data is loaded into a staging table and then it was processed.  Every time an update was made to one table, the same 3 tables had to be updated over again.  The original procedure had the same updates over and over again.  One of the rules I faced when optimizing this procedure was that I could not create any new objects, only change the existing one.  What I ended up doing was creating temporary stored procedures inside.  It was like having a GOTO in the procedure where you could keep on processing.

This was an extremely useful find for me and I use it quite often now, everywhere from logging transactions, doing calculations, cleansing data and even processing edi's.

Just wanted to pass this on so it can be used by the world.  I have never read of this being done before.

Chad Miles

DROP PROCEDURE IF EXISTS dbo.RunProcInProc 
GO
CREATE PROCEDURE dbo.RunProcInProc 
AS 
SET NOCOUNT ON;
/** CHAD MILES  12/5/2019  **/DROP TABLE IF EXISTS #DataLog
DROP PROCEDURE IF EXISTS #Success
DROP PROCEDURE IF EXISTS #Failure
CREATE TABLE #DataLog
(ProcessStep VARCHAR(50),
StartDate DATETIME DEFAULT GETDATE(),
StatusDesc VARCHAR(20))
DECLARE @SuccessQry VARCHAR(MAX), @FailureQry VARCHAR(MAX)

SET @SuccessQry = REPLACE('
CREATE PROCEDURE #Success 
@ProcessStep VARCHAR(50)
AS 
SET NOCOUNT ON;
INSERT INTO #DataLog
(ProcessStep, StatusDesc)
VALUES 
(@ProcessStep, |Success|)', '|', CHAR(39))

SET @FailureQry = REPLACE('
CREATE PROCEDURE #Failure 
@ProcessStep VARCHAR(50)
AS 
SET NOCOUNT ON;
INSERT INTO #DataLog
(ProcessStep, StatusDesc)
VALUES 
(@ProcessStep, |Failure|)', '|', CHAR(39))

EXEC (@FailureQry)
EXEC (@SuccessQry)

EXEC #Success @ProcessStep = 'Do Something Right'

EXEC #Failure @ProcessStep = 'Do Something Wrong'

EXEC #Success @ProcessStep = 'Back On Track'
SELECT * FROM #DataLog
GO 

EXEC dbo.RunProcInProc

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

The Basics of Cryptography Part 2

Encryption in SQL Server is difficult to implement and manage, but it is being required more and more often. However understanding what encryption means is as important as being able to manage and implement it. Michael Coles brings us part 2 in his series on explaining the mysteries behind cryptography.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2005-08-17

11,003 reads

SQLServerCentral Article

An Interview with Idera CEO, Rick Pleczko

I was talking with the people over at Idera a few weeks ago and they mentioned that their CEO would be happy to give us a few minutes of his time. So I took them up on the offer and built a list of questions about Idera and SQL Server 2005. Take a moment to learn about the vendor view of the world and the impending release of SQL Server 2005.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2005-03-15

6,097 reads