INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.

CODE

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.

CODE

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

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

I don't think you need this at all:

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

Do you have a SPACE after WHERE?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

CODE

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

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

(OP)
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.

CODE

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

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

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.

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

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

CODE

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

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

(OP)
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

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

CODE

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 

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

(OP)
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

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

(OP)
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 :)

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close