"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
, = 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 /[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 ='@MySPParm1'
THEN [value]
ELSE @MySPParm1
END
,@MySPParm2 = CASE
WHEN ='@MySPParm2'
THEN [value]
ELSE @MySPParm2 END
,@MySPParm3 = CASE
WHEN ='@MySPParm3'
THEN [value]
ELSE @MySPParm3
END
FROM dbo.[Configurations] a
CROSS APPLY dbo.DelimitedSplit8K(ConfigValue, ',') b
CROSS APPLY ( SELECT = 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 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 ='@MySPParm1'
THEN [value]
ELSE @MySPParm1
END
)
,@MySPParm2 =
ISNULL(@MySPParm2
,CASE WHEN ='@MySPParm2'
THEN [value]
ELSE @MySPParm2
END
)
,@MySPParm3 =
ISNULL(@MySPParm3
,CASE WHEN ='@MySPParm3'
THEN [value]
ELSE @MySPParm3
END
)
FROM dbo.[Configurations] a
CROSS APPLY dbo.DelimitedSplit8K(ConfigValue, ',') b
CROSS APPLY ( SELECT = 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
GOTo 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 = LEFT(Item, CHARINDEX('=', Item) - 1)
,[value] = RIGHT(Item, LEN(Item) - CHARINDEX('=', Item))
) c
WHERE EnterpriseID='ACME' AND ='@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 = LEFT(Item, CHARINDEX('=', Item) - 1)
,[value] = RIGHT(Item, LEN(Item) - CHARINDEX('=', Item))
) c
WHERE EnterpriseID=@EnterpriseID AND ='@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
