Geek Answers Handbook

IF ELSE Statement in SQL

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 zero, I want the list of all version numbers to be greater than 0, if it is not equal to 0, then Version should be what the value is.

This is the 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.
+2
source share
7 answers

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 the else part is moved to the code side.

0
source

You should use the CASE WHEN statement instead

+1
source

- :

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))
+1

, SQL-?

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

I don’t quite understand, but if you mean that the input variables are zero, you can 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 )
0
source

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 NOLOCK?)

0
source

Do not use concatenated SQL, this is a bad habit that increases the likelihood of SQL injection vulnerabilities. Your SQL code is now exactly the same as the following (more secure) 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)
0
source

More articles:


All Articles