SELECT WHERE CASE WHEN @VARIABLE 2

[tqeonline]

I have a page i'm creating and I have 4 variables.
1. Assignee
2. Priority
3. State
4. Type

*state is the state of the process, not physical land state

Here is what I have

SELECT * FROM [SQA_DASH_AUTOMATION_PIPELINE] 
WHERE 
   (CASE WHEN @ASSIGNEE = '' THEN ([ASSIGNEE] LIKE '%' or [ASSIGNEE] IS NULL) 
         ELSE [ASSIGNEE] LIKE '%' + @ASSIGNEE + '%' END) 
AND ([PRIORITY] LIKE @PRIORITY) 
AND ([STATE] LIKE @STATE) 
AND ([TYPE] LIKE @TYPE)

My error comes with Assignee. The page starts with the assignee blank and this query doesn't bring back anything. When I start to type nothing changes.

My logic is this for Assignee (the other filters work):

Case when Assignee is null or Assignee = '' then
    Bring back any Assignee
Else
    Where Assignee like '%' + @assignee + '%'
End

Help?!

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[Qik3Coder]

SELECT * 
FROM [SQA_DASH_AUTOMATION_PIPELINE] 
WHERE    
   [b]([ASSIGNEE] LIKE '%'+ Coalesce(NullIf(@ASSIGNEE, ''), ASSIGNEE) + '%')[/b]
   AND ([PRIORITY] LIKE @PRIORITY) 
   AND ([STATE] LIKE @STATE) 
   AND ([TYPE] LIKE @TYPE)

HTH,
Lodlaiden

I'll answer your question, not solve your problem

 

[tqeonline]

i tried that and it still doesn't return anything... this is what I have it as:

declare @assignee nvarchar(50)
declare @priority nvarchar(50)
declare @state nvarchar(50)
declare @type nvarchar(50)

set @assignee = '%'
set @priority = '%'
set @state = '%'
set @type = '%'

SELECT * FROM [SQA_DASH_AUTOMATION_PIPELINE] 
WHERE ([ASSIGNEE] LIKE '%' + Coalesce(NullIf(@ASSIGNEE, ''), ASSIGNEE) + '%') 
AND ([PRIORITY] LIKE @PRIORITY) 
AND ([STATE] LIKE @STATE) 
AND ([TYPE] LIKE @TYPE)

I also tried with

set @assignee = ''

still with no results

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

also note that in the database right now the assignee = "David" in one row and is null in the next.

There are only two rows of data. If I remove the Assignee constraint/variable it brings the rows back.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[SimonSellick]

I think your logic explanation is incorrect. Do you mean:

IF @Assignee is null or blank 
THEN
  return rows irrespective of value of assignee (no filter)
ELSE
  return only those rows where assignee contains @Assignee
END

? If so, try:

...
WHERE nvl(@Assignee, '') = '' OR CHARPOS(assignee, @Assignee) > 0
...

(I think that is the right way around for CharPos)
HTH
Simon

 

[SimonSellick]

Sorry! No such thing as CHARPOS in SQL Server. Try CHARINDEX.

 

[tqeonline]

haha. You are right, there also is no such thing as NVL???

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[Qik3Coder]

tgeonline,

I was expecting you to copy and past the assignee line for the rest of your parameters.
I have put together a full sample for you.

Declare @_table as Table(
id int Identity(1,1),
Assignee Varchar(255),
Priority Varchar(255),
[State] Varchar(255),
[Type] Varchar(255))

INSERT INTO @_Table
SELECT 'Joe', 'HIGH', 'NEW', 'TEK-TIPS POST' UNION ALL
SELECT 'Bob', 'MED', 'IN FLIGHT', 'HD TICKET' UNION ALL
SELECT 'SUE', 'HIGH', 'NEW', 'TEK-TIPS POST' UNION ALL
SELECT '', 'HIGH', 'NEW', 'HD TICKET' UNION ALL
SELECT 'Joe', 'LOW', 'CLOSED', 'TEK-TIPS POST' UNION ALL
SELECT 'Sue', 'MED', 'NEW', 'TEK-TIPS POST'

SELECT * FROM @_TABLE

DECLARE @_Assignee varchar(255), @_Priority varchar(255), @_State varchar(255), @_Type varchar(255)
SELECT @_Assignee = 'Joe', @_Priority='HIGH', @_State='NEW', @_Type='TEK-TIPS POST'
[green]
--Code from yesterday
--This piece works as long as the other parameters are filled in, and have a match[/green]
SELECT * FROM @_Table t
WHERE
	([ASSIGNEE] LIKE '%'+ Coalesce(NullIf(@_ASSIGNEE, ''), ASSIGNEE) + '%')   
	AND ([PRIORITY] LIKE @_PRIORITY)    AND ([STATE] LIKE @_STATE)    AND ([TYPE] LIKE @_TYPE)

SELECT @_Assignee = '', @_Priority='HIGH', @_State='NEW', @_Type=''

[green]--Full "optional" parameter query[/green]
SELECT * FROM @_Table t
WHERE
	([ASSIGNEE] LIKE '%'+ Coalesce(NullIf(@_ASSIGNEE, ''), [ASSIGNEE]) + '%')   
	AND ([PRIORITY] LIKE '%'+ Coalesce(NullIf(@_PRIORITY, ''), [PRIORITY]) + '%')   
	AND ([STATE] LIKE '%'+ Coalesce(NullIf(@_STATE, ''), [STATE]) + '%')   
	AND ([TYPE] LIKE '%'+ Coalesce(NullIf(@_TYPE, ''), [TYPE]) + '%')

HTH,
Lodlaiden

I'll answer your question, not solve your problem

 

[tqeonline]

Qik3Coder,

I had already come up with this

  IF (@ASSIGNEE IS NULL OR  @ASSIGNEE='')
	  Begin
		   SELECT * FROM [SQA_DASH_AUTOMATION_PIPELINE]
		   WHERE ([ASSIGNEE] IS NULL OR [ASSIGNEE] LIKE '%')
		   AND ([PRIORITY] LIKE @PRIORITY)
		   AND ([STATE] LIKE @STATE)
		   AND ([TYPE] LIKE @TYPE)
	  End
  Else
	  Begin
		SELECT * FROM [SQA_DASH_AUTOMATION_PIPELINE]
		WHERE [ASSIGNEE] LIKE '%' + @ASSIGNEE + '%'
		AND ([PRIORITY] LIKE @PRIORITY)
		AND ([STATE] LIKE @STATE)
		AND ([TYPE] LIKE @TYPE)
	  End

When you posted. I am looking into your method now as i'm not 100% sure about the way you went so i'm going to research it.

Thanks!

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

I think you're going want something like this:

SELECT * 
FROM   [SQA_DASH_AUTOMATION_PIPELINE] 
WHERE  (NullIf(@ASSIGNEE, '') Is Null Or [ASSIGNEE] LIKE @ASSIGNEE)
       AND (NullIf(@Priority, '') Is NULL Or [PRIORITY] LIKE @PRIORITY) 
       AND (NullIf(@STATE, '') Is NULL Or [STATE] LIKE @STATE) 
       AND (NullIf(@TYPE, '') Is NULL Or [TYPE] LIKE @TYPE)

With this code, you would use NULL or empty string ('') when you want the filter ignored. If you pass in a % or any other value, you will not get the NULLS.

If you want to ignore the filter condition when you pass in %, then you can use this code.

Declare @Test Table(ASSIGNEE VarChar(10), PRIORITY VarChar(10), STATE VarChar(10), TYPE VarChar(10))

Insert Into @Test Values(NULL, NULL, NULL, NULL)
Insert Into @Test Values('Bill',NULL, NULL, NULL)
Insert into @Test Values(NULL,'High',NULL, NULL)
Insert Into @Test Values(NULL, NULL, 'OPEN', NULL)
Insert Into @Test Values(NULL, NULL, NULL, 'Purple')

declare @assignee nvarchar(50)
declare @priority nvarchar(50)
declare @state nvarchar(50)
declare @type nvarchar(50)

set @assignee = '%'
set @priority = ''
set @state = '%'
set @type = ''

SELECT * 
FROM   [SQA_DASH_AUTOMATION_PIPELINE]
WHERE  (NullIf(NullIf(@ASSIGNEE, '%'), '') Is Null Or [ASSIGNEE] LIKE @ASSIGNEE)
       AND (NullIf(NullIf(@Priority, '%'), '') Is NULL Or [PRIORITY] LIKE @PRIORITY) 
       AND (NullIf(NullIf(@STATE, '%'), '') Is NULL Or [STATE] LIKE @STATE) 
       AND (NullIf(NullIf(@TYPE, '%'), '') Is NULL Or [TYPE] LIKE @TYPE)

I tested it like this:

Declare @Test Table(ASSIGNEE VarChar(10), PRIORITY VarChar(10), STATE VarChar(10), TYPE VarChar(10))

Insert Into @Test Values(NULL, NULL, NULL, NULL)
Insert Into @Test Values('Bill',NULL, NULL, NULL)
Insert into @Test Values(NULL,'High',NULL, NULL)
Insert Into @Test Values(NULL, NULL, 'OPEN', NULL)
Insert Into @Test Values(NULL, NULL, NULL, 'Purple')

declare @assignee nvarchar(50)
declare @priority nvarchar(50)
declare @state nvarchar(50)
declare @type nvarchar(50)

set @assignee = '%'
set @priority = ''
set @state = '%'
set @type = ''

SELECT * 
FROM   @Test
WHERE  (NullIf(NullIf(@ASSIGNEE, '%'), '') Is Null Or [ASSIGNEE] LIKE @ASSIGNEE)
       AND (NullIf(NullIf(@Priority, '%'), '') Is NULL Or [PRIORITY] LIKE @PRIORITY) 
       AND (NullIf(NullIf(@STATE, '%'), '') Is NULL Or [STATE] LIKE @STATE) 
       AND (NullIf(NullIf(@TYPE, '%'), '') Is NULL Or [TYPE] LIKE @TYPE)

Note that I mention this simply because that was the direction you were heading with your original query. From a performance perspective, this is probably not the best approach because the query will be unable to use any indexes. The point is, if you end up with a lot of data in this table, this approach may be a bit slow for you.

If this type of search query is important (frequently used) and your performance is suffering, then you should probably investigate using "dynamic sql" which allows you to build your where clause in pieces.

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

 

[SimonSellick]

Matt,

Sorry - I was having a bit of a brain blitz yesterday. NVL() is Oracle for IsNull().

Simon.

 

[markros]

The code you come up with is fine. It may be better than one query.

Check the following 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


PluralSight Learning Library