Optional Parameters

Optional Parameters
Unlike many programming languages, SQL does not lend itself to optional parameters, especially when using stored procedures. I’m going to share a SQL tip today regarding different methods to implementing optional parameters. All code is written in TSQL for MS SQL Server, but may be modified to work in other SQL engines.

For this demonstration I’m going to use a simple address table. The address table has both a UserID and an AddressID, both participating in the primary key. This results in a single unique clustered index on the table that may be used to locate any address. For this index to work best, you must supply both a UserID and AddressID for any query.

CREATE TABLE Address
(
AddressID INT NOT NULL
,UserID INT NOT NULL
,City VARCHAR(35) NOT NULL
,State CHAR(2) NOT NULL
,Zip CHAR(5) not null
,CONSTRAINT PK_Address
PRIMARY KEY CLUSTERED (UserID, AddressID)
)

I’m going to populate the table with a few rows for demonstration purposes.

INSERT INTO Address
SELECT 1, 1, 'Lakewood', 'CA', '90712'
UNION ALL SELECT 2, 1, 'Bellflower', 'CA', '90805'
UNION ALL SELECT 3, 2, 'Lakewood', 'CA', '90714'

With these rows in place I can query the table and return all addresses for a single user.

SELECT *
FROM Address
WHERE UserID = 2

I can also return a specific address for a single user, etc.

SELECT *
FROM Address
WHERE UserID = 1
AND AddressID = 2

Both of these queries result in a different query plan in the SQL Server plan cache. Even if they were written using parameters, it would still result in two separate query plans, because the queries have a different where clause.

What if you could write your query so you always provide the UserID, and optionally provide the AddressID? Here are a few ways I have found allowing you to make the AddressID Optional. I have the examples here using Declared variables…you could do the same using Stored Procedure Parameters or Function Parameters.

The Five Methods are:

• Set the Optional Parameter to Null and test for Null or Equal
• Set the Optional Parameter to Null and use a Case Statement for comparison
• Set the Optional Parameter to Null and use the ISNULL() function for resolution
• Use a Range With BETWEEN
• Use a Range with > AND <

Set the Optional Parameter to Null and test for Null or Equal


DECLARE @UserID INT = 1
DECLARE @AddressID INT = NULL


SELECT *
FROM Address
WHERE UserID = @UserID
AND
( @AddressID IS NULL
OR AddressID = @AddressID
)

Set the Optional Parameter to Null and use a Case Statement for comparison

DECLARE @UserID INT = 1
DECLARE @AddressID INT = NULL

SELECT *
FROM Address
WHERE UserID = @UserID
AND AddressID =
CASE
WHEN @AddressID IS NULL THEN AddressID
ELSE @AddressID
END

Set the Optional Parameter to Null and use the ISNULL() function for resolution

DECLARE @UserID INT = 1
DECLARE @AddressID INT = NULL

SELECT *
FROM Address
WHERE UserID = @UserID
AND AddressID = ISNULL(@AddressID, AddressID)

Use a Range With BETWEEN

DECLARE @UserID INT = 1
DECLARE @MinAddressID INT = 0
DECLARE @MaxAddressID INT = 99999999

SELECT *
FROM Address
WHERE UserID = @userID
AND AddressID BETWEEN @MinAddressID AND @MaxAddressID

Use a Range with > AND <

DECLARE @UserID INT = 1
DECLARE @MinAddressID INT = 0
DECLARE @MaxAddressID INT = 99999999

SELECT *
FROM Address
WHERE UserID = @userID
AND AddressID > @MinAddressID
AND AddressID < @MaxAddressID

Here are some examples of the Range Methods when you wish to specify a parameter.

Range > AND < Method Searching for AddressID = 1


DECLARE @UserID INT = 1
DECLARE @MinAddressID INT = 0
DECLARE @MaxAddressID INT = 2

SELECT *
FROM Address
WHERE UserID = @userID
AND AddressID > @MinAddressID
AND AddressID < @MaxAddressID

Between Method Searching for AddressID = 1

DECLARE @UserID INT = 1
DECLARE @MinAddressID INT = 1
DECLARE @MaxAddressID INT = 1

SELECT *
FROM Address
WHERE UserID = @userID
AND AddressID BETWEEN @MinAddressID AND @MaxAddressID

I know there is nothing magic here. Guess which method has the most functionality and best performance? Either one of the range methods result in a consistent query plan because real numbers are always specified in the where clause, and are not calculated on a per row basis. Additionally, they both take advantage of the Clustered index on the Address table.

The Range > AND < method is a little faster than between. This is because BETWEEN is altered by the query optimizer to AddressID >= 1 AND AddressID <=1. So, there are two additional operations there…but using between, you don’t have to adapt the value as I did in the example above to AddressID > 0 AND AddressID < 2.

I hope this has been helpful and practical. It’s the little tweaks like this that can sometimes really make a query sing. Not only that, you will notice that I did not have to create an additional index on the table serving the two different columns. Reduction of indexes is always a performance enhancement as long as you can always efficiently retrieve your data.

Hope you enjoyed this little tip. Feel free to add your comments by sending them to btaylor@sswug.org.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
Data Security: The Competitve Advantage
In an environment where information needs to mobile and accessible—and yet protected—a data-centric security strategy represents a critical business differentiator.

Featured White Paper(s)
How to Implement an Effective SharePoint Governance Plan
Written by AvePoint

Moving past the "what" and "why" of governance, an even… (read more)