Solved

SQL Server Query with where cluase and wildcard

Posted on 2014-12-03
5
101 Views
Last Modified: 2014-12-07
I am trying to build a query for MS SQL server that allows a user to enter an input. Using CASE, if the user do no pass a value, then all records are shown. If they enter a value then only records with that value show up. The problem that I am running into is that when the value is blank, I use the wildcard '%' to retrieve all the records based on the Student ID column, no records are returned.
0
Comment
Question by:Benki Canoso
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 250 total points
ID: 40479746
Is Student ID column of a numeric data type? If it is a CHAR/VARCHAR/NVARCHAR type you can use the LIKE operator
0
 

Author Comment

by:Benki Canoso
ID: 40479751
It is supposed to hold numeric/null/blank data. I tried using the following,
LIKE '%'
but it did not work.
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 125 total points
ID: 40479799
Most developers I know in this situation will handle it this way, using @customer_id as an example, the below T-SQL will return all rows if @customer_id is NULL or an empty string '', and just the row for that @customer_id if it exists.
SELECT yabba, dabba, doo
FROM wherever
WHERE (customer_id = @customer_id OR ISNULL(@customer_id, '') = '')

Open in new window

0
 
LVL 25

Assisted Solution

by:chaau
chaau earned 250 total points
ID: 40479814
I guess in this case your where clause for the StudentID condition should be something like this:

DECLARE @SearchStudent VARCHAR(20)
SET @SearchStudent = '25'

SELECT * from Student
WHERE 1 = (CASE WHEN @SearchStudent = '%' THEN 1
                     WHEN ISNUMERIC(@SearchStudent) = 0 THEN 0
                     ELSE CASE WHEN StudentID = CONVERT(INT, @SearchStudent) THEN 1 ELSE 0 END END)

Open in new window

The idea in the above criteria is to use CASE operator for short-circuiting. If you just used this:
SELECT * from Student
WHERE (@SearchStudent = '%' OR ISNUMERIC(@SearchStudent) = 0 OR StudentID = CONVERT(INT, @SearchStudent))

Open in new window

Then the SQL Server would complain that the '%' would not be converted to INT
SQL Server 2012 has a TRY_CAST and TRY_CONVERT that work without errors. If you have this version of SQL Server your query will look like this:
SELECT * from Student
WHERE (@SearchStudent = '%' OR StudentID = TRY_CONVERT(INT, @SearchStudent))

Open in new window

0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 125 total points
ID: 40480260
If you want to retrieve all records you don't need a filter. Just check if the value is blank then
SELECT * FROM StudentTable

Open in new window

0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question