Blocks, looping and conditional flow control statements
Statements blocks (Begin…End) can be used to group several statements to be executed together. Conditional execution (If statements) are used to change the flow of code based on the value of a condition. T-SQL contains only one statement that allows looping, which is also outlined in this excerpt.
Statement Blocks: Begin…End The developer can group several Transact-SQL statements by using Begin…End statements...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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, @ContactId, @TargetDate, @LocId) Select @ErrorCode = @@Error, @OrderId = @@Identity if @ErrorCode <> 0 begin RaiseError('Error occurred while inserting Order!', 16,1) Return @@ErrorCode end End
Conditional Execution: The If Statement
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(30) ) 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 (@chvMake, @chvModel, @intEqTypeId) 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 prInsertEquipment can be modified to use the Exists keyword: . . . If Exists (Select EqTypeId From dbo.EqType Where EqType = @chvEqType) . . .
The stored procedure prInsertEquipment can be modified to use the Exists keyword:
. . .If Exists (Select EqTypeId From dbo.EqType Where EqType = @chvEqType) . . .
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 = @chvEqType) --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 @intEqTypeId = 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
alter Procedure ap_Equipment_Insert_3 -- 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, @ErrorCode int, @intEqId int -- does such eqType already exist in the database If Not Exists (Select EqTypeId From dbo.EqType Where EqType = @chvEqType) --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 @intEqTypeId = 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.
Looping: The While Statement
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, but 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 *************************************************************/ ( @intInventoryId 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 = @intInventoryId 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(@chvProperties, 0, 2) = '; ' set @chvProperties = Right(@chvProperties, Len(@chvProperties) - 2) drop table #Properties return 0
![]()
Basic Transact-SQL Programming Constructs
![]()
Home: Introduction
Tip 1: T-SQL identifiers
Tip 2: Database object qualifiers
Tip 3: Character string data types
Tip 4: Date, time and number data types
Tip 5: Special data types-Part 1
Tip 6: Special data types-Part 2
Tip 7: Local variables
Tip 8: Global variables
Tip 9: Table variables
Tip 10: Flow control statements
Tip 11: Blocks, looping and conditional statements
Tip 12: unconditional and scheduled execution
Tip 13: Transact SQL cursors
Tip 14: Cursor related statements
Tip 15: Problems and justified use of cursors
The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.