Don't check field if parameter is an empty string

Question

I want to do this

(s.Reference = @Reference or @Reference = '')

but I get an sql error saying unable to convert nvarchar to bigint. What's going on there. I just want to skip that query on the Refernce if the input parameter is an empty string.

Answer 1

It looks as though Reference is a numeric field. In this case, you are probably looking to do:

(s.Reference = @Reference or @Reference is null)

When you put ='' into the test condition, you make an assumption that it's a string.

Answer 2

For SQL Server, the best decision would be this:

SELECT  *
FROM    mytable
WHERE   s.Reference = @Reference
UNION ALL
SELECT  *
FROM    mytable
WHERE   @Reference IS NULL

It will optimize one of the queries away and execute only the remaining one (using the index if needed).