SQL Server Query with where cluase and wildcard

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.
Benki CanosoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Is Student ID column of a numeric data type? If it is a CHAR/VARCHAR/NVARCHAR type you can use the LIKE operator
0
Benki CanosoAuthor Commented:
It is supposed to hold numeric/null/blank data. I tried using the following,
LIKE '%'
but it did not work.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
chaauCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.