From: rubendn on
I am creating a stored procedure that will have a BIT type parameter that
will be optional. If that parameter is not included then it should return
all values (1 and 0). I've put together the SP below but I am wondering if
there is a cleaner way to handle this situation.

CREATE PROCEDURE [dbo].[GetItems]
@IsInsert Bit = NULL,
@ItemYear Int = NULL
AS
BEGIN

SET NOCOUNT ON;

SELECT * FROM Item WHERE ItemYear = COALESCE(@ItemYear, '%') AND (IsInsert
= COALESCE(@IsInsert, 1) OR IsInsert = COALESCE(@IsInsert, 0))

END
From: Plamen Ratchev on
From performance stand point it will be better to split the logic with IF:

IF @IsInsert IS NULL
SELECT <columns> FROM Item;
ELSE
SELECT <columns> FROM Item WHERE IsInsert = @IsInsert;

Also, instead of using COALESCE you can do:

SELECT <columns> FROM Item WHERE IsInsert = @IsInsert OR @IsInsert IS NULL;

Here is a great article with more details:
http://www.sommarskog.se/dyn-search.html

--
Plamen Ratchev
http://www.SQLStudio.com
From: rubendn on
Thanks for the fast response.

My problem is that this is just a small example. My actual Select statement
would have like 15-20 parameters for which a value may or may not be set.

Thanks.

"Plamen Ratchev" wrote:

> From performance stand point it will be better to split the logic with IF:
>
> IF @IsInsert IS NULL
> SELECT <columns> FROM Item;
> ELSE
> SELECT <columns> FROM Item WHERE IsInsert = @IsInsert;
>
> Also, instead of using COALESCE you can do:
>
> SELECT <columns> FROM Item WHERE IsInsert = @IsInsert OR @IsInsert IS NULL;
>
> Here is a great article with more details:
> http://www.sommarskog.se/dyn-search.html
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
From: Plamen Ratchev on
Then review Erland's article:
http://www.sommarskog.se/dyn-search.html

--
Plamen Ratchev
http://www.SQLStudio.com