SQL Using CASE to Apply WHERE-CodePudding

I'm not sure if CASE is what I actually need to use here, but basically what I am trying to do is apply "WHERE ba.batchid = @batchId" but only if @batchId is not null.

Basically something like this, but this gives an error around the ba.batchid = '2' area ('2' is what the @batchId would come out to).

CASE WHEN '2' != '' THEN ba.batchid = '2' ELSE null END

CodePudding user response：

It helps to think of Case statements as a scalar value. The problem you are experiencing is caused by doing a comparison within the case statement. Basically, you need to pull the comparison out of the case statement.

I'm not sure of the logic you are trying to use, but the following syntax should be correct.

CASE WHEN '2' != '' THEN ba.batchid ELSE null END = '2'

Note that the = '2' is moved to the end. This way, the case statement will return batchid or null depending on the WHEN statement. This value is then compared to '2'.

CodePudding user response：

Try this:

WHERE NULLIF(@batchId, '') IS NOT NULL AND ba.batchid = @batchid

First condition avoids empty('') as well as NULL with @batchid. Second will match the table value.

CodePudding user response：

Take it out of the SELECT part of your statement entirely and put it in the WHERE clause (after FROM)

WHERE ba.batchid = @batchId
  AND ba.batchid IS NOT NULL