Flow-control statements from T-SQL are rather rudimentary compared to similar commands in other modern programming languages such as Visual Basic and C#. Their use requires knowledge and some skill to overcome their lack of user friendliness. However, on a positive note, they allow the creation of very complex procedures.
This section covers the use of the following Transact-SQL statements and programming constructs:
Comments
Statement block
If… Else
While…Break
Break
Continue GoTo
WaitFor
Begin… End
You can include comments inside the source code of a batch or a stored procedure; these comments are ignored during compilation and execution by SQL Server. It is a common practice to accompany source code with remarks that will help other developers to understand your intentions.
Comments can also be a piece of Transact-SQL source code that you do not want to execute for a particular reason (usually while developing or debugging). Such a process is usually referred to as commenting out the code.
There are two methods to indicate a comment. A complete line or part of the line can be marked as a comment if the user places two hyphens (––) at the beginning. The remainder of the line becomes a comment. The comment ends at the end of the line:
-- This is a comment. Whole line will be ignored.
You can place the comment in the middle of a Transact-SQL statement. The following example comments out the last column:
Select Leaseld, LeaseVendor --, LeaseNumber From dbo.Lease Where ContractDate > '1/1/1999'
This type of comment can be nested in another comment defined with the same or a different method:
-- select * from dbo.Equipment -- Just for debugging
This commenting method is compatible with the SQL-92 standard.
The second commenting method is native to SQL Server. It is suitable for commenting out blocks of code that can span multiple lines. Such a comment must be divided from the rest of the code with a pair of delimiters—(/*) and (*/):
/* This is a comment. All these lines will be ignored. */ /* List all equipment. */ select * from Equipment
Comments do not have a length limit. It is best to write as much as is necessary to adequately document the code.
Single-line comments can be nested inside multiline comments:
/* -- List all equipment. Select * from Equipment */
SQL Server 2005 also supports the nesting of multiline comments. Earlier versions of SQL Server had problems with that. In different versions and in different tools, the following may or may not generate a syntax error:
/* This is a comment. /* Query Analyzer in SQL Server 2000 will understand the following delimiter as the end of the first comment. However, it will work fine in SQL Server 2005 Management Studio.*/ This will generate a syntax error in some cases. */ Select * from dbo.Equipment
In Chapter 5 where I discuss batches, I will illustrate the restriction that multiline comments cannot span more than one batch.
Again, your comments will be of benefit to other developers who read your code; your comments will be better still if you make their presence in the code as obvious as possible. It is a favorable, although not required, practice to accompany comment delimiters with a full line of stars, or to begin each commented line with two stars:
/************************************************************ ** File: ap_Equipment_Insert.sql ** Name: ap_Equipment_Insert ** Desc: Insert equipment and equipment type ** (if not present). ** ** Return values: ErrorCode ** ** Called by: middleware ** ** Parameters: ** Input Output ** ___________ __________ ** Make EqId ** Model ** EqType ** ** Auth: Dejan Sunderic ** Date: 1/1/2005 ** ***************************************************************** ** Change History ***************************************************************** ** Date: Author: Description: ** _______ _______ ____________________________________ ** 11/1/2005 DS Fixed:49. Better error handling. ** 11/2/2005 DS Fixed:36. Optimized for performance. *****************************************************************/
Inserting two stars at the beginning of each line serves two purposes:
They are a visual guide for your eye. If you comment out code this way, you will not be in doubt whether a piece of code is functional or commented out.
They force SQL Server to report a syntax error if somebody makes an error (for example, by nesting comments or by spanning comments over multiple batches).
The preceding example is based on part of a SQL script for creating a stored procedure generated by Visual InterDev. It is very useful to keep track of all these items explicitly, especially Description and Change History. It is a personal choice to be more elaborate in describing stored procedures, but if you are, your comments can be used as instant design documentation.
Occasionally, developers believe that this type of header is sufficient code documentation, but you should consider commenting your code throughout. It is important to comment not how things are being done, but what is being done. I recommend that you write your comments to describe what a piece of code is attempting to accomplish, and then write the code itself. In this way, you create design documentation that eventually becomes code documentation.
The developer can group several Transact-SQL statements by using Begin…End statements in a logical unit. Such units are then typically used in flow-control statements to execute a group of Transact-SQL statements together. Flow-control statements, such as If and While, can incorporate a single statement or a statement block to be executed when certain conditions are met.
Begin Transact-SQL statements End
There must be one or more Transact-SQL statements inside a block. If there is only one statement inside, you could remove the Begin and End keywords. Begin and End must be used as a pair. If a compiler does not find a matching pair, it will report a syntax error.
Begin and End can also be nested, but this practice is prone to errors. However, if you are cautious and orderly, there should not be a problem. An excellent way to avoid such problems is to indent the code:
Begin Insert Order(OrderDate, RequestedById, TargetDate, DestinationLocation) Values(@OrderDate, @Contactld, @TargetDate, @LocId) Select @ErrorCode = OOError, @OsrderId = @@Identity if @ErrorCode <> 0 begin RaiseError('Error occurred while inserting Order!', 16,1) Return @@ErrorCode end End
The If statement is the most common flow-control statement. It is used to examine the value of a condition and to change the flow of code based on the condition. First, let us review its syntax:
If boolean_expression _ {Transact-SQL_statement | statement_block} [else {Transact-SQL_statement | statement_block}]
When the server encounters such a construct, it examines the value of the Boolean expression. If this value is True (1), it executes the statements or the statement block that follows it. The Else component of the statement is optional.
It includes a single statement or a statement block that will be executed if the Boolean expression returns a value of False (0).
The following code sample tests the value of the @ErrorCode variable. If the variable contains a 0, the server inserts a record in the Order table and then records the value of the identity key and any error that may have occurred in the process.
If @ErrorCode = 0 Begin Insert dbo.Order(OrderDate, RequestedById, TargetDate, DestinationLocation) Values(@dtOrderDate, @intContactId, @dtTargetDate, @intLocId) Select @intErrorCode = @@Error, @intOrderId = @@Identity End
Let us take a look at a more complex case. The following stored procedure inserts a record in the Equipment table and returns the ID of the record to the caller. The stored procedure accepts the equipment type, make, and model as input parameters. The stored procedure must then find out if such an equipment type exists in the database and insert it if it does not.
Create Procedure dbo.ap_Equipment_Insert_1 -- Store values in Equipment table. -- Return identifier of the record to the caller. ( @chvMake varchar(50), @chvModel varchar(50), @chvEqType varchar(BO) } As declare @intEqTypeId int, @intEqId int -- read Id of EqType Select @intEqTypeId = EqTypeId From dbo.EqType Where EqType = @chvEqType -- does such eqType already exists in the database If @intEqTypeId IS NOT NULL --insert equipment Insert dbo.Equipment (Make, Model, EqTypeId) Values (OchvMake, OchvModel, OintEqTypeId) Else --if it does not exist Begin -- insert new EqType in the database Insert dbo.EqType (EqType) Values (@chvEqType) -- get id of record that you've just inserted Select @intEqTypeId = @@identity --insert equipment Insert dbo.Equipment (Make, Model, EqTypeId) Values (@chvMake, @chvModel, @intEqTypeId) End Select @intEqId = @@identity -- return id to the caller return @intEqId
There are a few items that could be changed in this stored procedure, but the importance of this example is to illustrate a use of the Else statement.
One item that could be improved upon is the process of investigating the EqType table with the Exists keyword. Its use here is similar to its use in the Where clause. It tests for the presence of the records in the subquery:
If [NOT] Exists(subquery) {Transact-SQL_statement | statement_block} [else {Transact-SQL_statement | statement_block}]
The stored procedure prlnsertEquipment can be modified to use the Exists keyword:
. . . If Exists (Select EqTypeId From dbo.EqType Where EqType = OchvEqType) . . .
Naturally, if you use the Not operator, the encapsulated statement will be executed if the subquery does not return records:
Create Procedure ap_Equipment_Insert_2 -- Store values in equipment table. -- Return identifier of the record to the caller. ( @chvMake varchar(50), @chvModel varchar(50), @chvEqType varchar(30) ) As declare @intEqTypeId int, @intEqId int -- does such eqType already exist in the database If Not Exists (Select EqTypeId From dbo.EqType Where EqType = (SchvEqType) --if it does not exist Begin -- insert new EqType in the database Insert dbo.EqType (EqType) Values ((@chvEqType) -- get id of record that you've just inserted Select @intEqTypeId = @@identity End else -- read Id of EqType Select (SintEqTypeId = EqTypeId From dbo.EqType Where EqType = @chvEqType --insert equipment Insert dbo.Equipment (Make, Model, EqTypeId) Values (@chvMake, @chvModel, @intEqTypeId) Select @intEqId = @@identity -- return id to the caller Return @intEqId
Both If and Else statements can be nested:
alter Procedure ap_Equipment_Insert_3 -- Store values in equipment table. s-- Return identifier of the record to the caller. ( @chvMake varchar(50), @chvModel varchar(50), @chvEqType varchar(30) ) As declare @intEqTypeId int, @ErrorCode int, @intEqId int -- does such eqType already exist in the database If Not Exists (Select EqTypeId From dbo.EqType Where EqType = (SchvEqType) --if it does not exist Begin -- insert new EqType in the database Insert dbo.EqType (EqType) Values (@chvEqType) -- get id of record that you've just inserted Select @intEqTypeId = @@identity, @ErrorCode = @@Error If @ErrorCode <> 0 begin Select 'Unable to insert Equipment Type. Error: ', @ErrorCode Return -1 End End Else Begin -- read Id of EqType Select (SintEqTypeId = EqTypeId From dbo.EqType Where EqType = @chvEqType Select @ErrorCode = @@Error If @ErrorCode <> 0 begin Select 'Unable to get Id of Equipment Type. Error: ', @ErrorCode Return -2 End End --insert equipment Insert dbo.Equipment (Make, Model, EqTypeId) Values (@chvMake, @chvModel, @intEqTypeId) -- return id to the caller Select @intEqId = @@identity, @ErrorCode = @@Error If @ErrorCode <> 0 Begin Select 'Unable to insert Equipment. Error: ', @ErrorCode Return -3 End Return @intEqId
There is no limit to the number of levels. However, this capability should not be abused. The presence of too many levels is a sure sign that a more in-depth study should be made concerning code design.
Transact-SQL contains only one statement that allows looping:
While Boolean_expression {sql_statement statement_block} [Break] {sql_statement statement_block} [Continue]
If the value of the Boolean expression is True (1), the server will execute one or more encapsulated Transact-SQL statement(s). From inside the block of statements, this execution can be controlled with the Break and Continue statements. The server will interrupt the looping when it encounters a Break statement. When the server encounters a Continue statement, it will ignore the rest of the statements and restart the loop.
Note | Keep in mind that loops are primarily tools for third-generation languages. In such languages, code was written to operate with records one at a time. Transact-SQL is a fourth-generation language and is written to operate with sets of information. It is possible to write code in Transact-SQL that will loop through records and perform operations on a single record, hut you pay for this feature with severe performance penalties. However, there are cases when such an approach is necessary. |
It is not easy to find bona fide examples to justify the use of loops in Transact-SQL. Let us investigate a stored procedure that calculates the factorial of an integer number:
Create Procedure ap_CalcFactorial -- calculate factorial --1!=1 --3!=3*2*1 -- n! = n * (n-1)* . . .5*4*3*2*1 @inyN tinyint, @intFactorial bigint OUTPUT As Set @intFactorial = 1 while @inyN > 1 begin set @intFactorial = @intFactorial * @inyN Set @inyN = @inyN - 1 end return 0
Another example could be a stored procedure that returns a list of properties assigned to an asset in the form of a string:
alter Procedure ap_InventoryProperties_Get /************************************************************ Return comma-delimited list of properties that are describing asset. i.e.: Property = Value Unit;Property = Value Unit;Property = Value Unit;Property = Value Unit;Property = Value Unit;... test: declare @p varchar(max) exec ap_InventoryProperties_Get 5, @p OUTPUT, 1 select @p *************************************************************/ ( @intlnventoryId int, @chvProperties varchar(max) OUTPUT, @debug int = 0 ) As declare @intCountProperties int, @intCounter int, @chvProperty varchar(50) @chvValue varchar(50), @chvUnit varchar(50) Create table #Properties( Id int identity(1,1}, Property varchar(50), Value varchar(50), Unit varchar(50)) -- identify Properties associated with asset insert into #Properties (Property, Value, Unit) select Property, Value, Unit from dbo.InventoryProperty InventoryProperty inner join dbo.Property Property on InventoryProperty.PropertyId = Property.PropertyId where InventoryProperty.InventoryId = @intlnventoryId if @debug = 1 select * from #Properties -- set loop select @intCountProperties = Count(*), @intCounter = 1, @chvProperties = '' from #Properties -- loop through list of properties while @intCounter <= @intCountProperties begin -- get one property select @chvProperty = Property, @chvValue = Value, @chvUnit = Unit from #Properties where Id = @intCounter -- assemble list set @chvProperties = @chvProperties + '; ' + @chvProperty + '=' + @chvValue + ' ' + ISNULL(@chvUnit, '') -- let's go another round and get another property set ©intCounter = @intCounter + 1 end if Substring(OchvProperties, 0, 2} = '; ' set @chvProperties = Right(@chvProperties, Len(@chvProperties) - 2) drop table #Properties return 0
The GoTo statement forces the server to continue the execution from a label:
GoTo label . . . label:
The label has to be within the same stored procedure or batch. It is not important whether the label or the GoTo statement is defined first in the code. The label can even exist without the GoTo statement. On the contrary, the server will report an error if it encounters a GoTo statement that points to a nonexistent label.
The following stored procedure uses the GoTo statement to interrupt further processing and display a message to the user when an error occurs:
Create Procedure dbo.ap_Lease_Close -- Clear Rent, Scheduleld, and LeaseId on all assets associated -- with specified lease. @intLeaseId int As -- delete schedules Update dbo.Inventory Set Rent = 0, LeaseId = null, LeaseScheduleId = null Where LeaseId = @intLeaseId If @@Error <> 0 Goto PROBLEM_1 -- delete schedules Delete from dbo.LeaseSchedule Where LeaseId = @intLeaseId If @@Error <> 0 Goto PROBLEM_2 -- delete lease Delete from dbo.Lease Where LeaseId = @intLeaseId If @@Error <> 0 Goto PROBLEM_3 Return 0 PROBLEM_1: Select 'Unable to update Inventory!' Return 50001 PROBLEM_2: Select 'Unable to remove schedules from the database! Return 50002 PROBLEM_3: Select 'Unable to remove lease from the database!' Return 50003
Note | The stored procedure is only an academic example. It would be better to use transactions and rollback changes in case of errors. I will describe transactions in Chapter 5. |
There are two ways to schedule the execution of a batch or stored procedure in SQL Server. One way is based on the use of SQL Server Agent. The other way is to use the WaitFor statement. The WaitFor statement allows the developer to specify the time when, or a time interval after which, the remaining Transact-SQL statements will be executed:
WaitFor {Delay 'time' | Time 'time'}
There are two variants to this statement. One specifies the delay (time interval) that must pass before the execution can continue. The time interval specified as a parameter of the statement must be less than 24 hours. In the following example, the server will pause for one minute before displaying the list of equipment:
WaitFor Delay '00:01:00' Select * from Equipment
The other variant is more significant. It allows the developer to schedule a time when the execution is to continue. The following example runs a full database backup at 11:00 P.M.:
WaitFor Time '23:00' Backup Database Asset To Asset_bkp
There is one problem with this Transact-SQL statement. The connection remains blocked while the server waits to execute the statement. Therefore, it is much better to use SQL Server Agent than the WaitFor statement to schedule jobs.