CASE statement in a WHERE clause

Question

I need to parse a sproc parameter with three conditions...

1.  If parameter is empty return all records

2.  If parameter contains a single value, return only that record

3,  If parameter contains a comma separated list, return only the records in that list.

I've got it working for conditions 1 & 2, but when I add the third condition it errors out.

Logically it's something similar to:

LEN(@PermitNumber) CASE
WHEN 0 THEN P.PermitNumber Like '%'  --select all
WHEN 7 THEN P.PermitNumber = @PermitNumber  -- select one
ELSE P.PermitNumber IN (SELECT *  --select from list
                           FROM   dbo.Strtotable(@PermitNumber))

Any ideas?

Answer 1

http://www.sommarskog.se/arrays-in-sql.html

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 2

I suggest to use 2 cases

1. Parameter is empty

2. List of values (one value is also a list).

So,

IF NULLIF(@param,'') IS NULL

    select * from myTable

ELSE -- list of values

    select * from myTable where PermitNumber IN (select Item from dbo.fnSplit (@ListOfPermitNumbers,','))

-------------------------------------------

Where fnSplit function can be any of the split functions available, for example, check

http://www.sqlservercentral.com/articles/Tally+Table/72993/

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

I have a function that takes a comma separated list and outputs a table that can be consumed by an IN clause.   Both of these are helpful.  But I need the logic to work in a where clause.  I'm hoping not to have to have to repeat the entire select statement twice.  A simplified example...

SELECT P.PermitNumber, P.ProjectName

FROM Permit P

WHERE

P.PermitNumber LIKE CASE 
--single permit
WHEN LEN(RTRIM(@PermitNumber)) > 0 THEN @PermitNumber
--more than one permit
--WHEN (SELECT *
     --                         FROM   dbo.Strtotable(@Permit))
                   --all permits 
                    ELSE '%'
   END

 when I uncomment the line which breaks the comma separated list into a table it errors out

Answer 4

Why not just change your function to output a table with the Permit numbers and just join to it rather than caring wether there are 1 or more than one?

---

Chuck

Answer 5

Something like this:

SELECT P.PermitNumber, P.ProjectName
 
FROM Permit P
JOIN dbo.Strtotable(@PermitNumber) plf on p.permitnumber = plf.permitnumber or @PermitNumber = ''

---

Chuck

Answer 6

You can also use dynamic SQL here, e.g.

declare @SQL nvarchar(max)

create table #PermitNumbers (PermitNumber int primary key)

set @SQL = 'Here goes that static part of the complex query including parameters, if needed'

if NULLIF(@PermitNumber, '') IS NOT NULL

     BEGIN

      insert into #PermitNumbers select item from dbo.StrToTable (@PermitNumber)

     set @SQL = @SQL + ' AND P.PermitNumber IN (select PermitNumber from #PermitNumbers)'

   END

EXECUTE sp_ExecuteSQL @SQL, N'@ParamListIfNeeded', @Parameters

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

Here - this works:  Just mess around with the values in @PermitNumber to test

CREATE FUNCTION [dbo].[Strtotable]
 (
   @delimited nvarchar(max)
 ) RETURNS @t TABLE
 (
   permitnumber nvarchar(max)
 )
 AS
 BEGIN
   declare @xml xml
   set @xml = N'<root><r>' + replace(@delimited,',','</r><r>') + '</r></root>'
 
  insert into @t(permitnumber)
   select 
    r.value('.','varchar(50)') as item
   from @xml.nodes('//root/r') as records(r)
 
  RETURN
 END

CREATE TABLE #Permit(Permitnumber int, Projectname varchar(50))
INSERT #Permit Values(122,'Test1')
INSERT #Permit Values(1234,'Test2')
INSERT #Permit Values(123456,'Test3')

DECLARE @PermitNumber varchar(max)
SET @PermitNumber = ''

SELECT P.PermitNumber, P.ProjectName
 
FROM #Permit P
JOIN dbo.Strtotable(@PermitNumber) plf on p.permitnumber = plf.permitnumber or @PermitNumber = ''

---

Chuck

Answer 8

I don't think it's going to be a good performing solution. I recommend to compare 2 versions - the one I suggested with this version on big tables.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

I don't think it's going to be a good performing solution. I recommend to compare 2 versions - the one I suggested with this version on big tables.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Why would it not perform well?   I'm assuming permit would be indexed on PermitNumber - also assume that they are not sending in a string with 20,000 comma delimited values.  There is no reason the join would not be fast.   In general I've got a absolutely no dynamic sql rule in stored procedures.

---

Chuck

Answer 10

When we stat using OR conditions in the JOIN, it's not going to be really fast and also may end up generating wrong plan depending on the initial call. At least we may want to add OPTION (Recompile) to the query above.

See these links

Do you use ISNULL(...). Don't, it does not perform - short blog by Denis Gobo
Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later) - long and comprehensive article by Erland Sommarskog 
Catch All Queries - short blog by Gail Shaw

Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch

Option recompile Option recompile discussion thread

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

But the OR is joining against either a non indexed small table with few rows, no rows or an empty variable.  Don't see any sort of performance issue in this case.   The dynamic search condition problem I agree with - but that is an entirely different case where the table you are OR joining to actually contains rows.

---

Chuck

Answer 12

I think it's the same case here - we either JOIN or not join at all. I can not run some performance tests now, but if you have a moment, it will be a good test to try.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog