Solved

SQL Server Query with where cluase and wildcard

Posted on 2014-12-03
5
97 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
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 65

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 49

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

713 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