Search notes:

SQL Server T-SQL: IF statement

The if statement allows to conditionally execute statements depending on the value of a boolean expression.

There is no then, elseif or elsif keyword in SQL Server.

SQL-Server does not have a real boolean data type. The closest it can offer is the bit data type. However, even an expression that evaluates to a bit data type needs to be explicetly compared to either 1 or 0 in an if statement as outlined here.

Without blocks

if      datepart(hour, sysdatetime()) <  7

        print('Too early');

else if datepart(hour, sysdatetime()) < 18

        print('Ok');

else
        print('To late');

Github repository about-MSSQL, path: /t-sql/statements/if/if-else-if-no-block.sql

With begin … end blocks

If the if or else part controls more than one statement then they need to be enclosed in a begin … end block:

if db_name() = 'PROD'
  begin

    print('Updating Production DB');
    update tq84_bla set x = x * 2;

  end
else
  begin

    print('Updating Test DB');
    update tq84_bla set x = x - 10;

  end

Github repository about-MSSQL, path: /t-sql/statements/if/if-else-with-block.sql

if [not] exists(…)

A particularly nice, imho, feature in T-SQL is the possibility to combine the SQL operator exists with an if statement:

create table some_data (
   id  integer,
   txt varchar(10)
);

insert into some_data values
  (3, 'three'),
  (5, 'five' ),
  (9, 'nine' );

if not exists (select * from some_data where id between 6 and 8) begin
   print('No record found, insert one');
   insert into some_data values (7, 'seven');
end;

select * from some_data;

Github repository about-MSSQL, path: /t-sql/statements/if/exists.sql

SQL Server T-SQL: IF statement

Without blocks

With begin … end blocks

if [not] exists(…)

See also