Description:
Can we have flow control statements in SQL scripts, rather than only within SPs / Functions.
Without IF THEN ELSE IF ELSE END IF it is excessively difficult to produce maintenance scripts to manage a remote system.
I want to be able to do things like:
Create table if not exists
IF primary key exists on wrong column THEN
Drop primary key
END IF
IF primary key does not exist THEN
Create primary key
END IF
IF index exists on wrong column THEN
Drop index
END IF
IF index does not exist THEN
Create index
END IF
IF foreign key exists on wrong columns THEN
Drop foreign key
END IF
IF foreign key does not exist THEN
Create foreign key
END IF
[END OF EXAMPLE]
A script like this can be run to create a database from scratch or it can be used to make small changes to an existing database without having to have individual scripts for every different aspect.
How to repeat:
Any IF THEN ELSE IF THEN ELSE END IF throws an error unless it is inside a SP or Function.
Description: Can we have flow control statements in SQL scripts, rather than only within SPs / Functions. Without IF THEN ELSE IF ELSE END IF it is excessively difficult to produce maintenance scripts to manage a remote system. I want to be able to do things like: Create table if not exists IF primary key exists on wrong column THEN Drop primary key END IF IF primary key does not exist THEN Create primary key END IF IF index exists on wrong column THEN Drop index END IF IF index does not exist THEN Create index END IF IF foreign key exists on wrong columns THEN Drop foreign key END IF IF foreign key does not exist THEN Create foreign key END IF [END OF EXAMPLE] A script like this can be run to create a database from scratch or it can be used to make small changes to an existing database without having to have individual scripts for every different aspect. How to repeat: Any IF THEN ELSE IF THEN ELSE END IF throws an error unless it is inside a SP or Function.