How to code choosing wildcard or variable in WHERE clause?

[AlienHost]

Hello,

I'm trying to set up a where clause with (among other things) a selection of a variable if it has a value other than "all" or a wildcard if it is "all." I'm pulling the data from a temp table.

I'm not sure how to code this and have botched it in several different ways. Can you help me? (beginner here). Here are the error messages and code below that:

Server: Msg 156, Level 15, State 1, Procedure usp_ProgressNotes, Line 186
Incorrect syntax near the keyword 'if'.
Server: Msg 156, Level 15, State 1, Procedure usp_ProgressNotes, Line 186
Incorrect syntax near the keyword 'THEN'.
Server: Msg 156, Level 15, State 1, Procedure usp_ProgressNotes, Line 188
Incorrect syntax near the keyword 'then'.

Here's what I have now that isn't working:

Select top 10* from #TmpProgressNotes WHERE 

if @ServiceCodeGroup LIKE 'All Service Codes' THEN 
			ServiceCodeGrpDesc LIKE '%'
else if not  ISNULL(@ServiceCodeGroup,'NULL') then 
		ServiceCodeGrpDesc=@ServiceCodeGroup
end

 

[gmmastros]

Try this...

Select top 10* from #TmpProgressNotes WHERE 

(@ServiceCodeGroup = 'All Service Codes' or @ServiceCodeGroup Is NULL or ServiceCodeGroup = @ServiceCodeGroup)

Read this as...

If @ServiceCodeGroup is 'All Service Codes' or null, then return all the rows, otherwise, only return rows where ServiceCodeGroup matches @ServiceCodeGroup.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Select top 10 *
       from #TmpProgressNotes
WHERE (@ServiceCodeGroup   = 'All Service Codes' OR
        ServiceCodeGrpDesc = @ServiceCodeGroup)

I don't understand this:

if not  ISNULL(@ServiceCodeGroup,'NULL')

What you want if @ServiceCodeGroup is NULL?
No records selected?


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[hwkranger]

Here's an easy trick that I use alot when i need to pass a variable where clause:

ALTER PROCEDURE [dbo].[uspElectricCode_Getall]
	@UserID int = null,
	@LanguageID int = null,
	@RegionID int = null,
	@ContractorID int = null,
	@ElectricCodeID int = null
AS
SELECT [EC_ElectricCodeID] ElectricCodeID
      ,[EC_Name] Name
      ,[EC_Description] Description
      ,[EC_RegionID] RegionID
     ,[EC_ContractorID] ContractorID
  FROM [dbo].[Electric_Code]
WHERE
  EC_IsDeleted = 'False'
	AND
(
	CASE WHEN @RegionID is null then 1 else @RegionID end
		=
	CASE WHEN @RegionID is null then 1 else [EC_RegionID] end
)
	AND
(
	CASE WHEN @ContractorID is null then 1 else @ContractorID end
		=
	CASE WHEN @ContractorID is null then 1 else [EC_ContractorID] end
)
	AND
(
	CASE WHEN @ElectricCodeID is null then 1 else @ElectricCodeID end
		=
	CASE WHEN @ElectricCodeID is null then 1 else [EC_ElectricCodeID] end
)

When nothing is supplied, it returns 1=1 in those case statements. Otherwise, you get dilimiters on the values of the variables passed.

 

[Qik3Coder]

@hwkRanger, i had your same issue, but came to a shorted answer, cause I'm lazy...

WHERE
  EC_IsDeleted = 'False'
    AND
    [EC_RegionID] = CASE WHEN [b]Colese(@RegionID,'') = ''[/b] then [b][EC_RegionID][/b] else @RegionID end
    AND
(...

This way i can handle Nulls, Blanks, wildcards and actual joins.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[gmmastros]

Qik3Coder,

The code you posted is not functionally equivalent to the code hwkranger posted. Under certain circumstances, it may appear as though it's the same, but please don't be convinced it is the same.

Below is some sample code to illustrate my point.

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('Apple')
Insert Into @Temp Values('Orange')
Insert Into @Temp Values('')
Insert Into @Temp Values(NULL)

Declare @Test VarChar(20)

Select * 
From   @Temp
Where  Data = Case When Coalesce(@Test, '') = '' Then Data Else @Test End

Notice in the code above, I am not setting a value for @Test, so it is NULL. When you run the code, you may expect there to be 4 rows returned. In fact, only 3 rows are returned because 1 row has a null value.

hwkRanger's code does return 4 rows, but so does the code presented by Boris (and myself).

Ex:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('Apple')
Insert Into @Temp Values('Orange')
Insert Into @Temp Values('')
Insert Into @Temp Values(NULL)

Declare @Test VarChar(20)

[green]-- Play with the value of @Test to see how this works[/green]
Set @Test = NULL

Select * 
From   @Temp
Where  (@Test is NULL or Data = @Test)

In my opinion, this code is a lot simpler to read and understand, making it slightly better than the alternatives.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

Gmmastros, you are correct.

That example was from left join that autmatically removed the null rows that i didn't want. I didn't check it.
I have one similiar that I add a isNull/Coalesce onto the table column as well, to prevent this.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]VarChar[/color](20))
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'Apple'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'Orange'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]''[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](NULL)
[COLOR=blue]Declare[/color] @Test [COLOR=blue]VarChar[/color](20)
[COLOR=green]--hwkRanger's Example
[/color][COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]  @Temp
[COLOR=blue]Where[/color] [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] @Test [COLOR=blue]is[/color] null [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] @Test [COLOR=blue]end[/color]
      = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] @Test [COLOR=blue]is[/color] null [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] Data [COLOR=blue]end[/color]
[COLOR=green]--My Initial example, with null row "cleaning"
[/color][COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  Data = [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]Coalesce[/color](@Test, [COLOR=red]''[/color]) = [COLOR=red]''[/color] [COLOR=blue]Then[/color] Data [COLOR=blue]Else[/color] @Test [COLOR=blue]End[/color]
[COLOR=green]--My Revised, with null rows
[/color][COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  [COLOR=#FF00FF]Coalesce[/color](Data,[COLOR=red]''[/color]) = [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]Coalesce[/color](@Test, [COLOR=red]''[/color]) = [COLOR=red]''[/color] 
	[COLOR=blue]Then[/color] [COLOR=#FF00FF]Coalesce[/color](Data,[COLOR=red]''[/color]) [COLOR=blue]Else[/color] @Test [COLOR=blue]End[/color]
[COLOR=green]--Alternate with all 4 rows
[/color][COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Where[/color]  [COLOR=#FF00FF]Coalesce[/color](Data,[COLOR=red]''[/color]) =[COLOR=#FF00FF]Coalesce[/color](@Test,[COLOR=#FF00FF]Coalesce[/color](Data,[COLOR=red]''[/color]))

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[AlienHost]

Thanks to all of you! I got some great ideas, but used GMastros code first and it did the trick perfectly. I had to think about why the first part [(@ServiceCodeGroup = 'All Service Codes']
would return all records, but then I translated this to the equivalent of "where 0=0" which would be true when the rest was false. Cool!