Prev: Is there a Third Party Fuzzy Lookup Control For SSIS
Next: Where to find AdventureWorks database samples
From: rubendn on 16 Mar 2010 18:55
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 16 Mar 2010 19:29
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 16 Mar 2010 20:19
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 16 Mar 2010 21:22
Then review Erland's article:
http://www.sommarskog.se/dyn-search.html

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Is there a Third Party Fuzzy Lookup Control For SSIS
Next: Where to find AdventureWorks database samples