Using IF inside a SQL Server stored procedure

Question

I was trying to create a procedure which shows the absolute salary of an employee using a param, to do that I need how many children the employee has, this way:

If the salary is < 500 the absolute salary will be:
```
absoluteSalary = salary + numChildren * 200
```

Else:

absoluteSalary = salary + numChildren * 100

This is my code:

create procedure absoluteSalary (@documento varchar(8))
as
begin
    select 
        nombre, apellido,
        if (select sueldo from empleados) < 500
        begin
            sueldo + cantidadhijos*200
        end
        else
        begin
            sueldo + cantidadhijos*200
        end as sueldototal
    from 
        empleados
    where 
       @documento like '%'
end;

But I have a mistake in this if, and I don't know why, my friend did this an it works:

create pa_sueldototal(@documento varchar(8)=’%’) 
as 
    select 
        nombre, apellido,
        sueldoTotal = case 
                         when sueldo >= 500 
                            then sueldo + (cantidadhijos * 100) 
                         when sueldo < 500 
                            then sueldo + (cantidadhijos * 200) 
                      end
    from 
        empleados 
    where 
        documento like @documento;

`case` is the proper way to put conditional logic into a `select`. — Gordon Linoff, May 25 '18 at 16:01
Case is used in sql server to determine the value of a field based on other criteria. Your friend used it correctly here. If/Else logic is used to determine if an action should occur in your code or not. — Daniel Marcus, May 25 '18 at 16:02
You can also use IIF in a select if you have version 2012 or higher. — Tab Alleman, May 25 '18 at 16:04
Possible duplicate of [How do I perform an IF...THEN in an SQL SELECT?](https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select) — Tab Alleman, May 25 '18 at 16:05
The problem in your version lies in the IF statement. (select sueldo from empleados) does not select a specific row from the table. It should really be (select sueldo from empleados where documento like @documento), but even that doesn't make sense. You already have the value to evaluate, so why select from the table again? Also, you where clause looks wrong since you aren't comparing the parameter to a field in the table/query. — UnhandledExcepSean, May 25 '18 at 16:14

S3S · Answer 1 · 2018-05-25T16:05:05.353

A case expression would do it. It's like an IF statement, but is a more logical way of writing this query. Also, your where clause would bring back non-nulls, which is better done with IS NOT NULL

create procedure absoluteSalary (@documento varchar(8))
as
begin
select 
    nombre,
    apellido,
    sueldototal = 
        case 
            when sueldo < 500 then sueldo + cantidadhijos*200
            else sueldo + cantidadhijos*100 
        end
from empleados
--this isn't needed
--where @documento like '%'

Using IF inside a SQL Server stored procedure

1 Answers1