Mar 102013
 

Conditions are really easy in SQL Server. Simple IF at the top and multiple SQL statements in the middle:

IF (SELECT Foo FROM Test WHERE Bar='X') = 42
BEGIN
    DROP TABLE Some;
    CREATE TABLE Some(Thing INT);
END

Unfortunately this is not a standard SQL feature so you cannot count on other databases having it implemented in same manner. One database that does not really support this is PostgreSQL. No matter how you do it, you cannot use pure SQL for conditional execution of multiple SQL statements.

What we can do is fake it:

CREATE OR REPLACE FUNCTION TEMPSQL() RETURNS INT AS '
    DROP TABLE Some;
    CREATE TABLE Some(Thing INT);
    SELECT 0;
' LANGUAGE SQL VOLATILE;

SELECT Foo,
    CASE Foo WHEN 42 THEN
        TEMPSQL()
    END
    FROM Test WHERE Bar='X';

DROP FUNCTION TEMPSQL();

All SQL statements sit inside of a function and condition is modified to use standard SQL 92 CASE statement. SELECT will trigger execution of TEMPSQL function every time Foo is equal to 42.

Notice that this means there should be nothing in function that prevents it from being called more than once. Alternative is to do condition (Bar='X') on field that is unique or just adding LIMIT 1 to statement.

PS: For homework, check why function has INT for return type instead of more logical VOID.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>