CODEWITHSUNDEEP
sqlsql-server-2005
Bob1254
Bob1254

Reputation: 75

Return All Rows if Field is null or blank

How do I make this return all results if @title is null or if its blank ''?

@title varchar

AS

SELECT *
FROM mydb
Where (Title = @title)

Ive been trying to mess around with different ways but I always get no rows returns. I just need to return all the rows, if nothing is inputed into the parameter.

Upvotes: 1

Views: 2935

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332571

Use:

IF LEN(@title) > 0 THEN
BEGIN 

  SELECT * FROM mydb t
   WHERE t.title = @title

END
ELSE
BEGIN 

  SELECT * FROM mydb 

END

You can use:

SELECT *
  FROM mydb
 WHERE (LEN(@title) = 0 OR t.title = @title)

...to only use the @title variable if it's not zero length or NULL, but the statement is not sargable -- it won't perform as well as breaking out the statement to be only what it needs to be for the situation.

Upvotes: 1

JNK
JNK

Reputation: 65157

Add:

...
OR @Title IS NULL
OR @Title = ''

The OR makes it inclusive, and there's no way that you will have @Title meet more than one of those three criteria (unless your Title field has NULL or blank values).

Upvotes: 4

Related Questions