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.
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.
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.
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;
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;
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;
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
There are several system functions that exist in dynamic SQL that assist in obtained additional information about statements that are run.
| 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.
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
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.
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.
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
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
-- 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
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.
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
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;
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)
WHILE expression
BEGIN
<statement block>
<BREAK or CONTINUE>
END
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
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 -------------------------------------------
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.
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.
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.
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