Newbie help with dynamic SQL

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)
ENDMine 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.googlegr oups.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|||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|||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|||Nice article. Thanks for sharing it.|||YES, I read your article and used your example for dynamic SQL and it
works PERFECTLY. THANKS!!!!!!!!!!!!!