CREATE PROCEDURE sp_AddOrder
(
@bill_fname varchar(50) = Null,
@bill_lname varchar(50) = Null,
@items_ordered_Array varchar(500) = '0',
@new_pk_order_id int OUTPUT
)
AS
DECLARE @returnval int,
@nCnt int,
@nLen int,
@nGetItem int,
@nItem int,
@nQty int,
@sTmp Varchar(10),
@sItem VarChar(10),
@sQty VarChar(10)
BEGIN
select @returnval = 0
BEGIN TRANSACTION AddOrderTrans
insert into Orders(bill_fname,bill_lname,order_datetime) values (@bill_fname,@bill_lname,GetDate())
if (@@ERROR <> 0) Goto OnErrorTrap
select @new_pk_order_id = @@identity
/* get the length of the @items_ordered_Array
string for later use. */
select @nLen = Len(@items_ordered_Array)
/* set our counter to start with 1 as that will be
the first position in the delimited string that
we look at */
select @nCnt = 1
/* The variable @nGetItem is used to determine
whether the current value should be
added onto the @sItem variable. This
variable will eventually hold a string/varchar
version of our Item number.
If the @nGetItem = 1, then it is true. */
select @nGetItem = 1
/* @sItem and @sQty are the string/varchar version
of the item number and quantity values we will
end up inserting into the OrderedItems table.
We must set these variables to empty or we'll
wind up with a Null value in the string(s). */
select @sItem = ''
select @sQty = ''
/* enter into a loop where we will check each
character in the @items_ordered_Array string. */
WHILE (@nCnt <= @nLen)
BEGIN
/* Get the current character and store it
in the temp variable @sTmp */
select @sTmp = SUBSTRING(@items_ordered_Array,@nCnt,1)
/* if we find the | or pipe character, then we
should attempt an insert into the
OrderedItems table using the values
in @sItem and @sQty variables */
if (@sTmp = '|')
BEGIN
/* set the next iteration after this one
in the loop back to setting the @sItem
variable */
select @nGetItem = 1
/* make sure there are no trailing
blanks in these variables */
select @sItem = ltrim(rtrim(@sItem))
select @sQty = ltrim(rtrim(@sQty))
/* convert these variables to integers as
their data type must match the column data
type during the insert into the OrderedItems
table */
select @nItem = CAST(@sItem as int)
select @nQty = CAST(@sQty as int)
/* perform the insert for this item number using
the newly created @new_pk_order_id value
from the insert of the Orders table. */
insert into OrderedItems
(
fk_order_id,
fk_item_id,
qty
) values
(
@new_pk_order_id,
@nItem,
@nQty
)
if (@@ERROR <> 0) Goto OnErrorTrap
/* reset the variables back to an empty
string and prepare for another record */
select @sItem = ''
select @sQty = ''
END
/* check the @sTmp variable and see if we need
to set the @nGetItem to store future values
in the @sItem variable. Remember, we are
using the - or dash character to indicate
that all future values should be added on
to the @sQty variable until we find another
delimiter. */
if (@sTmp = '-') select @nGetItem = 0
/* if the current character is not a delimiter
character, add the value to the @sItem
or @sQty variable based on the status of
our @nGetItem variable */
if ((@sTmp <> '|') AND (@sTmp <> '-'))
BEGIN
if (@nGetItem = '1')
BEGIN
select @sItem = ltrim(rtrim(@sItem)) + @sTmp
END
if (@nGetItem = '0')
BEGIN
select @sQty = ltrim(rtrim(@sQty)) + @sTmp
END
END
/* increment our loop counter and move on to
the next value in the items_ordered_Array
string */
select @nCnt = @nCnt + 1
END
/* if we get here (after the while loop),
all has gone well and we can commit the transaction */
COMMIT TRANSACTION AddOrderTrans
goto OnExit
END
OnErrorTrap:
/* oops!!! we ran into a problem. Return the
SQL Server error code to ADO and rollback
the transaction */
select @returnval = @@Error
ROLLBACK TRANSACTION AddOrderTrans
OnExit:
RETURN @returnval
GO
|