CODEWITHSUNDEEP
sqlstored-proceduresnull
Apemantus
Apemantus

Reputation: 683

Using parameters in stored procedures where the column may be null

I want to (loosely) have a stored procedure like

select * from table where col1 like @var

the default value of @var is '%', so if I don't have a value for @var it should return every row. However, the problem is it doesn't return rows where col1 is null.

How do I return all rows if @var = '%' and rows that are like @var if it does have a value?

[the actual sp is significantly more complex so I don't want to just wrap it in a if..then and have two selects]

Upvotes: 2

Views: 303

Answers (3)

CResults
CResults

Reputation: 5105

Just a heads up more than anything. If this SP is to be used frequently and the data you are selecting from is large make sure you performance test your query (using either of the solutions above). From past experience using LIKE and ISNULL or IS NULL together can have a big performance hit

Upvotes: 1

Adam Hughes
Adam Hughes

Reputation: 3812

You could special case when @var is '%' to include all the null values like this:

select * from table where col like @var or (@var = '%' and col is null)

Upvotes: 0

roufamatic
roufamatic

Reputation: 18495

select * from table where isnull(col1, '') like @var

Should do the trick.

Upvotes: 3

Related Questions