Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 512 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 527 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 534 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 570 Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1244 Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1442 Strict Standards: Redefining already defined constructor for class wpdb in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/wp-db.php on line 306 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/cache.php on line 103 Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/cache.php on line 431 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/query.php on line 61 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/theme.php on line 1109 Strict Standards: Declaration of Walker_Comment::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Redefining already defined constructor for class WP_Dependencies in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/class.wp-dependencies.php on line 31 Strict Standards: Redefining already defined constructor for class WP_Http in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/http.php on line 61 SQL Tidbits » sql

You are currently browsing the archives for the sql category.

Searching with optional parameters

January 16th, 2009

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

The perils of not specifying a variable or parameter length

December 12th, 2008

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)