Writing structured programs for the SQL server DBMS

Transact SQL may be used to write structured programs for the SQL Server DBMS. Programs may contain a sequence of commands, take input, return output and contain variables that hold intermediate and final values. Some programs add structure to sets of SQL commands and are referred to as procedures and others are called functions. Procedures carry out tasks via conditional constructs, looping constructs that execute a piece of code over and over again and so on.

 print('Hello World')

The above command is a T-SQL directive, not a SQL query. The 'print' function carries out the simple task of printing out a line onto the output. A stored procedure may carry out a set of such tasks. For example, the following set of commands greet a user and display the current date and time. This code may be compiled and executed by running the code on a SQL Query window and hitting the execute button or the F5 key.

print('Hello ' + user + '!')
print ('It is ' + convert(char(8),getdate(),8) +
' on ' + convert(char(12),getdate(),107))

Output of greeting Code

We could define this simple set of commands as a stored procedure which we can schedule to execute each time a user logs into the server. The CREATE PROCEDURE statement is used to create a new procedure. Note that you should open a new query specifically for the database that you wish to add the procedure to in order to add the procedure to that database. Right click on the database of interest and click on 'New Query' to open a query window for that database. Once you run the following set of commands, you will be able to see a new Stored procedure when you expand and refresh the 'Stored Procedures' folder under the 'Programmability' folder for the database under which you run the creation command.

CREATE PROCEDURE greeting
AS
BEGIN
DECLARE @this_user varchar(15)
set @this_user = user
print('Hello ' + @this_user + '!')
print ('It is ' + convert(char(8),getdate(),8) +
' on ' + convert(char(12),getdate(),107))
END

We declare a variable called this_user and set it to equal the current user (the 'user' function returns the current user). Note that all user variables should be preceded by the symbol '@'. We use the string concatenation operator '+' to combine literal strings such as 'Hello ' with the values of variables such as this_user, and getdate(). The 'CREATE PROCEDURE greeting' command creates a procedure with the name greeting. This is followed by the keyword 'IS', variable initializations and declaration, and finally the code within the 'BEGIN' and 'END' statements. Note that the BEGIN and END statements denote a code block. The EXECUTE command may be used to run a stored procedure.

EXECUTE greeting
OR
EXEC greeting

You may delete a stored procedure by issuing a 'DROP PROCEDURE' command or by right clicking on it and selecting delete.

DROP PROCEDURE greeting

Creating a procedure using the GUI is very similar to creating one using a SQL query window. Right click on the 'Stored Procedures' Link under 'Programmability' and click on 'New Stored Procedure..'. A new query window with a Stored Procedure template will be displayed. Change the template and save the stored procedure by right clicking on the Query window tab and selecting 'Save'.

Stored Procedures are more efficient than raw queries as the SQL Server compiles each stored procedure just once and then reuses the execution plan. Further, in cases where the server is accessed remotely, a single execute statement saves bandwidth unlike lengthy strings that contain sets of SQL queries. Also, users who are given access to stored procedures need not necessarily have access to the underlying tables. Stored procedures may be used to provide a layer of separation and security.

A T-SQL procedure may be a lot more complicated and includes several loops or conditional statements. It may also take input parameters and return output. The following stored procedure takes in the author's lastname and firstname as parameters and returns the total number of books written by the author placed on order.

CREATE PROCEDURE book_orders
(@lastname_param VARCHAR(100),
@firstname_param VARCHAR(100))
AS
BEGIN
DECLARE @book_count int
DECLARE @prn_message varchar(300)
set @book_count = (SELECT sum(quantity)
FROM book, author, orderdetails
WHERE book.authorid = author.authorid
AND orderdetails.bookid = book.bookid
AND author.lastname = @lastname_param
AND author.firstname = @firstname_param)
set @prn_message = CASE
WHEN @book_count > 1 THEN
@book_count + ' books by '
+ @lastname_param + ', ' + @firstname_param +' ordered'
WHEN @book_count = 1 THEN
'@One book by '
+@lastname_param + ', '+ @firstname_param +' ordered'
ELSE
'No books by '
+@lastname_param + ', ' + @firstname_param +' ordered'
END
print(@prn_message)
END

The procedure takes as input the lastname and firstname of the author. The variable 'book_count' is set to contain the results of the join query (in red) that produces the desired result - a count of books by the specified author. Also note the CASE - ELSE - END loop. This loop sets a custom message (@prn_message) based on the number of books found. Finally, the print function is used to print the output message. Run the stored procedure in the following way after creation:

exec book_orders 'Shakespeare', 'William'

Output of book_orders

The above program is limited - it is hard to specify an exact author name string. How would we make this procedure more general? We could fix it so that a short form of just the last name is taken as input. The procedure should then return a sorted list of all authors whose lastname matches the input string and the number of books by the author in the orderdetails table. This result, however, involves sending back a set of rows. This may be accomplished by using a cursor in the following way:

create PROCEDURE book_orders_extended
(@lastname_param VARCHAR(100))
AS
BEGIN
DECLARE @author VARCHAR(100)
DECLARE @book_count integer
DECLARE books_sum CURSOR FOR
SELECT lastname+', '+firstname AS AUTHOR, sum(quantity)
FROM book, author, orderdetails
WHERE book.authorid = author.authorid
AND orderdetails.bookid = book.bookid
AND author.lastname like @lastname_param+'%'
GROUP BY lastname, firstname
ORDER BY lastname, firstname
OPEN books_sum
FETCH NEXT FROM books_sum
INTO @author,@book_count
WHILE @@FETCH_STATUS = 0
BEGIN
print(convert(varchar(5),@book_count) + ' books by ' + @author)
FETCH NEXT FROM books_sum
INTO @author,@book_count
END
CLOSE books_sum
DEALLOCATE books_sum
END

We declare a cursor called books_sum and variables author and book_count. We set the cursor variable books_sum equal to the set of rows returned by the query we used (with a few modifications - mainly, we use wildcards) within the program last time. We then open the cursor and loop through each available line of the cursor (@@FETCH_STATUS is a system variable that remains 0 while there are more rows in the cursor). Each returned row is in turn stored in variables author and book_count and displayed using the 'print' command. We exit the 'while' loop when new rows are not found. Finally, we close the cursor and re-allocate the space it takes up in the SQL Server memory space. The following are a couple of outputs for the new and improved book_orders_extended procedure. You may now look for authors merely by typing part of the first name in either the upper or lowercase if you left the default value of collation (case insensitive) as is during your installation. Otherwise, you may have to be specific with the case of the partial name.

exec book_orders_extended 'b'
exec book_orders_extended 'car'

Output of Extended book Orders

A function is very similar to a stored procedure. However, a function always returns a single value through the 'return' statement. A function may be used as part of a SQL command like any SQL Server built-in function. The following function computes and returns the discounted price of a book given the unitprice and discount percentage.

create function apply_discount
(@unitprice money,
@discount int)
returns money as
BEGIN
DECLARE @discountprice money
SET @discountprice = @unitprice*(100-@discount)/100
RETURN @discountprice
END

The following query returns the total price after applying discount on each order. It uses the apply_discount function to calculate the total price. The ISNULL T-SQL function returns the discount percent if available or '0' if the discount column contains null. Note that we use 'dbo.apply_discount' - this prompts the SQL server to search for apply_discount among user DBO's custom functions. Partial results are shown underneath.

select orderid,
sum(quantity*(dbo.apply_discount(unitprice,ISNULL(discount,0))))
AS TOTALPRICE
from orderdetails
group by orderid
order by orderid

Select Query that uses custom function

A trigger database object is used to execute sets of commands each time a certain event happens. In other words, a specified event 'triggers' execution of the commands. The general syntax of a trigger follows. The '|' symbol stands for or - just one of the keywords should be used:

CREATE TRIGGER <trigger_name>
ON { table | view }
{
{ { FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS
<All T-SQL Statements that make up the trigger>
}

A trigger may be defined for a table or view and be executed before, after or instead of an insert, update, or delete event. The following simple trigger prints out the name of a new author after executing each insertion command for the author table:

CREATE TRIGGER SHOW_AUTHOR
ON AUTHOR
AFTER INSERT
AS
BEGIN
DECLARE @author VARCHAR(100)
SET @author = (SELECT firstname + ' ' + lastname FROM Inserted)
PRINT 'New author "' + @author + '" added'
END

The above trigger is fired after the insertion of each new row into the AUTHOR table. It performs just one function: printing the name of the author just added. We declare a variable called author and set it to contain the inserted values. The "Inserted" table is a virtual SQL Server System table that contains all the column values for the insert that fired the trigger. We use this table to extract the values of author lastname and firstname. Finally, we print a message with the name of the new author.

Testing our Trigger