Solved

SQL Server Query with where cluase and wildcard

Posted on 2014-12-03
5
90 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 24

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 24

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 47

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 and will be exposed to the many uses the SELECT statement has.

776 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