Help with SQL using CASE in Where statement looking for specific text 1

[DougP]

I have a WEB page which I want a user to select a "Group" from a drop down box. If the user selects "Show all" I want it to show all items. If they select something else I want it to filter for that in the Where clause.
here is what I have so far. The reason I am not doing this in code is I'm using ASP.NET and the Datagrid is bound to a SQL Datasource.

SELECT Name, Description, [Group], nearby, Latitude, Longitude, 
                                DateAdded, UniqueID, CastType, Price, LocationGroup FROM RenFest 
WHERE 
CASE  When [Group] ='Show All' Then
	[Name] = -- DON'T KNOW WHAT TO PUT HERE?
	CASE when [Group] = '%'  Then -- ??? HERE IS IF THEY SELECT ANYTHING FROM THE DROP DOWN BOX AND GROUP HAS A VALUE
	 Name LIKE '%' + @name +'%' OR [Group] LIKE '%' + @Group + '%'
	END
        Order by [name]

this is the code that drives the drop down, if anyone is interested.

Select 1 as UniqueID, 'Show All' as [Group] from renfest 
Union 
Select  2 as UniqueID, [Group] from renfest
Group by UniqueID, [Group]
order by UniqueID

Or any other suggestions welcome

TIA

DougP

 

[Andrzejek]

I don't think you need this at all:

[pre]
CASE When [Group] ='Show All' Then
[Name] = -- DON'T KNOW WHAT TO PUT HERE?
[/pre]
WHERE is for limiting/filtering records returned, so if you want to 'Show All' records, you don't need any filter

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

[case]
WHERE
CASE When [Group] ='Show All' Then
1 = 1
[/case]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DougP]

Skip, I tried 1=1 and I get syntax error "incorrect syntax near ="

Also Andrzejek, as I mentioned this is being driven by a drop down which has "Show All" as an option. Perhaps this won't even work if I get the syntax right anyway?
And also as mentioned this is a bound Datagrid Configure Data Source, so I am trying to put something in there that will work without having to unbind it and write everything the Connection gives from scratch.

DougP

 

[SkipVought]

Do you have a SPACE after WHERE?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[DaveInIowa]

I'm going to assume @Group is a parameter value that can be 'Show All' and you didn't mean [Group], the column value.

 WHERE CASE
		  WHEN @Group = 'Show All' THEN 1
		  WHEN Group LIKE '%' + @Group + '%' THEN 1
		  WHEN Name LIKE '%' + @Name + '%' THEN 1
		  ELSE 0
	   END = 1

 

[DougP]

Skip, yes there is a space after where.
DaveInIowa, I get all records no matter what criteria I put in below.
@Group is a parameter passed from the dropdown to the SQL datasource and [Group] is the name of a column. This is an ASP.NET web page using a Gridview to hold results, a textbox called Name, and a dropdown which has a unique list of groups which I added "Show all" to as shown in the original post.

Declare @Group as nvarchar(10)
Declare @Name as nvarchar(10)

Set @Group = ''
Set @Name = 'wolf'
OR 
Set @Group = 'wolf'
Set @Name = ''
OR
Set @Group = 'Show All'
Set @Name = ''
SELECT Name, Description, [Group], nearby, Latitude, Longitude, 
                                DateAdded, UniqueID, CastType, Price, LocationGroup FROM RenFest 
                                WHERE CASE
								  WHEN @Group = 'Show All' THEN 1
								  WHEN [Group] LIKE '%' + @Group + '%' THEN 1
								  --WHEN Name LIKE '%' + @Name + '%' THEN 1
								  ELSE 0
							   END = 1

It does work, but seems it's not using any criteria it just returns everything.
So it needs to return 3 different options, if possible.
Show all ignores anything else and shows all records, or I could choose a group and show those records or I could choose a name and show those names that match.
Oh, I remmed out the [Name] parameter to see it that did any different , but no.

DougP

 

[DaveInIowa]

Setting @Group or @Name to an empty string will always return all records because it evaluates to "WHEN Group LIKE '%%' THEN 1". Instead of using an empty string (''), set the parameter values to NULL.

 

[gmmastros]

You (probably) don't need a case statement. The following where clause will probably work.

WHERE @Group = 'ShowAll'
      Or Name LIKE '%' + @name +'%' 
      OR [Group] LIKE '%' + @Group + '%'

-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

 

[DougP]

It does work though so thank you so much !!!

Ok then that brings up another issue. If a user leaves the text box blank then how do I pass a NULL to the @name parameter? And now that you mention it; the @Group will never be empty or NULL; it will be either "Show All" or contain some value since its a drop down box. (So my bad on testing that in SSMS).
Is can we modify the SQL to make @Name NULL if it has ''. Unless I can do some tricky on the .NET side to make it NULL if it contains ''?


DougP

 

[DaveInIowa]

IF (@Name = '') SET @Name = NULL

-- OR (I'm not sure the following will work; I was getting a red squiggly in SSMS)

WHERE CASE
         WHEN @Group = 'Show All' THEN 1
         WHEN Group LIKE '%' + @Group + '%' THEN 1
         WHEN Name LIKE '%' + NULLIF(@Name, '') + '%' THEN 1
         ELSE 0
      END = 1

 

[DougP]

I found an option which is to make a Stored Procedure and check the value of @Name in there and make it NULL.
I don't know how to make it NULL in VB.NET since there is no .NET code driving anything its all using Connected items. There is a default value but putting the word NULL in there it thinks it's trying to find "NULL" as a valid search and returns no records.

DougP

 

[DougP]

Ok here is my final solution I made a stored Procedure and checking @name for NULL, which works great, It returns just groups or it also returns groups and one name as well, or jus tone name and now group so Hoorah. or
Thanks all. Consider this one solved

Create Procedure sp_RenFest_SearchNameGroup
	@Group nvarchar(50),
	@Name nvarchar(50)
AS
IF @Name = '' 
	BEGIN
		SET @Name = NULL
	END

SELECT Name, Description, [Group], nearby, Latitude, Longitude, 
                                DateAdded, UniqueID, CastType, Price, LocationGroup FROM RenFest 
                                WHERE CASE
								  WHEN @Group = 'Show All' THEN 1
								  WHEN [Group] LIKE '%' + @Group + '%' THEN 1
								  WHEN Name LIKE '%' + @Name + '%' THEN 1
								  ELSE 0
							   END = 1

DougP