There must be a clever way 1

[donutman]

I believe there's a way to select e.g. either all customers from a table or just one customer from a table when given either a customer's unique id (alpha characters) or a null value as the parameter input to a stored procedure. Of course it is possible to just use an IF Else block, but isn't there a way to do it within one Select statement with a cleverly written boolean expression in the where clause?


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[djj55]

SELECT * FROM mytable WHERE Client LIKE ISNULL(@client, '%')

Written on the fly so may not work.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[DaveInIowa]

A star for djj for a unique way of solving this problem.

[green]-- Normally I use this technique ...[/green]
SELECT * FROM mytable WHERE (@customerId IS NULL OR customerId = @customerId)

[green]-- djj's answer led me to try this ...[/green]
SELECT * FROM mytable WHERE customerId = COALESCE(@customerId, customerId)

 

[gmmastros]

The method suggested by djj55 only works if the column does not allow nulls.

Here is an example:

Declare @Fruit Table(Id Int Identity(1,1), Name VarChar(20))

Insert Into @Fruit(Name) Values('Apple')
Insert Into @Fruit(Name) Values('Banana')
Insert Into @Fruit(Name) Values('')
Insert Into @Fruit(Name) Values(NULL)
Insert Into @Fruit(Name) Values('Strawberry')

Select * From @Fruit

Declare @Name VarChar(20)

-- The following code selects just one row
Set @Name = 'Banana'
SELECT * FROM @Fruit WHERE Name LIKE ISNULL(@Name, '%') 

-- The following code "should" select all rows, but doesn't because of the NULL in Row 6
Set @Name = NULL
SELECT * FROM @Fruit WHERE Name LIKE ISNULL(@Name, '%')

The technique of "WHERE (@customerId IS NULL OR customerId = @customerId)" always works. Unfortunately, this can cause havoc with unexpected things. The biggest problem with this code is that it is not sargable, so an index on the column would be ignored and you are left with an index scan instead of seek.

If you choose the IF/ELSE block method, looking for a single row would be sargable. Unfortunately, SQL may end up caching the execution plan with an index scan instead of seek, so you're no better off. This would be the old "Parameter Sniffing" problem.

As much as it sucks, there are a couple ways around this.

You could write separate stored procedures, one with the parameter and the other without it. Then, a 3rd procedure that looks like:

If @Parameter Is NULL
  Exec Procedure_Without_Parameter
Else
  Exec Procedure_With_Parameter @Parameter

This almost seems like a reasonable approach, until you start considering multiple optional parameters. Consider a procedure with 2 optional parameters. You would need 4 separate procedures. 3 parameters would be 8 procedures to handle all of he combinations. This quickly gets ugly.

Unfortunately, the best approach would be to use dynamic SQL. Done properly, it always returns the correct result and also performs as good as possible. For more reading on this:

http://blogs.lessthandot.com/index....ming/do-you-use-column-param-or-param-is-null

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[donutman]

I've learned something from each of you. Thanks

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[djj55]

George, I see what you are saying. What about?

WHERE (Name LIKE ISNULL(@Name, '%') OR Name IS NULL)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[bborissov]

Then the record 4 will always be in the resultset.

Borislav Borissov
VFP9 SP2, SQL Server

 

[TheBugSlayer]

Set @Name = NULL
SELECT * FROM @Fruit WHERE ISNULL(Name, '') LIKE ISNULL(@Name, '%')

What gives? You are not going to have everything with this one anyway: either the query is not sargable or you have to write some more potentially 'inflatable' code. Hopefully your table is not too big.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)