5 - Introduction to Dynamic SQL

Table of Contents

What is Dynamic SQL

In MS SQL, dynamic SQL is part of T-SQL (Transactional SQL) and varies greatly between different dialects of SQL. Specifically Oracle uses an extension to SQL called PL/SQL and SQL Server uses an extension to T-SQL. Although they differ greatly, the fundamental concepts in both are relatively equivalent, so learning one will enable the learner to easily learn the other at a later time.

In all cases, dynamic SQL allows the use of software coding operators, algorithms, and patterns to create dynamic SQL that can even be taken as far as being developed using object oriented programming techniques. The separation of concerns, code repeatability, and the reduction in workload are just come advantages of Dynamic SQL.

Hello World!

As you always do when learning a new language, we start with the typical Hello World! application.

BEGIN
    PRINT 'Hello World!'
END

In SSMS, highlight all three lines and execute it. The output from the PRINT statements will appear in the messages output window.

Running Scripts Basics

BEGIN and END

Scripting or statement "blocks" in SQL will be contained within a BEGIN and END container. BEGIN and END are used for most code blocks and are often nested several levels deep. This is somewhat similar to curly braces {} in languages like JavaScript and the C family of languages. We will see this more as we progress through this chapters content.

Another rule of thumb with BEGIN and END are that they are only required if there is more than one line of code to be executed.

DECLARE and SET Parameters

In SQL, a parameter, or a variable, can be declared using a DECLARE statement and prefixed with the "@" symbol.

The value of a parameter can be set during the declaration as a default value or using the SET statement.

BEGIN
    DECLARE @x INT = 5;
    DECLARE @y INT;
    SET @y = 7;
    SELECT @x + @y AS tot;
END;

Populating parameters from a SELECT statement

Parameter values can be set as the result of another SQL statement.

BEGIN
    DECLARE @NumEmps INT;
    SET @NumEmps = (SELECT Count(*) FROM employees);
    PRINT 'There are ' + CONVERT(varchar, @NumEmps) + ' employees in the database.';
END;

Scripting CRUD statements

When we extend DML to include the other CRUD statements, INSERT, UPDATE, and DELETE we can use parameters to assist in how this works. The purpose of doing this is such that code can be repeatable.

BEGIN
    DECLARE @countryID char(2), @countryName nvarchar(40), @regionID tinyInt;
    
    SET @countryID = 'CA';
    SET @countryName = 'Canada';
    SET @regionID = 2;

    INSERT INTO countries VALUES (@countryID, @countryName, @regionID);
END;

If you can imagine where we will go with this, that the above code makes inserting records extremely repeatable once we stored named objects.

Another example:

BEGIN
    DECLARE @locationID INT = 100
    DELETE FROM locations WHERE locationID = @locationID;
END;

Implicit System Functions

When executing CRUD statements, it will be equally important to retrieve feedback to if the statement was successful or failed. The main indicator of this will be to determine the number of rows impacted by the statement. The @@ROWCOUNT is a built in system function that returns the number of rows impacted by the single previous SQL statement.

BEGIN
    DECLARE @NumRowsUpdated INT, @lastName nvarchar(20) = 'Smith'

    UPDATE employees SET monthlySalary = monthlySalary * 1.01
    WHERE lastName = @lastName;

    SET @NumRowsUpdated = @@ROWCOUNT;
    PRINT 'There were ' + CONVERT (varchar,@NumRowsUpdated) + ' employees given a raise of 1%';
END

Other System Functions

There are several system functions that exist in dynamic SQL that assist in obtained additional information about statements that are run.

System Functions
Function Description
@@ROWCOUNT returns the number of rows affected by the most recent SQL statement
@@SERVERNAME returns the name of the local server
@@ERROR returns the error number generated by the execution of the most recent SQL statement. Returns 0 if no error occurred.
@@IDENTITY returns the last value generated for an identity column on the server. Returns NULL if no identity value was generated.
HOST_NAME returns the name of the current workstation
SYSTEM_USER returns the name of the current user

Note that some system functions start with @@ and other do not. This is simply a result of when the functions were added. The original functions start with @@ while newer ones do not. i.e.: there is no real good reason.

@@IDENTITY

Along with @@ROWCOUNT, @@IDENTITY is one of the most used system functions with transactional SQL. Let us image that we have a parent and child foreign key relationship between two tables and both tables use auto-numbering identity for their Primary Key. When you insert the parent record, the PK would be auto generated and the SQL would not know the actual value when the child record is to be inserted, which requires the value to insert the Foreign Key (FK) value. The @@IDENTITY function allows us to obtain the value of the PK inserted into the parent table, so that it can be used to insert the FK in the child table.

BEGIN
    DECLARE @NewPK INT, @RowsInserted INT;

    INSERT INTO parentTable (field1, field2) VALUES (value1, value2);
    -- note we are not inserting the PK value as it is an identity (auto-number)

    SET @NewPK = @@IDENTITY;

    INSERT INTO childTable (field1, FK1, field2) VALUES (value1, @NewPk value2);

    SET @RowsInserted = @@ROWCOUNT;

    PRINT CONVERT(varchar,@RowsInserted) + ' child rows were inserted';
END

Conditional Statements

Just like other structured programming languages, dynamic SQL allows for the use of conditional statement. These are very similar to those in languages such as JavaScript or C.

IF - ELSE IF - ELSE structure

To execute a statement or a block of statements based on a condition, you use the IF-ELSE IF-ELSE statement. When an IF statement is executed, SQL Server evaluates the conditional expression after the IF keyword. If this condition is true, the statement or block of statements after the IF keyword is executed. If not, then each subsequent ELSE IF condition is evaluated. If none of the IF or ELSE IF conditions are true, then the statement or block of statements after the ELSE keyword is executed.

Syntax

IF boolean_expression
    statement
ELSE IF boolean_expression
    statement
ELSE
    statement

-- note that there is no END part of the IF ELSE structure

If any code block under any of the conditions requires more than 1 statement, then it must be surrounded in a BEGIN..END container.

IF boolean_expression
    BEGIN
        statement 1
        statement 2
    END
ELSE IF boolean_expression
    statement
ELSE
    statement

Examples

Example 1
BEGIN
    DECLARE @NumRows INT = 0, @departmentID INT = 30

    UPDATE employees SET monthlySalary = monthlySalary * 1.1
    WHERE departmentID = @departmentID;

    SET @NumRows = @@ROWCOUNT

    IF @NumRows = 0
        PRINT 'No rows were updated!'
    ELSE IF @NumRows = 1
        PRINT 'One row was updated!'
    ELSE
        PRINT CONVERT(varchar, @NumRows) + ' rows were updated.'
END
Example 2
-- Assuming both tables use an identity (auto-number) for the PK
BEGIN
    DECLARE @parentFirstName nvarchar(20), 
        @parentLastName nvarchar(20), 
        @childFirstName nvarchar(20), 
        @childLastName nvarchar(20);
    DECLARE @parentID INT = 0, childID INT = 0;

    INSERT INTO parentTable (firstName, lastName) VALUES (@parentFirstName, @parentLastName);
    SET @parentID = @@IDENTITY;

    IF @parentID > 0
        BEGIN    -- note the BEGIN..END block here
            PRINT 'Parent was inserted';
            INSERT INTO childTable (firstName, lastname, parentID) 
                VALUES (@childFirstName, @childLastName, @parentID);
            SET @childID = @@IDENTITY;

            PRINT 'both the parent and child were inserted successfully'
        END;
    ELSE
        PRINT 'No parent record could be inserted, therefore no child is inserted either';
END

CASE SELECT

The CASE SELECT structure can be used in cases where there are many possibilities or as simply an alternative to an IF structure..

It is important to note that in the coding structure, CASE cannot be used as a standalone condition operator, but used more as an inline conditional algorithm.

Example

BEGIN
    DECLARE @monthNum INT;
    DECLARE @monthName varchar(9);
    SET @monthNum = month(getdate());

    SET @monthName = (SELECT 
        CASE @monthNum
            WHEN 1 THEN 'January'
            WHEN 2 THEN 'February'
            WHEN 3 THEN 'March'
            WHEN 4 THEN 'April'
            WHEN 5 THEN 'May'
            WHEN 6 THEN 'June'
            WHEN 7 THEN 'July'
            WHEN 8 THEN 'August'
            WHEN 9 THEN 'September'
            WHEN 10 THEN 'October'
            WHEN 11 THEN 'November'
            WHEN 12 THEN 'December'
        END);

    Print 'Today''s day name is: ' + @monthName;
END

IF EXISTS

The IF EXISTS statement can be used to determine if a specific database object exists in the database before performing an associated operation.

DROP table employees IF EXISTS;

OR

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'employees')
            DROP TABLE employees;

You can use the OBJECT_ID function to check for the existence of a table, view, stored procedure, user-defined function, or trigger. You use the DB_ID function to check for the existence of a database. Both functions return a null value if the object doesn’t exist. Otherwise, they return the object’s identification number.

IF OBJECT_ID('InvoiceCopy') IS NOT NULL 
    DROP TABLE InvoiceCopy;

Iterations or Loops

Like conditional structures, looping, or iteration, is also possible in SQL. We will specifically look at the while loop. (Note that a FOR loop does not exist in SQL Server T-SQL)

Syntax

WHILE expression
    BEGIN
        <statement block>
        <BREAK or CONTINUE>
    END

Example 1 - Simple Counting

DECLARE @i INT = 0, @max INT = 10;
WHILE @i < @max
    BEGIN
        SET @i = @i + 1;
        PRINT 'Number: ' + CONVERT(varchar, @i);
    END

-- Output
Number: 1
Number: 2
Number: 3
Number: 4
Number: 5
Number: 6
Number: 7
Number: 8
Number: 9
Number: 10

Example 2 - Multiplication Table

While loops can be nested as well to simulate a 2-dimensional object, such as a 2D array or a coordinate system, or 2 dimensional data system.

DECLARE
    @row INT = 0,
    @col INT = 0,
    @maxNum INT = 10,
    @rowString nvarchar(255) = ''
PRINT '-------------------------------------------'
WHILE @row <= @maxNum
    BEGIN
        SET @rowString = STR(@row,3)
        WHILE @col <= @maxNum
            BEGIN
                SET @rowString = @rowString + STR(@row * @col, 6)
                SET @col = @col + 1
        END
        SET @row = @row + 1
        SET @col = 0
        PRINT @rowString
    END
PRINT '-------------------------------------------'

outputs...

-------------------------------------------
  0     0     0     0     0     0     0     0     0     0     0     0
  1     0     1     2     3     4     5     6     7     8     9    10
  2     0     2     4     6     8    10    12    14    16    18    20
  3     0     3     6     9    12    15    18    21    24    27    30
  4     0     4     8    12    16    20    24    28    32    36    40
  5     0     5    10    15    20    25    30    35    40    45    50
  6     0     6    12    18    24    30    36    42    48    54    60
  7     0     7    14    21    28    35    42    49    56    63    70
  8     0     8    16    24    32    40    48    56    64    72    80
  9     0     9    18    27    36    45    54    63    72    81    90
 10     0    10    20    30    40    50    60    70    80    90   100
-------------------------------------------  

BREAK and CONTINUE

BREAK

It is quite often when iterating through data, that we find what we are looking for, or complete our task, before the entire loop has completed. It is best practice at this point to exit the loop and avoid unnecessary iteration or processing, since we already have our conclusion. Therefore a way is needed to exit the loop before the WHILE expression is met. For this we can use the BREAK statement.

Example

Iterate through the numbers 1 to 100 and add them up, at what number will the total exceed 500?

DECLARE 
    @i INT = 0,
    @iMax INT = 100,
	@total INT = 0,
	@totalMax INT = 500
WHILE @i <= @iMax
    BEGIN
        SET @total = @total + @i
        IF @total >= @totalMax BREAK
        SET @i = @i + 1
    END

PRINT 'The total exceeds ' + STR(@totalMax,3) + ' when @i is ' + STR(@i,3)
-- output
The total exceeds 500 when @i is  32

When the @total value exceeds @totalMax, there is no point in continuing the iteration, as we have reached our answer. Additionally, by exiting immediately, the value of @i is preserved as the answer we were looking for.

CONTINUE

The CONTINUE statement can be used to discontinue or skip a current iteration. This does not exit the loop, just the current instance through the loop.

Example

Print the numbers 1 to 10, skipping 4 and 7

DECLARE @i INT = 0, @iMax INT = 10
WHILE @i < @iMax
    BEGIN
        SET @i = @i + 1
        IF @i = 4 OR @i = 7 CONTINUE -- skips the rest of this iteration of the loop and goes back to the WHILE again 
        PRINT STR(@i,2);
    END
-- output
 1
 2
 3
 5
 6
 8
 9
10