IF ELSE Statement in SQL

Question

SELECT S.Id, S.Name, S.Version, S.SoftNo
FROM SOFTWARE S WITH(NOLOCK)
WHERE (IF S.Version = 0 THEN S.Version > 0 ELSE S.Version = @Version)
AND (IF S.SoftNo = 0 THEN S.SoftNo > 0 ELSE S.SoftNo = @SoftNo)

If Version is equal to zero, I want to list everything version number is greater then 0 if it is not equal to 0, then Version should be whatever it is value.

This is same for SoftNo.

How can I fix my SQL query. It just doesn't work.

More details:

This is what I want to achieve:

if(Version == 0)
{
     display every single rows if their version number is greater then 0;
}
else
{
     Lets assume that Version is equal to 5. Then just display the rows if their Version number is equal to 5;
} // This is like a C# code of what I am trying to do in my sql query.

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'IF'. Incorrect syntax near the keyword 'THEN'. Incorrect syntax near the keyword 'THEN'. — Cute Bear, Mar 26 '12 at 09:54
http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select — assylias, Mar 26 '12 at 09:57

score 1 · Answer 1 · answered Jun 27 '15 at 23:27

1

Have you thought of using SQL cases?

SELECT col1, col2,
    CASE
        WHEN expression THEN return 
        WHEN expression THEN return 
        ELSE return 
    END AS NameOfNewColWithReturnValues
FROM Col_FROM_WHICH_TABLE

answered Jun 27 '15 at 23:27

abhimanyuaryan

3,882
5
41
83

tobias86 · Answer 2 · 2012-03-26T10:04:10.173

1

Try something like this:

SELECT S.Id, S.Name, S.Version, S.SoftNo
FROM SOFTWARE S WITH(NOLOCK)
WHERE ((@Version = 0 AND S.Version > 0) OR 
       (@Version <> 0 AND S.Version = @Version)) AND 
      ((@SoftNo = 0 AND S.SoftNo > 0) OR 
       (@SoftNo <> 0 AND S.SoftNo = @SoftNo))

edited Mar 26 '12 at 10:04

answered Mar 26 '12 at 09:59

tobias86

4,979
1
21
30

How can something be 0 and greater than 0 at the same time? (S.Version = 0 AND S.Version > 0) – Paul Grimshaw Mar 26 '12 at 10:00
@PKG: This is purely based on the OP's question. Thanks for picking that up. – tobias86 Mar 26 '12 at 10:02
Thank you for your answer guys. However, I might be explained in a wrong way since my sql quite sucks but i updated the question. if you guys find a chance to look at it again, i'll really appreciate that. – Cute Bear Mar 26 '12 at 11:53

score 0 · Answer 3 · answered Jul 18 '13 at 00:19

Why not do

SELECT S.Id, S.Name, S.Version, S.SoftNo
FROM SOFTWARE S WITH(NOLOCK)
WHERE 
(
    (@Version = 0 OR (@Version <> 0 AND S.Version = @Version))
    AND
    (@SoftNo = 0 OR (@SoftNo <> 0 AND S.SoftNo = @SoftNo))
)

(Do you really need the NOLOCK?)

score 0 · Answer 4 · answered Dec 11 '13 at 23:52

Don't use concatenated SQL, it is a poor habit that increases the probability of SQL injection vulnerabilities. Your SQL code is now the exact same as the following (safer) code:

SELECT 
    S.Id, S.Name, S.Version, S.SoftNo
FROM
    SOFTWARE S WITH(NOLOCK)
WHERE
    (@Version = 0 OR @Version = S.Version)
    AND (@SoftNo = 0 OR @SoftNo = S.SoftNo)

score 0 · Answer 5 · answered Mar 26 '12 at 09:56

0

You have to use CASE WHEN statement instead of if

answered Mar 26 '12 at 09:56

Habib

219,104
29
407
436

You can't use CASE WHEN to determine which comparison to perform. It emits values only. – tobias86 Mar 26 '12 at 10:05

Paul Grimshaw · Answer 6 · 2012-03-26T10:07:38.640

0

Don't quite understand, but if you mean the input variables equal zero, you could do something like:

SELECT S.Id, S.Name, S.Version, S.SoftNo
FROM SOFTWARE S WITH(NOLOCK)
WHERE ((@Version = 0 AND S.Version > 0) OR (S.Version = @Version AND @Version > 0))
AND (@SoftNo = 0 AND S.SoftNo > 0) OR (@SoftNo = S.SoftNo AND @SoftNo > 0 )

edited Mar 26 '12 at 10:07

answered Mar 26 '12 at 09:58

Paul Grimshaw

19,894
6
40
59

score 0 · Accepted Answer · answered Mar 30 '12 at 10:11

Instead, I came up with this workaround,

sSQL = @"SELECT S.Id, S.Name, S.Version, S.SoftNo
         FROM SOFTWARE S WITH(NOLOCK)
         WHERE 1 = 1";
// 1=1 is used just to list everything.

if(pVersion != 0)
{
     sSQL += " AND S.Version = @Version";
}
if(pSoftNo != 0)
{
     sSQL += " AND S.SoftNo = @SoftNo";
}

Conclusion, if else part is moved to code side.

IF ELSE Statement in SQL

7 Answers7

Linked