CODEWITHSUNDEEP
sqlsql-server
Jammer
Jammer

Reputation: 10206

SQL Conditional WHERE Using IN

I'm having a rough time getting this syntax correct and cannot figure out how to correctly write this.

I have a stored procedure with some joins and the where clause is like this:

WHERE
    [Column1] = (SELECT Source FROM @CurrentTransition) AND
    [Column2] = (SELECT Target FROM @CurrentTransition) AND
    [IsDeprecated] = 0 AND
    sbl.StratId is null AND
    std.StratId is null AND
    CASE WHEN s.StratTimeBiasId <> NULL THEN s.StratTimeBiasId IN (SELECT * FROM dbo.fnGetValidTimeBiases(CAST(@datetime AS TIME)))

The error is simply Incorrect syntax near the keyword 'IN'.

The fnGetValidTimeBiases function just returns a list of the Id values from the table that the StratTimeBiasId is the foreign key to.

I only want that particular join to be used when there is actually a value in the StratTimeBiasId column.

Upvotes: 0

Views: 54

Answers (3)

smoore4
smoore4

Reputation: 4866

You probably figured this out already....

WHERE
[Column1] = (SELECT Source FROM @CurrentTransition) AND
[Column2] = (SELECT Target FROM @CurrentTransition) AND
[IsDeprecated] = 0 AND
sbl.StratId is null AND
std.StratId is null AND
s.StratTimeBiasId IN (SELECT * FROM dbo.fnGetValidTimeBiases(CAST(@datetime AS TIME)))

Upvotes: 0

Anthony Grist
Anthony Grist

Reputation: 38345

That's not what a CASE statement is for. Use simple boolean logic instead:

AND (s.StratTimeBiasId IS NULL OR s.StartTimeBiasId IS NOT NULL AND s.StratTimeBiasId IN (SELECT * FROM dbo.fnGetValidTimeBiases(CAST(@datetime AS TIME))))

The above will match anything where s.StratTimeBiasId has a NULL value OR where its value is in the results of that function (I assume the function only returns a single column? If not, that's going to throw an error).

Related note: = and <> aren't the correct operators when dealing with NULL. If you want to test if a column's value is (not) null you need to use column IS (NOT) NULL.

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18767

Try this:

WHERE
    [Column1] = (SELECT Source FROM @CurrentTransition) AND
    [Column2] = (SELECT Target FROM @CurrentTransition) AND
    [IsDeprecated] = 0 AND
    sbl.StratId is null AND
    std.StratId is null AND
    s.StratTimeBiasId IS NULL OR s.StratTimeBiasId IN (dbo.fnGetValidTimeBiases(CAST(@datetime AS TIME)))

Upvotes: 0

Related Questions