Blocks, looping and conditional flow control 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, @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.

---