Databases are the foundation of many applications, from simple web applications to complete ERP systems. In BPM, databases maintain the long term state of processes and use that data to ‘rehydrate’ a process instance, provide up to the moment business activity monitoring and feed reporting dashboards and the like. In this article however I want to focus on the processing of (customer, financial, state) data at the data layer (aka logical data level). It is a frequent requirement that processing of data be carried out at the data layer handing back just the required responses to the service layer for subsequent action (e.g. a GUI response to the user).
Lots of application hand off the processing logic of the data they consume to the database server in the form of a stored procedure call. Stored procedures are just collections of statements that you save to a self contained entity for later re-use. They may seem scary if you are new to databases and writing SQL syntax, but once you have grasped the basics and the points we’ll cover in this article, stored procedures can become arguably the easiest programming technique there is (quick basic aside!).
The basic syntax of a stored procedure is as follows:
CREATE PROCEDURE [ProcedureName]
@OutputParameter varchar(10) OUTPUT
Writing procedures require you have a knowledge of commonly used statements, local and global variables, control flow and other essentials. I attempt to cover the most important in the following list.
TSQL statements make up the bulk of your stored procedures. They manipulate your data and control the run time execution of your code. Understanding your TSQL syntax is the first action step when understanding how to read and write stored procedures so take a look at our article on SQL Syntax if you’re not familiar.
Statements constitute the action taken in regards to your data. SELECT, UPDATE, INSERT and DELETE are data manipulation statements. CREATE, ALTER, DROP etc are declaration / definition statements.
Once you understand SQL syntax and can write out statements with ease you may need to batch up your statements into units for your day to day queries. TSQL statement batches are seperated by the GO statement. Pop the GO statement on its own line, it cannot share a line with other statements. Important to remember is that Rules, Triggers, Defaults, Procedures, Views and Functions can not be created in combination within the same batch and since we’re talking about stored procedures you cannot create more than one batch in an sp.
Variables like in all programming languages are a place holder for your data. You may to select a value into a variable in order to manulate it and then re-write that value to the database once certain control flow logic has been followed. TSQL has local and global variables.
Local variables are declared using (oddly enough) the DECLARE statement (specifying the data type whilst doing so):
DECLARE @MyVar smalldatetime, @MySecondVar nvarchar(50)
As shown, you can declare many variables at the time by seperating each variable and datatype after the DECLARE statement with a comma. Local variables are prefixed with a single @.
As with other programming languages, local variables have scope. In the same way .NET stores variables against the stack and you cannot access the local variables of a method that has already exited (i.e the stack frame has been removed), variables are available to SQL at certain points and at most for the life of the procedure execution itself.
Batches for example isolate local variables with the scope of the batch.
DECLARE @MyVar int
SELECT @MyVar = Field FROM Database.[Table] WHERE ID = 2
UPDATE Table SET Field = @MyVar
This scenario would generate an error as the scope of the local variable is to the statements in batch 1. The same goes for table variables set in Batch 1 (references to a table object).
Global variables are prefixed with double @ (e.g. @@ROWCOUNT). Global variables are maintained by SQL Server and you will never populate these with values however you will utilize them as part of the decision making within your code. The most common global variables you’ll use include:
@@IDENTITY – This is the last known scope identity in the current session. If you are inserting a row to a table and need to know the row identity (the column you’ve specified as the incremental identity index) this variable will let you know that. It’s important to get this value and store in a local variable prior to executing any other statements in your procedure so you have the correct value stored before it is overwritten.
@@ERROR – To ensure your processing is executing correctly it is always useful to check the sql error response. Providing everything has gone swimmingly with your previous statement sql server will report this value as an integer of 0. If an error has occured however, this value will not be zero. Running an IF statement on this variable can provide you with the exception handling you need (see the example in the transaction block below). Again, grab this value before writing more statements as the value will be overwritten.
@@ROWCOUNT – Indicates the number of rows affected by the previous statement executed. Once again, grab this value quick to have the right row count stored. If you have NOCOUNT set to ON (SET NOCOUNT ON), this variable will still be populated however sql server will not return the row count to the calling client.
@@TRANCOUNT – The number of BEGIN statements found in a transaction keeping you current with the point at which a transaction may have aborted. We cover transaction later in this article, so for now, just keep this global variable in mind.
There a more global variables of course but these are the most commonly used in writing stored procedures.
4) Control flow
Now that we can run statements within a batch, store our data in local variables and reference sql server global variables we need to look at how and when our statements are executed. Runtime code execution decisions can be made based on your variable values to enable the stored procedure to determine what subsequent statements to execute. This process is called control flow and is a standard characteristic of all programming languages. A standard set of control flow syntax is used using the concepts of looping, conditional branching and jumping to different parts of the code. Keywords such as BEGIN, END, BREAK, CONTINUE, GOTO, IF ELSE, RETURN, WAITFOR AND WHILE are used.
If and Else:
IF [CONDITION IS TRUE]
[STATEMENTS TO EXECUTE]
[STATEMENTS TO EXECUTE]
–This is a statement block
[STATEMENTS TO EXECUTE]
WAITFOR – Sets an amount of time to wait
RETURN – An immediate exit of the stored procedure (or functions)
WHILE [CONDITION IS TRUE]
[STATEMENTS TO EXECUTE]
The while loop will continue to loop until the boolean expression equals true. Normally the statement block affects the data involved in the boolean expression up to the point that expression evaluates to TRUE (e.g. an incremental integer that increases by 1 for each loop instance).
BREAK – Ends a WHILE loop
CONTINUE – Causes the next loop iteration to occur
When thinking of transactions, think of the interactions you have with an ATM in the wall. You start your transaction by passing in your card and entering your pin number. Next you request an amount of money and wait for the ATM to debit your account and then hand over the requested amount of your hard earned cash. If for any reason during this interaction the ATM fails to process your request and you see no money the entire transaction is aborted and your account is left unaffected (the debit does not occur). The same rule applied for statements you execute in your stored procedures. In many cases you may need several statements to execute together in full and if one of them fails all of the statements should ‘roll back’ as if the transaction had not begun. It is all or nothing with transactions.
In terms of your TQL programming you need to wrap your statements in a TRANSACTION block in order to tell sql that the enclosed statements are part of an atomic transaction (its full steam ahead or the transaction ‘blows up’ and nothing is committed). Here is an example use of the transaction block illustrating the use of the TRANSACTION, ROLLBACK and COMMIT key words:
DECLARE @CustName varchar(50),@CustID int,@RowIdent smallint
INSERT INTO Customer (CustomerName,CustomerID) VALUES (@CustName, @CustID)
–Catch an error using the error global variable we mentioned earlier
IF @@ERROR <> 0
SELECT @RowIdent = @@IDENTITY
If all goes well, sql server arrives at the end of the transaction and executes the full commit else if problems are found the ROLLBACK statement is executed and the transcation aborts in full. Notice that the BEGIN TRANSACTION concludes with the END statement.
By now the picture should be a little clearer in terms of how to write a stored procedure. A clear understand of TSQL statement syntax, global variables, rules relating to batches, control flow and transactions constitutes a good grounding for experimenting further with stored procedures.