Sequential Execution of SQL Server Stored Procedures

By: Tim Smith   |   Updated: 2019-05-02   |   Comments   |   Related: > Stored Procedures

Problem

We use a data migration flow that calls multiple SQL Server stored procedures sequentially and must be followed in this order. Our first procedure uses parameters to perform a check against our data and our next procedures only execute if the first procedure passes without returning failures. Sometimes, our second or later procedures rely on other input before executing, not just the first procedure succeeding. We've run this through SQL Server Integration Services (SSIS) in the past and want to migrate some of these to .NET. When we call multiple stored procedures sequentially, especially when the procedures may have different parameter calls, how can we do this with PowerShell as an alternative technique?

Solution

In this development situation, we have a design where we have multiple procedures and latter procedures depend on the execution of the starting procedure (or former procedures, if there are more than 2 procedures and each procedure depends on the former). Because the naming of "first procedure" then "next procedure" can get tricky, for easy-to-read purposes, I've named the procedures as stpFirst and stpSecond. We'll create another first procedure called stpFirst2. This will help us understand the logic of a first procedure being required before a second procedure can be called and not indicative of naming conventions.

We'll contrive a data example where we check if a product exists and is active in our stpFirst and only if the product count is greater than 0, we'll call our second procedure which only tracks the time and count of the inventory (not product). We'll also add a "multiplier" to this count when we look at passing other values from external sources (files, other servers, user input, etc). In this example we're intentionally making our second procedure dependent on our first and we're also intentionally not passing any of the first procedures parameters to the second. While this makes a useful demonstration as to what we can do when we face these situations, actual situations may involve passing in some of the first procedures parameters to the second or other sequential procedures.

CREATE TABLE ProductList(
	ProductId TINYINT,
	Product VARCHAR(10),
	ProductActive BIT
)

CREATE TABLE ProductInventory(
	ProductId TINYINT,
	Amount INT
)

CREATE TABLE InventoryAudit(
	AuditDate DATETIME,
	ProductCount INT
)

INSERT INTO ProductList
VALUES     (1,'Apple',1)
	, (2,'Banana',1)
	, (3,'Cabbage',1)
	, (4,'Dill',0)
	
INSERT INTO ProductInventory
VALUES     (1,100)
	, (2,7)
	, (3,25)
	, (4,0)

CREATE PROCEDURE stpFirst
@productid TINYINT, @active BIT
AS
BEGIN
	DECLARE @count INT = 0
	IF EXISTS (SELECT TOP 1 * FROM ProductList WHERE ProductId = @productid AND ProductActive = 1)
	BEGIN
		SELECT @count = Amount FROM ProductInventory WHERE ProductId = @productid
	END
	SELECT @count ProductCount
END
GO

CREATE PROCEDURE stpSecond
@count INT
AS
BEGIN
	INSERT INTO InventoryAudit
	VALUES (GETDATE(),@count)
END
GO

-- execute the first SP as follows
stpFirst 1,1

-- the output from the first SP is 100, then use this value to call the second SP as follows
stpSecond 100

Call a sequential procedure within a procedure

There are contexts in which I wouldn't use a separate call after the first procedure (stpFirst). Other than security, we can look at this from the view of parameters - if the requirement or parameters of the second procedure (stpSecond) involves action or data from the first (stpFirst) and no other outside parameters or manipulation of existing parameters, we can execute the second procedure from the first and use it to indicate success or failure. If we have new parameters that must be passed to the second procedure after the first procedure executes, we may even choose to add this parameter to the first, provided its input is not based on the actions of the first stored procedure.

In the below code, we'll copy our first procedure that we created and rename it to stpFirst2 as well as edit the procedure to call the second procedure. We'll notice here that our stpFirst2 procedure calls the stpSecond inside the procedure itself if the @count is above 0 while still returning the count. The image shows us the broken down rows affected by the call after the procedure is created and called - we see the selects returning and when we look at the InventoryAudit, we see the audit record since the count was 100.

CREATE PROCEDURE stpFirst2
@productid TINYINT, @active BIT
AS
BEGIN
	DECLARE @count INT = 0
	IF EXISTS (SELECT TOP 1 * FROM ProductList WHERE ProductId = @productid AND ProductActive = 1)
	BEGIN
		SELECT @count = Amount FROM ProductInventory WHERE ProductId = @productid

		IF (@count > 0)
		BEGIN
			EXEC stpSecond @count
		END
	END

	SELECT @count ProductCount
END

-- execute the SP as follows which will also call stpSecond
stpFirst2 1,1

This is a development technique we can use, if we choose to or if we find it more appropriate for our context. When might this be a good development technique to use? A few situations where this could be helpful:

1. When permissions allow for this. It's possible that the user executing stpFirst2 should never call stpSecond. If we don't have this delineation for our users, this may be useful for design.
2. We require no oversight or analysis when the first procedure (stpFirst) completes and calls the second procedure (stpSecond). If the analysis can be done automatically based on output, it may be appropriate to call the second procedure from the first, like we've done in the above code. The reason for this is that if we require human oversight before the second procedure is called, automatically calling it from the first procedure didn't allow for the human oversight.
3. When the first procedure (stpFirst) is used for some form of data validation (removing bad records, cleaning data, etc) that must happen prior to the second procedure. The reason for this is that the first procedure is demarcated in function from the second procedure. Imagine a scenario where we have to debug the second procedure, but it's part of a process with the first procedure. This invites complexity. If the first procedure only validates data and we know it completed, we don't have to worry or consider functional complexity when debugging the second procedure.
4. If the first procedure (stpFirst) returns a count or a data point that can be quickly determined if a debugging situation arises, like our example shows. These cases can be argued both ways, but provided there isn't a significant number of parameters from one to the next, an automatic call may make sense. If we are going to automatically call the first to the next, we may want to log the values passed from one to another, if it falls in this scenario. In addition, I chose this contrived example because we still see the select statement returning the ProductCount at the end. What if we wanted to run this procedure, stpFirst2 without calling stpSecond? If we call a procedure from a procedure, we have to consider these cases - we may want a procedure to be entirely different from another one.

Using PowerShell to retain a returned value

We can use PowerShell to get a value from a procedure and pass the saved value to another procedure in PowerShell identically to using stored procedures. However, we may have situations where we need to wait for user input or get data from an alternate source before moving to the next procedure - in some cases with other data sources, we may be able to use procedures entirely. Sometimes, we may need a tool like SSIS or PowerShell.

In the below examples, we'll use Read-Host, which reads user entry, as a contrived example for "other input" from another source before we can execute our second procedure. The first code snippet uses Invoke-Sqlcmd, which may suffice in some security contexts (our second PowerShell function we create is stricter). We call the below code in PowerShell ISE, input the number 3, and see our data value saved in the InventoryAudit table when we select from it.

[int]$product = 2
[bool]$active = 1

$server = "OurServer"
$db = "Ourdatabase"
$stpFirst = "EXEC stpFirst $product,$active"
$stpSecond = "EXEC stpSecond"

$ProductCount = (Invoke-Sqlcmd -ServerInstance $server -Database $db -Query $stpFirst).ProductCount

[int]$multiplier = Read-Host("Enter a number")
$final = $ProductCount * $multiplier
Invoke-Sqlcmd -ServerInstance $server -Database $db -Query "$stpSecond $final"

For situations where we may want more security and we don't always want to call the second function, but have it as an option to call, we'll create a custom function that will still use Invoke-Sqlcmd in the second function call, but will require strict parameters in the first function call. We see the strict definition of the parameters that link directly to what we have in SQL Server - for instance, our integer range matches the TINYINT definition (0 to 255) along with the boolean definition matching the bit definition. For functionality, we do allow a server and database to be passed to our function. In addition, we have the option to call the second procedure ($callsecond) along with our contrived Read-Host that represents external input before proceeding to the next procedure.

Function Call-ProcedureFirst {
    Param(
        [Parameter(Mandatory=$true)][ValidateRange(0,255)][int]$product
        , [Parameter(Mandatory=$true)][bool]$active
        , [Parameter(Mandatory=$true)][string]$server
        , [Parameter(Mandatory=$true)][string]$database
        , [Parameter(Mandatory=$false)][bool]$callsecond = $false
    )
    Process
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=true"
        
        $cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $scon
        $cmd.CommandTimeout = 0

        $productid = New-Object System.Data.SqlClient.SqlParameter("@productid",$product)
        $isactive = New-Object System.Data.SqlClient.SqlParameter("@active",$active)

        $cmd.CommandText = "EXEC sp_executesql N'EXEC stpFirst @productid,@active',N'@productid TINYINT, @active BIT',@productid,@active"
        $cmd.Parameters.Add($productid) | Out-Null
        $cmd.Parameters.Add($isactive) | Out-Null

        try
        {
            $scon.Open()
            $filldatatable = $cmd.ExecuteReader()
            $returneddata = New-Object System.Data.DataTable
            $returneddata.Load($filldatatable)

            $productcount = $returneddata.Rows.ProductCount
        }
        catch [Exception]
        {
            Write-Warning $_.Exception.Message
        }
        finally
        {
            $scon.Dispose()
            $cmd.Dispose()
        }

        if ($callsecond -eq $true)
        {
            [int]$multiplier = Read-Host("Enter a number") ###For this example, I entered 3
            $final = $productcount * $multiplier
            Invoke-Sqlcmd -ServerInstance $server -Database $database -Query "$stpSecond $final"
        }
    }
}

$server = "OurServer"
$db = "Ourdatabase"
Call-ProcedureFirst -product 1 -active 1 -server $server -database $db -callsecond 1

Remember that our Read-Host in these examples represents the "other" input that we must wait for, enter or extract from other sources before we can call the second procedure. If we don't need to wait for any input or we don't require any other source of data, we may be able to call the second procedure from the first procedure (and if we can tie a data source to our database, we may be able to extract it from within the first procedure). Still, these situations exist and it's helpful to see how we can use a combination of either to solve.

Next Steps

• If the appropriate development situation applies, we can use stored procedures to call other stored procedures and conveniently pass values from one procedure to another procedure. Remember, that we may not want users having the permissions to call a second stored procedure, so this use-case must be evaluated.
• If we need the procedures' functions to be demarcated - meaning a first procedure may not always need to call a second, we may want to avoid tying procedures together when the first procedure is called through developing multiple versions of the first procedure.
• Where tools like PowerShell or SSIS can help us is when our second (or later) procedure relies on other input than simply the first or earlier procedure and this information cannot be obtained on the database level - whether we have to read it from a file, get user input, extract it from another server, etc.
• Underneath this topic is a discussion about accounts that have execute permissions on procedures. We want to make sure that we are as strict as possible with permissions when it comes to allowing accounts to execute procedures.
• This tip involves using PowerShell version 5.1; to determine your version, type $PSVersionTable and review the PSVersion property.

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips