Variable Default Values to a Stored Procedure

   Recently I encountered an interesting problem while writing a SQL Stored Procedure (SP) for an enterprise-grade application that is designed to support multiple enterprises.  Throughout the application, there are configurations that drive the way it behaves, based on the enterprise assigned to the user that has logged in.  In this case, I had a SP that needed to have several control parameters that tailored the results of the embedded query in various ways.
Enterprises tend to have different needs; hence one flavor does not usually satisfy all.  In this case, the default behavior of the SP could need to be different depending on the enterprise that was using it.  Furthermore, all enterprises needed to have the same flexibility in the SPs behavior so it was necessary to be able to override the default behavior depending on control selections entered from the User Interface (UI) form.

An Enterprise Configurations Table

To support any high-grade, multi-enterprise you will need to have some sort of configurations table.  You may or may not have a form that updates this table (perhaps it can be configured only by script).
Let’s propose a format for this table and populate it with some data.

CREATE TABLE dbo.[Configurations] (
     EnterpriseID    VARCHAR(40)
    ,ConfigName     VARCHAR(40)
     ,ConfigValue    VARCHAR(8000) 
);

INSERT INTO [Configurations] 
 SELECT 'ACME', 'MySP', '@MySPParm1=1,@MySPParm2=2,@MySPParm3=3'
 UNION ALL 
 SELECT 'ACE', 'MySP', '@MySPParm1=4,@MySPParm2=5,@MySPParm3=6'
 UNION ALL 
 SELECT 'ALLIED', 'MySP', '@MySPParm1=7,@MySPParm2=8,@MySPParm3=9'

SELECT *
 FROM dbo.[Configurations];

In our configurations table, we have created rows for 3 enterprises.  The ConfigName column is a unique identifier of the configurations that will apply for a specific enterprise when using the SP we will construct.  Each of the configuration values are chosen to be unique to emphasize the results.
The data stored in this table appears as follows:

EnterpriseID  ConfigName  ConfigValue
------------- ----------- ------------
ACME          MySP        @MySPParm1=1,@MySPParm2=2,@MySPParm3=3
ACE           MySP        @MySPParm1=4,@MySPParm2=5,@MySPParm3=6
ALLIED        MySP        @MySPParm1=7,@MySPParm2=8,@MySPParm3=9

Note also that the string we’ve saved as ConfigValue, is somewhat self-documenting, in that it references the name of the SP’s local variable along with the value it will be assigned.

Parsing the Configurations

We have chosen to include our configurations for this SP as a delimited list of parameters, and our objective will be to assign the value of each of the configurations to a local variable inside of our SP.  Those local variables will control the desired results delivered by the SP.
Parsing the configurations is pretty easy, by making use of the well-known, high-performance, community delimited string splitter popularized by SQL MVP Jeff Moden, better known as DelimitedSplit8K (this FUNCTION can be downloaded at the link provided).  So let’s do that and look at the results.  Any delimited string splitter FUNCTION will do of course, but we’ve had exceptional results with this one in the past and my feeling is that it should be in every developer’s tool box.

SELECT ConfigValue, ItemNumber, Item
 FROM dbo.[Configurations]
   CROSS APPLY dbo.DelimitedSplit8K(ConfigValue, ',')
 WHERE EnterpriseID = 'ACME';

For this example, we’ve chosen only to retrieve the parameters for ACME.  Let’s take a quick look at those results.

ConfigValue                              ItemNumber  Item
------------                             ----------- -----
@MySPParm1=1,@MySPParm2=2,@MySPParm3=3   1           @MySPParm1=1
@MySPParm1=1,@MySPParm2=2,@MySPParm3=3   2           @MySPParm2=2
@MySPParm1=1,@MySPParm2=2,@MySPParm3=3   3           @MySPParm3=3

Our initial query has each of the parameters split to its own row.  Now all we need to do is to figure out a way to parse the key/value pair on the equal sign, and then assign the value to a local variable.  That first part is pretty easy.

SELECT ItemNumber, Item
    ,[key]   = LEFT(Item, CHARINDEX('=', Item) - 1)
    ,[value] = RIGHT(Item, LEN(Item) - CHARINDEX('=', Item))
FROM dbo.[Configurations]
CROSS APPLY dbo.DelimitedSplit8K(ConfigValue, ',')
WHERE EnterpriseID = 'ACME';

Those results look like this.

ItemNumber  Item          key         value
----------- -----         ----        ------
1           @MySPParm1=1  @MySPParm1  1
2           @MySPParm2=2  @MySPParm2  2
3           @MySPParm3=3  @MySPParm3  3

Now is where it gets just a bit tricky.

The Local Variable Overlay

We’ll use a technique I’ve heard referred to as the Variable Overlay.  You’ve probably all seen it before and will recognize it, but perhaps you haven’t heard it described this way.  First we’ll define 3 local variables that will be “overlaid” by the results from the query and then we’ll perform the overlay.  The creation of [key]/[value] pair will be moved down to a CROSS APPLY structure to improve the readability.

DECLARE @MySPParm1   TINYINT
       ,@MySPParm2   TINYINT
       ,@MySPParm3   TINYINT;

SELECT @MySPParm1 = CASE 
    WHEN [key]='@MySPParm1' 
     THEN [value] 
     ELSE @MySPParm1 
    END
    ,@MySPParm2   = CASE
     WHEN [key]='@MySPParm2'
      THEN [value]
      ELSE @MySPParm2 END
    ,@MySPParm3   = CASE 
    WHEN [key]='@MySPParm3' 
     THEN [value] 
     ELSE @MySPParm3 
    END
 FROM dbo.[Configurations] a 
 CROSS APPLY dbo.DelimitedSplit8K(ConfigValue, ',') b 
 CROSS APPLY ( SELECT [key] = LEFT(Item, CHARINDEX('=', Item) - 1)
                     ,[value] = RIGHT(Item, LEN(Item) - CHARINDEX('=', Item))
              ) c
 WHERE EnterpriseID = 'ACME' AND ConfigName = 'MySP';

SELECT [@MySPParm1]=@MySPParm1,
       [@MySPParm2]=@MySPParm2, 
       [@MySPParm3]=@MySPParm3;

Our results are now just what we need!

@MySPParm1  @MySPParm2  @MySPParm3
1           2           3

The part referred to as a “variable overlay” is using the SELECT to assign to the local variable.  It works because the value of each parameter starts as a NULL and remains a NULL until a [key] matches the name specified in the CASE.  Subsequent rows simply reassign the parameter’s current value to itself.

Overriding the Default Configurations in a SP

We’ll demonstrate this approach using a SP but the same will work in a FUNCTION, the sole difference being that in a FUNCTION call you must supply all input parameters and not just the ones you want to override.
So here’s a pretty simple SP that does just what we need.  Of course normally, as I had to do, the use of the local variables to control the behavior of my SP was much more complex.  But for us we just need to demonstrate that the override works.

CREATE PROCEDURE dbo.MySP (
     @EnterpriseID    VARCHAR(40)
    ,@MySPParm1      TINYINT = NULL
    ,@MySPParm2      TINYINT = NULL
    ,@MySPParm3      TINYINT = NULL )
AS
BEGIN
 SELECT @MySPParm1 =
             ISNULL(@MySPParm1 
                   ,CASE WHEN [key]='@MySPParm1' 
                          THEN [value] 
                          ELSE @MySPParm1 
                    END    
                    )
       ,@MySPParm2   =
             ISNULL(@MySPParm2
                   ,CASE WHEN [key]='@MySPParm2' 
                          THEN [value] 
                          ELSE @MySPParm2 
                    END
                    )
       ,@MySPParm3   =
             ISNULL(@MySPParm3
                   ,CASE WHEN [key]='@MySPParm3' 
                          THEN [value] 
                          ELSE @MySPParm3 
                    END
                    )
  FROM dbo.[Configurations] a
     CROSS APPLY dbo.DelimitedSplit8K(ConfigValue, ',') b
     CROSS APPLY ( SELECT [key]   = LEFT(Item, CHARINDEX('=', Item) - 1)
                  ,[value] = RIGHT(Item, LEN(Item) - CHARINDEX('=', Item))
                  ) c
  WHERE EnterpriseID = @EnterpriseID AND ConfigName = 'MySP';

  SELECT [@MySPParm1]=@MySPParm1
       , [@MySPParm2]=@MySPParm2 
       ,[@MySPParm3]=@MySPParm3; 
END
GO

To apply the default value passed to the SP to override the configured value, we have added the ISNULL part of each variable overlay assignment.  This ensures that the initial value of the parameter overrides any subsequent values obtained from the configurations.  Default values of NULL are provided so parameters to the SP are optional.
Let’s take a look at some results.

EXEC dbo.MySP @EnterpriseID='ACME';

EXEC dbo.MySP @EnterpriseID='ACME', @MySPParm1=10, @MySPParm3=12;

The first case returns the defaults for ACME.

@MySPParm1  @MySPParm2  @MySPParm3
1           2           3

The second case overrides @MySPParm1 with 10 and @MySPParm3 with 12.

@MySPParm1  @MySPParm2  @MySPParm3
10          2           12

Try it yourself to see how it returns different defaults for different enterprises and how each can be overridden.

Setting the Default Value for a Parameter Based on a Sub-query

You may be asking yourself why I didn’t just set the default value for each input parameter using a sub-query to retrieve the enterprise configuration for the parameter.  First let’s construct a query that theoretically extracts the configuration for ACME for @MySPParm1:

SELECT [value]
FROM dbo.[Configurations] a
CROSS APPLY dbo.DelimitedSplit8K(ConfigValue, ',') b
CROSS APPLY (
    SELECT [key] = LEFT(Item, CHARINDEX('=', Item) - 1)
        ,[value] = RIGHT(Item, LEN(Item) - CHARINDEX('=', Item))
    ) c
WHERE EnterpriseID='ACME' AND [key]='@MySPParm1';

It would be nice if you could generate the default value of the input parameter with a sub-query, sort of like this.

CREATE PROCEDURE MySP
(
    @EnterpriseID    VARCHAR(40)
    ,@MySPParm1      TINYINT =
        (
         SELECT [value]
         FROM dbo.[Configurations] a
         CROSS APPLY dbo.DelimitedSplit8K(ConfigValue, ',') b
         CROSS APPLY (
             SELECT [key] = LEFT(Item, CHARINDEX('=', Item) - 1)
                 ,[value] = RIGHT(Item, LEN(Item) - CHARINDEX('=', Item))
             ) c
         WHERE EnterpriseID=@EnterpriseID AND [key]='@MySPParm1'
        )
    ,@MySPParm2      TINYINT = NULL
    ,@MySPParm3      TINYINT = NULL
)

Unfortunately, it is not supported (at least not in SQL 2008 R2).