How to make a SQL "IF-THEN-ELSE" statement

Question

I've seen other questions about SQL If-then-else stuff, but I'm not seeing how to relate it to what I'm trying to do. I've been using SQL for about a year now but only basic stuff and never this.

If I have a SQL table that looks like this

|  Name | Version | Category | Value | Number |
|:-----:|:-------:|:--------:|:-----:|:------:|
| File1 | 1.0     | Time     |   123 |      1 |
| File1 | 1.0     | Size     |   456 |      1 |
| File1 | 1.0     | Final    |   789 |      1 |
| File2 | 1.0     | Time     |   312 |      1 |
| File2 | 1.0     | Size     |   645 |      1 |
| File2 | 1.0     | Final    |   978 |      1 |
| File3 | 1.0     | Time     |   741 |      1 |
| File3 | 1.0     | Size     |   852 |      1 |
| File3 | 1.0     | Final    |   963 |      1 |
| File1 | 1.1     | Time     |   369 |      2 |
| File1 | 1.1     | Size     |   258 |      2 |
| File1 | 1.1     | Final    |   147 |      2 |
| File2 | 1.1     | Time     |   741 |      2 |
| File2 | 1.1     | Size     |   734 |      2 |
| File2 | 1.1     | Final    |   942 |      2 |
| File3 | 1.1     | Time     |   997 |      2 |
| File3 | 1.1     | Size     |   997 |      2 |
| File3 | 1.1     | Final    |   985 |      2 |

How can I write a SQL IF, ELSE statement that creates a new column called "Replication" that follows this rule:

A = B + 1 when x = 1
else 
A = B

where A = the number we will use for the next Number
B = Max(Number)
x = Replication count (this is the number of times that a loop is executed. x=i)

The results table will look like this:

|  Name | Version | Category | Value | Number | Replication |
|:-----:|:-------:|:--------:|:-----:|:------:|:-----------:|
| File1 | 1.0     | Time     |   123 |      1 |           1 |
| File1 | 1.0     | Size     |   456 |      1 |           1 |
| File1 | 1.0     | Final    |   789 |      1 |           1 |
| File2 | 1.0     | Time     |   312 |      1 |           1 |
| File2 | 1.0     | Size     |   645 |      1 |           1 |
| File2 | 1.0     | Final    |   978 |      1 |           1 |
| File1 | 1.0     | Time     |   369 |      1 |           2 |
| File1 | 1.0     | Size     |   258 |      1 |           2 |
| File1 | 1.0     | Final    |   147 |      1 |           2 |
| File2 | 1.0     | Time     |   741 |      1 |           2 |
| File2 | 1.0     | Size     |   734 |      1 |           2 |
| File2 | 1.0     | Final    |   942 |      1 |           2 |
| File1 | 1.1     | Time     |   997 |      2 |           1 |
| File1 | 1.1     | Size     |   997 |      2 |           1 |
| File1 | 1.1     | Final    |   985 |      2 |           1 |
| File2 | 1.1     | Time     |   438 |      2 |           1 |
| File2 | 1.1     | Size     |   735 |      2 |           1 |
| File2 | 1.1     | Final    |   768 |      2 |           1 |
| File1 | 1.1     | Time     |   786 |      2 |           2 |
| File1 | 1.1     | Size     |   486 |      2 |           2 |
| File1 | 1.1     | Final    |   135 |      2 |           2 |
| File2 | 1.1     | Time     |   379 |      2 |           2 |
| File2 | 1.1     | Size     |   943 |      2 |           2 |
| File2 | 1.1     | Final    |   735 |      2 |           2 |

EDIT: Based on the answer by Sean Lange, this is my 2nd attempt at a solution:

SELECT COALESCE(MAX)(Number) + CASE WHEN Replication = 1 then 1 else 0, 1) FROM Table

The COALESCE is in there for when there is no value yet in the Number column.

NO your edits are all wrong. That is not how a case expression works. You can make it work like that but you would need to use subqueries. In this example why not use something like select coalesce(MAX(Number) + case when Replication = 1 then 1 else 0 end, 1) from Table. — Sean Lange
– Sean Lange, Commented Aug 25, 2015 at 16:56

Laurel · Accepted Answer · 2024-08-22 02:17:33Z

7

The IF/Else construct is used to control flow of statements in t-sql. You want a case expression, which is used to conditionally return values in a column.

Yours would be something like:

case when x = 1 then A else B end as A

edited Aug 22, 2024 at 2:17

Laurel

6,23114 gold badges35 silver badges60 bronze badges

answered Aug 25, 2015 at 16:31

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JohnN Over a year ago

Couple of Questions: 1. does case work in the batch function sqlcmd? If not, then I'll just make a query file and execute it in the script. 2. (more importantly) I'll make an edit to my question, could you verify that it is correct syntax?

Sean Lange Over a year ago

It is part of a query, it is NOT a stand alone mechanism.

logixologist · Accepted Answer · 2015-08-25 16:43:18Z

4

As SeanLange pointed out in this case it would be better to use an CASE/WHEN but to illustrate how to use If\ELSE the way to do it in sql is like this:

 if x = 1
 BEGIN
 ---Do something
 END
 ELSE
 BEGIN
 --Do something else
 END

I would say the best way to know the difference and when to use which is if you are writing a query and want a different field to appear based on a certain condition, use case/when. If a certain condition will cause a series of steps to happen then use if/else

answered Aug 25, 2015 at 16:43

logixologist

3,8644 gold badges34 silver badges47 bronze badges

Collectives™ on Stack Overflow

How to make a SQL "IF-THEN-ELSE" statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related