Flow-control Statements


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

  • WhileBreak

  • Break

  • Continue GoTo

  • WaitFor

  • Begin End

Comments

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.

Single-line Comments

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.

Multiline Comments: /**/

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.

Documenting Code

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.

Statement Blocks: BeginEnd

The developer can group several Transact-SQL statements by using BeginEnd 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 

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

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

Unconditional Execution: The GoTo Statement

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.

Scheduled Execution: The WaitFor Statement

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.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net