Microsoft SQL Server forum - Newbie help with dynamic SQL

Author

Newbie help with dynamic SQL

Erich93063

2005-08-04, 1:25 pm

I am creating a stored procedure that will perform a search against a
table. I am passing search parameters to the SP. If the user did not
select a value on the front end, then I am passing NULL into the SP for
that field. So my question is, what is the best practice to only search
on a field if a value is passed for the given field?

This is what I was thinking, but obviously this doesn't work:

@vchrFieldOne VARCHAR (200) = NULL

SELECT ...
FROM ...
WHERE
0=0

AND
CASE @vchrFieldOne
WHEN NULL THEN (0 = 0)
ELSE (vchrFieldOne = @vchrFieldOne)
END

Tony Sebion

2005-08-04, 1:25 pm

Mine would look something like this:

CREATE PROC pub_info2 @vchrFieldOne varchar(200) = NULL
AS
If (@vchrFieldOne is null)
BEGIN
-- select statement where value is not given
END
ELSE
BEGIN
-- select statement where value is given
END

Hope it helps,
Tony Sebion

"Erich93063" <erich93063@gmail.com> wrote in message
news:1123175211.631764.144360@z14g2000cwz.googlegroups.com:

> I am creating a stored procedure that will perform a search against a
> table. I am passing search parameters to the SP. If the user did not
> select a value on the front end, then I am passing NULL into the SP for
> that field. So my question is, what is the best practice to only search
> on a field if a value is passed for the given field?
>
> This is what I was thinking, but obviously this doesn't work:
>
> @vchrFieldOne VARCHAR (200) = NULL
>
> SELECT ...
> FROM ...
> WHERE
> 0=0
>
> AND
> CASE @vchrFieldOne
> WHEN NULL THEN (0 = 0)
> ELSE (vchrFieldOne = @vchrFieldOne)
> END

Erland Sommarskog

2005-08-04, 1:25 pm

Erich93063 (erich93063@gmail.com) writes:
> I am creating a stored procedure that will perform a search against a
> table. I am passing search parameters to the SP. If the user did not
> select a value on the front end, then I am passing NULL into the SP for
> that field. So my question is, what is the best practice to only search
> on a field if a value is passed for the given field?
>
> This is what I was thinking, but obviously this doesn't work:
>
> @vchrFieldOne VARCHAR (200) = NULL
>
> SELECT ...
> FROM ...
> WHERE
> 0=0
>
> AND
> CASE @vchrFieldOne
> WHEN NULL THEN (0 = 0)
> ELSE (vchrFieldOne = @vchrFieldOne)
> END

It doesn't work for several reasons.

1) WHEN NULL - is the same as "WHEN @charFieldOne = NULL", but in SQL
NULL is never equal to anything, not even another NULL. NULL is a
unknown value, and any comparison with NULL yields the value UNKNOWN.
Correct is WHEN @vcharFieldOne IS NULL.
2) The return value of a CASE expresssion is always an SQL Server data
type, and there is no boolean data type in T-SQL. Thus you cannot
have "THEN (0 = 9".

The normal way to write this is

AND (vchrFieldOne = @vchrFieldOne OR @vchrFieldOne IS NULL)

However, while this works as far as giving the correct result, the
performance can be unbearable. I have an article on my web site that
discusses a number of alternatives for dynamic searches,
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Erich93063

2005-08-04, 1:25 pm

Thanks for the reply. I tried it (At least what I think you meant) but
I get an error (Incorrect syntax near the keyword 'AND' and Incorrect
syntax near the keyword 'END'. Here is what I have:

SELECT ...
FROM...
WHERE 0=0

IF (@vchrFieldOne IS NULL)

BEGIN
AND 0=0
END
ELSE
BEGIN
AND vchrFieldOne = @vchrFieldOne
END

jasonkester

2005-08-04, 1:25 pm

Nice article. Thanks for sharing it.

Erich93063

2005-08-05, 3:24 am

YES, I read your article and used your example for dynamic SQL and it
works PERFECTLY. THANKS!!!!!!!!!!!!!

Sponsored Links