SQL Tidbits

Sometimes we need to have optional parameters to a query. This is a very common scenario for searches, for instances. So how do you handle optional parameters? I’ve created a small script below where I query a table with two parameters, where I can use either one or two. By using ISNULL() I get a clean and nice way to set the parameters if necessary.

Please note query #1, where both parameters are null. Then all rows are returned.

CREATE TABLE #temp (n NVARCHAR(1), f NVARCHAR(1))

INSERT INTO #temp VALUES ('a', 'b')
INSERT INTO #temp VALUES ('a', 'c')
INSERT INTO #temp VALUES ('b', 'c')
INSERT INTO #temp VALUES ('e', 'd')

DECLARE @n AS NVARCHAR(1)
DECLARE @f AS NVARCHAR(1)

SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)

SET @n = 'a'
SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)

SET @f = 'c'
SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)

SET @n = NULL
SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)

DROP TABLE #temp   

Results:

n    f
---- ----
a    b
a    c
b    c
e    d

n    f
---- ----
a    b
a    c

n    f
---- ----
a    c

n    f
---- ----
a    c
b    c

When declaring a variable or parameter as varchar or nvarchar, you might get into some nasty surprises if you don’t specify the number of characters in the data. If you are declaring a variable like DECLARE @string AS VARCHAR, you are going to end up with a string that only holds 1 character. That’s probably not what you intended, but rather DECLARE @string AS VARCHAR(50) or similar.

The behaviour is similar when using CAST and CONVERT. Then, the default is to limit the data to 30 characters.

Some examples of this:

DECLARE @string AS VARCHAR
SET @string = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit'

SELECT
'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS [Original string],
LEN('Lorem ipsum dolor sit amet, consectetur adipisicing elit') AS [Length of original string],
@string AS [varchar],
CAST('Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS VARCHAR) AS [Casted varchar],
LEN(CAST('Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS VARCHAR)) AS [Length of casted varchar]
GO

DECLARE @string AS NVARCHAR
SET @string = N'Lorem ipsum dolor sit amet, consectetur adipisicing elit'

SELECT
N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS [Original string],
LEN(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit') AS [Length of original string],
@string AS [nvarchar],
CAST(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS NVARCHAR) AS [Casted nvarchar],
LEN(CAST(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS NVARCHAR)) AS [Length of casted nvarchar]

Results:

Original string                                          Length of original string varchar Casted varchar                 Length of casted varchar
-------------------------------------------------------- ------------------------- ------- ------------------------------ ------------------------
Lorem ipsum dolor sit amet, consectetur adipisicing elit 56                        L       Lorem ipsum dolor sit amet, co 30

(1 row(s) affected)

Original string                                          Length of original string nvarchar Casted nvarchar                Length of casted nvarchar
-------------------------------------------------------- ------------------------- -------- ------------------------------ -------------------------
Lorem ipsum dolor sit amet, consectetur adipisicing elit 56                        L        Lorem ipsum dolor sit amet, co 30

(1 row(s) affected)