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
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.
