CODEWITHSUNDEEP
sql
Damian
Damian

Reputation: 1553

Don't check field if parameter is an empty string

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.

Upvotes: 1

Views: 310

Answers (2)

Quassnoi
Quassnoi

Reputation: 425623

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).

Upvotes: 0

Pete OHanlon
Pete OHanlon

Reputation: 9146

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.

Upvotes: 1

Related Questions