Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Variable Default Values to a Stored Procedure (SQL Spackle)

By Dwain Camps,

"SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks."

--Phil McCracken

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).

Conclusion

We have demonstrated a way to assign configurations to the local variables in a Stored Procedure and override the default configurations by passing in the override as one of the SP’s input parameters.

Now I am no rocket scientist and this is not rocket science I know, but still this is an approach that might not be considered a run-of-the-mill use of a variable overlay assignment.

We hope you find that this bit of SQL Spackle fills a crack!

Dwain Camps

SQL Enthusiast

Skype: dwaincamps

Total article views: 6762 | Views in the last 30 days: 14
 
Related Articles
FORUM

configuration

configuration

FORUM

Help ! Can't login, SELECT permission denied .......

SELECT permission denied on object 'configurations', database 'mssqlsystemresource', schema 'sys'

BLOG

SQL Server Silent unattended installation using configuration file

1) Open Installation Wizard Open Installation Wizard 2) Select Feature You want to install Select...

FORUM

SSIS Package Configuration

sql server package configuration

BLOG

How can I configure SQL Server to listen on multiple TCP/IP ports?

Run the SQL Server Configuration Manager, expand SQL Server Network Configuration and select the pro...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones