 | |||
|
|
||
| |||
| |||
| ||||||||
|
|
|||||||
|
|
|
|
| Tutorial: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
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
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
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.
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.
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
Both If and Else statements can be nested:
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
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 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
 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.
|
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||
Home: 
|
|
 |  |  | |  | |  | |  | |  | |  | |  | |  | |
|
|
About Us | Contact Us | For Advertisers | For Business Partners | Site Index | RSS |
|
|
||
|
|||||||
