My First Script

[eveCalypso]

A While ago I posted a question regarding advice on how to do quite a few calculations on the DB side as a transaction, and have thus stumbled into the world of Transact-SQL.

The following script works in the query analyser and does exactly what I want it to do, however, I would appreciate it if you could tell me what I have
1) done wrong
2) can do better
3) not testing (like errors etc)
4) how I can call this script from code (i.e. do I 'make' it into a Stored Procedure?
5) make sure the Transaction Commit/Rollback do what I expect.

Your comments will be appreciated!! Just remember, its my first script!!

Incoming and Other Variables
DECLARE @givenStockID AS BIGINT, @TransDate AS DATETIME, @TransQuantity as int, @TransPricePerItem as money,@TransAdjQuantity as int, @BinLocationID as int, @reasonID as int, @TransNotes as varChar(400), @CostCentreCode as varchar(12), @insUser as int

-- Variables for testing
SET @givenStockID=3
SET @reasonID = 2
SET @TransNotes = 'This is an insert from the inside of SQL!!'
SET @CostCentreCode = 'N/A'
SET @insUser = 2
--

-- Subset of transactions to iterate through and do check calculations on
DECLARE stockTrans CURSOR LOCAL STATIC FOR
SELECT TransDate, TransQuantity, TranspricePerItem,TransAdjQuantity,BinLocationID
FROM [Stock Transactions]
WHERE StockItemID = @givenStockID
AND InvoiceNo IS NOT NULL
AND (TransAdjQuantity >0 OR TransAdjQuantity <0)
OPEN stockTrans

-- @incomingQty is sent from application, the others will be used here
DECLARE @totalAvailQty as int, @incomingQty as int, @ReturnError as int
SET @ReturnError = 0
-- for testing
SET @incomingQty = 9

-- Must check if the item has enough stock balance in total to satisfy this request before updates are done
EXEC getStockBalAvailable @givenStockID, @qtyAvail = @totalAvailQty OUTPUT
--PRINT @totalAvailQty
--PRINT @incomingQty
IF @totalAvailQty < @incomingQty
BEGIN
SET @ReturnError = 50
GOTO HowToExit
END

-- All the subsequent inserts/updates must either fail or succeed as a LUW
BEGIN TRANSACTION

FETCH NEXT FROM StockTrans
INTO @TransDate, @TransQuantity, @TransPricePerItem, @TransAdjQuantity, @BinLocationID
-- This must loop for as long as there were no errors or I have handled the qty adjustment required
WHILE ((@@FETCH_STATUS = 0) and @incomingQty<>0)
BEGIN
IF @incomingQty >=0 AND @TransAdjQuantity <> 0
BEGIN
--PRINT 'IN SCENARIO 1'
DECLARE @tmpQty as int, @insertQty as int
SET @insertQty = 0
SET @tmpQty = @TransAdjQuantity
IF @incomingQty <= @tmpQty
BEGIN
-- Update the transaction's running balance avail with old qty - remaining given Qty
UPDATE [STOCK TRANSACTIONS]
SET TransAdjQuantity = @tmpQty - @incomingQty
WHERE StockItemID = @givenStockID
AND TransDate = @TransDate
IF (@@ERROR <> 0)
BEGIN
SET @ReturnError = @@ERROR
GOTO HowToExit
END
-- Set the remaining Qty to zero
Set @insertQty = @incomingQty
SET @incomingQty = 0
END
ELSE
BEGIN
--PRINT 'IN SCENARIO 2'
-- Update the transaction's running balance to zero and calc the remaining qty to be handled.
UPDATE [STOCK TRANSACTIONS]
SET TransAdjQuantity = 0
WHERE StockItemID = @givenStockID
AND TransDate = @TransDate
IF (@@ERROR <> 0)
BEGIN
SET @ReturnError = @@ERROR
GOTO HowToExit
END
-- Adjust the Qty remaining to be handled
SET @incomingQty = @incomingQty - @tmpQty
SET @insertQty = @tmpQty
END

-- Insert a new entry into transaction table with correct values for given Qty's - FIFO
WAITFOR DELAY '00:00:001' -- Key Must be Unique
INSERT INTO [STOCK TRANSACTIONS] VALUES
(@givenStockID, getDate(), Null, @insertQty, @reasonID, @TransPricePerItem, @TransNotes, NULL, @BinLocationID, @CostCentreCode, @insUser, getDate())
IF (@@ERROR <> 0)
BEGIN
SET @ReturnError = @@ERROR
GOTO HowToExit
END

-- Go to next record
FETCH NEXT FROM StockTrans
INTO @TransDate, @TransQuantity, @TransPricePerItem, @TransAdjQuantity, @BinLocationID
END -- Iterate through
END

-- If everything was successful, commit, else rollback.
HowToExit:
IF @ReturnError <> 0
BEGIN
--PRINT 'Error Which Caused Rollback' + CAST(@ReturnError AS VARCHAR(5))
ROLLBACK TRANSACTION
--PRINT 'Rollback Complete'
END
ELSE
BEGIN
--PRINT 'TransActions Successful - About to Update Stock Items'
EXEC updateStockBalanceGen @givenStockID
COMMIT TRANSACTION
--PRINT 'Commit Complete'
END

CLOSE StockTrans
DEALLOCATE StockTrans
GO

Regards,
EvE