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
END IF;
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.
Variables
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:
CREATE OR REPLACE VARIABLE @start_date date;
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:
CREATE OR REPLACE VIEW myview AS ...
A single coded statement can be used to create and update stored procedures.
CREATE OR REPLACE PROCEDURE my_procedure()
BEGIN
SELECT * from mytable
END
Tables
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;
END IF;
IF NOT EXISTS(
SELECT
*
FROM sys.sysobject
WHERE objname = "my_object"
) THEN
CREATE TABLE my_table (col1 INT, col2);
END IF;
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.