SQL Server Data Automation Through Frameworks: Building Metadata-Driven Frameworks with T-SQL, SSIS, and Azure Data Factory

Part IStored Procedure-Based Database Frameworks

© Andy Leonard, Kent Bradshaw 2020

A. Leonard, K. BradshawSQL Server Data Automation Through Frameworkshttps://doi.org/10.1007/978-1-4842-6213-9_1

1. Stored Procedures 101

Andy Leonard¹  and Kent Bradshaw²

(1)

Farmville, VA, USA

(2)

Providence Forge, VA, USA

One of the most common issues continually facing IT organizations is finding the proper balance between the effort to develop and deploy processes into production against the efficiency and effectiveness of production control operators. The effort to develop and deploy and the effectiveness of production control seem to be diametrically opposed. Making it easier to develop and deploy processes usually means more work and manual intervention for production control. The real question that needs to be considered is where is it better to feel the pain? Pushing the effort toward the development side of the equation can slow down the throughput but minimizes the liability of issues at the production process level. This and the next few chapters are going to concentrate on processes that are executed using stored procedures. In this chapter, you’ll get a basic introduction to stored procedures that is the foundation for the chapters that follow. You’ll see how to create a child stored procedure, and we’ll provide you a template that you can use to create similar procedures in your own work.

The Need for a Framework

When just in development mode, it is awfully easy (and impressive) to construct large, monolithic procedures that do everything from beginning to end. They are the proverbial black box where something goes in, many gyrations take place, and then the desired result occurs. Those are great until a problem occurs, or just a change in business requirements means that modifications need to be made. When the procedure does so much, what does it take to test it once the modifications are done? Even though the change only impacts 10 percent of the procedure, the entire procedure has to be tested. What does it take to accomplish that? What if there are several intricate modifications that need to be made? How difficult does it become for more than one developer to work on the changes and coordinate their efforts?

Now, consider that monolith broken up into multiple procedures, each of which performs a unit of work. When a change is made and needs to be tested, that effort can be isolated to just what is necessary to perform that unit of work, and the validation is concentrated on the result of the procedure. And, with it now being multiple procedures, modifications can usually be done simultaneously by multiple developers, and their efforts can be mutually exclusive. Over time, that approach can prove to be much more cost effective and efficient.

That is where a framework helps to organize and manage processes to provide the most flexibility in development and can minimize the maintenance effort (which, sometimes, is not considered until it becomes an obvious issue). A framework provides a consistent methodology for assembling and executing processes. It also promotes writing code in small units of work that can potentially be mixed, matched, and reused. It adds complexity to the development and deployment processes but can reduce the effort for production scheduling. The framework can also provide greater flexibility for managing the execution of the process.

Demonstration of a Framework

To begin the analysis of the framework concept, we need a process. Our example to follow shows a framework built to run a daily process against an example schema. The details of that process don’t matter to the example. Just consider that any production system might have something that needs to be done each day, and what follows is a framework by which to make those daily processes happen.

Also, part of the example is a monthly process. Just as a system might need certain tasks to be done each day, it’s also common to have certain things that need to be done once monthly. In designing such a system, one must take into account the order in which daily and monthly processes execute when their schedules intersect on – in our example – the first of each month.

For the purpose of this book, a simple process has been developed (NOTE: all of the code described can be downloaded at entdna.​com. You can also find a link to the code from the book’s catalog page on Apress.​com). Downloading the example code enables you to follow along with the upcoming examples on your own machine.

An Example Schema

Listing 1-1 shows code to create a schema called FWDemo that will contain everything needed for the demonstration. Also, there is code to create a table called FWDemo.ProcessLog. Including a pattern for writing to this table throughout all of the procedures certainly adds some complexity and overhead to the procedures, but what it provides in monitoring and troubleshooting more than makes up for the upfront effort.

print 'FWDemo Schema'

If Not Exists(Select name

              From sys.schemas

              Where name=FWDemo)

begin

  print ' - Creating FWDemo schema'

  declare @sql varchar(255) = 'Create Schema FWDemo'

  exec(@sql)

  print ' - FWDemo schema created'

end

Else

print ' - FWDemo schema already exists.'

print ''

GO

IF  EXISTS (SELECT * FROM sys.objects

            WHERE object_id = OBJECT_ID(N'FWDemo.ProcessLog')

              AND type in (N'U'))

    DROP TABLE FWDemo.ProcessLog

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [FWDemo].[ProcessLog](

      [ProcessLogID]      [int] IDENTITY(1,1) NOT NULL,

      [ProcessLogMessage] [nvarchar](255)     NOT NULL,

      [CreateDate]        [smalldatetime]     NOT NULL

)

GO

SET ANSI_PADDING OFF

GO

Listing 1-1

Schema and log table creation

Do you have a SQL Server instance that you can use for learning purposes? Connect to that instance as an administrator, for example, as the sa user. Then, in SQL Server Management Studio (SSMS), open a New Query window, copy the code from Listing 1-1, and execute it to create the example schema used in this and subsequent chapters.

The Daily Process

Listing 1-2 shows the code to create two stored procedures that will make up our demonstration Daily Process. We are providing two procedures in our example because it’s common to have more than one, and having two allows us to show how to make the execution of subsequent procedures depend upon the success of earlier ones – because the need to execute a series of procedures and halt or take other actions when an error occurs is the real-life scenario that most of us face.

These procedures (as well as all others that we will be using) can be compiled and executed for your own testing. You will notice that there is some code commented out (lines that are preceded with ‘--’) in each procedure that can be invoked (remove the ‘--’, then recompile) to create an error condition. This ability to create an error condition allows testing for successful and unsuccessful completions that will become more important as we progress through demonstration iterations in later chapters.

For the sake of this exercise, we will declare a business rule for the Daily Process stating that FWDemo.DailyProcess1 must complete successfully before FWDemo.DailyProcess2 can be executed. FWDemo.DailyProcess2 must then complete successfully before the Daily Process can be deemed successfully executed.

If Exists(Select s.name + '.' + p.name

          From sys.procedures p

          Join sys.schemas s

            On s.schema_id = p.schema_id

          Where s.name = 'FWDemo'

            And p.name = 'DailyProcess1')

begin

  print ' - Dropping FWDemo.DailyProcess1 stored procedure'

  Drop Procedure FWDemo.DailyProcess1

  print ' - FWDemo.DailyProcess1 stored procedure dropped'

end

GO

CREATE PROCEDURE FWDemo.DailyProcess1

AS

---------------------------------------------------------------------------

---------------------------------------------------------------------------

--

-- Purpose: This procedure is part of the Stored Procedure Framework Demo.

--

-- NOTE: An Error situation can be created for testing/demo purposes by

--    un-commenting the Error code in the body of the procedure.  To return

--    to a procedure with a successful execution, re-comment the code or       --    recompile the original.

--

---------------------------------------------------------------------------

---------------------------------------------------------------------------

SET NOCOUNT ON

/*********************************************/

/*  Log the START of the procedure to the process log  */

/*********************************************/

INSERT INTO FWDemo.ProcessLog (

      ProcessLogMessage,

      CreateDate

)

Values ('Procedure FWDemo.DailyProcess1 - STARTING',

      GETDATE()

)

DECLARE @RetStat int

SET @RetStat = 0

/******************************************/

/*  Force an ERROR CONDITION for this procedure  */

/******************************************/

--INSERT INTO FWDemo.ProcessLog (

--    ProcessLogMessage,

--    CreateDate

--)

--VALUES ('Procedure FWDemo.DailyProcess1 - Problem Encountered',

--    GETDATE()

--)

--SET @RetStat = 1

/****************************************************/

/*  Log the COMPLETION of the procedure to the process log     */

/****************************************************/

IF @RetStat = 0

   BEGIN

      INSERT INTO FWDemo.ProcessLog (

            ProcessLogMessage,

            CreateDate

      )

      VALUES ('Procedure FWDemo.DailyProcess1 - COMPLETED',

            GETDATE()

      )

   END

ELSE

   BEGIN

      INSERT INTO FWDemo.ProcessLog (

            ProcessLogMessage,

            CreateDate

      )

      VALUES ('Procedure FWDemo.DailyProcess1 - ERROR',

      GETDATE()

      )

   END

RETURN @RetStat

GO

If Exists(Select s.name + '.' + p.name

          From sys.procedures p

          Join sys.schemas s

            On s.schema_id = p.schema_id

          Where s.name = 'FWDemo'

            And p.name = 'DailyProcess2')

begin

  print ' - Dropping FWDemo.DailyProcess2 stored procedure'

  Drop Procedure FWDemo.DailyProcess2

  print ' - FWDemo.DailyProcess2 stored procedure dropped'

end

GO

CREATE PROCEDURE FWDemo.DailyProcess2

AS

---------------------------------------------------------------------------

---------------------------------------------------------------------------

--

-- Purpose: This procedure is part of the Stored Procedure Framework Demo.

--

-- NOTE: An Error situation can be created for testing/demo purposes by

--    un-commenting the Error code in the body of the procedure.  To return

--    to a procedure with a successful execution, re-comment the code or

--    recompile the original.

--

---------------------------------------------------------------------------

---------------------------------------------------------------------------

SET NOCOUNT ON

/*********************************************/

/*  Log the START of the procedure to the process log  */

/*********************************************/

INSERT INTO FWDemo.ProcessLog (

      ProcessLogMessage,

      CreateDate

)

Values ('Procedure FWDemo.DailyProcess2 - STARTING',

      GETDATE()

)

DECLARE @RetStat int

SET @RetStat = 0

/******************************************/

/*  Force an ERROR CONDITION for this procedure  */

/******************************************/

--INSERT INTO FWDemo.ProcessLog (

--    ProcessLogMessage,

--    CreateDate

--)

--VALUES ('Procedure FWDemo.DailyProcess2 - Problem Encountered',

--    GETDATE()

--)

--SET @RetStat = 1

/****************************************************/

/*  Log the COMPLETION of the procedure to the process log     */

/****************************************************/

IF @RetStat = 0

   BEGIN

      INSERT INTO FWDemo.ProcessLog (

            ProcessLogMessage,

            CreateDate

      )

      VALUES ('Procedure FWDemo.DailyProcess2 - COMPLETED',

            GETDATE()

      )

   END

ELSE

   BEGIN

      INSERT INTO FWDemo.ProcessLog (

            ProcessLogMessage,

            CreateDate

      )

      VALUES ('Procedure FWDemo.DailyProcess2 - ERROR',

            GETDATE()

      )

   END

RETURN @RetStat

GO

Listing 1-2

Daily Process stored procedures

In a New Query window in SSMS, execute the code from Listing 1-2 while connected to the FWDemo schema. The code creates two stored procedures that together make up a daily process. With those procedures in place, you can turn your attention to the next problem, which is to schedule those procedures to actually run each day.

Executing the Daily Process

Now that an environment has been built and a process created, let’s turn to the execution. Operations staff will have to set up or schedule the procedures to run and either monitor for any error conditions that are raised or set up precedence rules if such a function exists in any scheduling tool used. In a basic sense, we now have a Daily Process that is ready for production. Listing 1-3 shows the statements that can be used to execute the Daily Process procedures and also a SELECT statement that can be run to view the output written to FWDemo.ProcessLog. You will notice that we are ordering the output in a descending order. This will show the most recent messages at the top and eliminate the need to scroll down to get to the messages for the current execution and much easier once the log starts to become heavily populated.

EXECUTE FWDemo.DailyProcess1

EXECUTE FWDemo.DailyProcess2

SELECT ProcessLogID

      ,ProcessLogMessage

      ,CreateDate

FROM FWDemo.ProcessLog

ORDER BY ProcessLogID desc

Listing 1-3

Daily Process execute statements and process log SELECT statement

Including a Monthly Process

Now it’s time to add another layer to our production process. In Listing 1-4, there is code to create two more stored procedures that will make up a Monthly Process. The procedures operate the same as our daily process procedures, and there are some business rules associated with them. First, the monthly process will run on the first day of the month. Second, it will run after the successful execution of the Daily Process, and third, FWDemo.MonthlyProcess1 must complete successfully before FWDemo.MonthlyProcess2 can be executed.

If Exists(Select s.name + '.' + p.name

          From sys.procedures p

          Join sys.schemas s

            On s.schema_id = p.schema_id

          Where s.name = 'FWDemo'

            And p.name = 'MonthlyProcess1')

begin

  print ' - Dropping FWDemo.MonthlyProcess1 stored procedure'

  Drop Procedure FWDemo.MonthlyProcess1

  print ' - FWDemo.MonthlyProcess1 stored procedure dropped'

end

GO

CREATE PROCEDURE FWDemo.MonthlyProcess1

AS

---------------------------------------------------------------------------

---------------------------------------------------------------------------

--

-- Purpose: This procedure is part of the Stored Procedure Framework Demo.

--

-- NOTE: An Error situation can be created for testing/demo purposes by

--    un-commenting the Error code in the body of the procedure.  To return

--    to a procedure with a successful execution, re-comment the code or

--    recompile the original.

--

---------------------------------------------------------------------------

---------------------------------------------------------------------------

SET NOCOUNT ON

/*********************************************/

/*  Log the START of the procedure to the process log  */

/******************************** ************/

INSERT INTO FWDemo.ProcessLog (

      ProcessLogMessage,

      CreateDate

)

Values ('Procedure FWDemo.MonthlyProcess1 - STARTING',

      GETDATE()

)

DECLARE @RetStat int

SET @RetStat = 0

/******************************************/

/*  Force an ERROR CONDITION for this procedure  */

/******************************************/

--INSERT INTO FWDemo.ProcessLog (

--    ProcessLogMessage,

--    CreateDate

--)

--VALUES ('Procedure FWDemo.MonthlyProcess1 - Problem Encountered',

--    GETDATE()

--)

--SET @RetStat = 1

/****************************************************/

/*  Log the COMPLETION of the procedure to the process log     */

/****************************************************/

IF @RetStat = 0

   BEGIN

      INSERT INTO FWDemo.ProcessLog (

            ProcessLogMessage,

            CreateDate

      )

      VALUES ('Procedure FWDemo.MonthlyProcess1 - COMPLETED',

            GETDATE()

      )

   END

ELSE

   BEGIN

      INSERT INTO FWDemo.ProcessLog (

            ProcessLogMessage,

            CreateDate

      )

      VALUES ('Procedure FWDemo.MonthlyProcess1 - ERROR',

            GETDATE()

      )

   END

RETURN @RetStat

GO

If Exists(Select s.name + '.' + p.name

          From sys.procedures p

          Join sys.schemas s

            On s.schema_id = p.schema_id

          Where s.name = 'FWDemo'

            And p.name = 'MonthlyProcess2')

begin

  print ' - Dropping FWDemo.MonthlyProcess2 stored procedure'

  Drop Procedure FWDemo.MonthlyProcess2

  print ' - FWDemo.MonthlyProcess2 stored procedure dropped'

end

GO

CREATE PROCEDURE FWDemo.MonthlyProcess2

AS

---------------------------------------------------------------------------

---------------------------------------------------------------------------

--

-- Purpose: This procedure is part of the Stored Procedure Framework Demo.

--

-- NOTE: An Error situation can be created for testing/demo purposes by

--    un-commenting the Error code in the body of the procedure.  To return

--    to a procedure with a successful execution, re-comment the code or

--    recompile the original.

--

---------------------------------------------------------------------------

---------------------------------------------------------------------------

SET NOCOUNT ON

/*********************************************/

/*  Log the START of the procedure to the process log  */

/********************************************/

INSERT INTO FWDemo.ProcessLog (

      ProcessLogMessage,

      CreateDate

)

Values ('Procedure FWDemo.MonthlyProcess2 - STARTING',

      GETDATE()

)

DECLARE @RetStat int

SET @RetStat = 0

/******************************************/

/*  Force an ERROR CONDITION for this procedure  */

/******************************************/

--INSERT INTO FWDemo.ProcessLog (

--    ProcessLogMessage,

--    CreateDate

--)

--VALUES ('Procedure FWDemo.MonthlyProcess2 - Problem Encountered',

--    GETDATE()

--)

--SET @RetStat = 1

/****************************************************/

/*  Log the COMPLETION of the procedure to the process log     */

/****************************************************/

IF @RetStat = 0

   BEGIN

      INSERT INTO FWDemo.ProcessLog (

            ProcessLogMessage,

            CreateDate

      )

      VALUES ('Procedure FWDemo.MonthlyProcess2 - COMPLETED',

            GETDATE()

      )

   END

ELSE

   BEGIN

      INSERT INTO FWDemo.ProcessLog (

            ProcessLogMessage,

            CreateDate

      )

      VALUES ('Procedure FWDemo.MonthlyProcess2 - ERROR',

            GETDATE()

      )

   END

RETURN @RetStat

GO

Listing 1-4

Monthly Process stored procedures

The execute statements to be run or scheduled by the operations staff are shown in Listing 1-5. Use the SELECT statement introduced earlier to monitor the progress of the process execution.

EXECUTE FWDemo.MonthlyProcess1

EXECUTE FWDemo.MonthlyProcess2

Listing 1-5

Monthly Process execute statements

From an