Syntactically better SQL CREATE statements

Mon, May 26, 2014

Debugging ETL SQL scripts can be frustrating because of the syntax used to create database objects. In this post I share some tips to write syntactically and functionally better SQL scripts that are particularly useful when using SQL for ETL. All syntax written for Sybase IQ 15.4.

Historically to avoid object already exists type errors I used to put a statement such as the one below around most SQL code blocks:

IF NOT EXISTS(SELECT * FROM sys.sysobject WHERE objname = "my_object" ) THEN
  do something

As the SQL script gets longer it becomes harder to separate actual code implementing the business logic from code used to make sure that your script does not fail. I found that there are smarter ways to write safe code.


The typical code used for creating variables is as follows:

CREATE VARIABLE @start_date date;
SET @start_date = getdate();

The problem with creating variables in this way is that the variable persists for the duration of your session. If you try rerun the SQL statement you will get an error because the variable already exists. You need to drop the variable before you can rerun the part of SQL code. It is good practice to drop your variables at the end of your script but the code you are testing might be nowhere near the end of your full script.

To test your code your either have to drop your connection to the database and reconnect or run the DROP VARIABLE statement before running the CREATE VARIABLE statement. The following syntax solves all these problems:


You can run this statement over and over without worrying if a variable already exists and without the need to drop your database session to flush out the variables.

Views and Stored Procedures

The first time you create a view you use the CREATE VIEW statement then changes to the view are done using the ALTER VIEW statement. To use one statement for creating or altering a view use the following statement:


A single coded statement can be used to create and update stored procedures.

SELECT * from mytable


You only ever need to create a table once so when debugging a script you have to keep dropping the table to test your script. You have to be careful to remove the DROP TABLE statement in the script promoted to production. Sometimes you can forget to remove the DROP statement. Instead of using

 IF EXISTS(SELECT * FROM sys.sysobject WHERE objname = "my_object" ) THEN
    DROP TABLE my_table;

IF NOT EXISTS(SELECT * FROM sys.sysobject WHERE objname = "my_object" ) THEN
  CREATE TABLE my_table (col1 INT, col2);

you only need use one statement.

CREATE TABLE IF NOT EXISTS my_table (col1 INT, col2);

Keeping the syntax simple helps keep the scripts simple which in turn allows you to focus on coding the business logic and not focusing on the syntax. Please share your tips for other database servers.

Tags create table/etl/sql/sybase/

Related Posts

Three very useful open source SQL editors
SQL: Introduction to SELECT statement, analogy for beginners