I have written the following Stored Procedure in an attempt to update two tables in the same database reliably but unfortunately it is not too successful. I ocassionaly end up with only the BundlesIssued tables updated and nothing in TicketsIssued.
Please make suggestions on how I could make this stored procedure update both tables reliably. -
ALTER PROCEDURE spIssueScannedTickets
-
@iEventID int,
-
@MemberNum nvarchar(12),
-
@BatchSize int,
-
@FirstNumber nvarchar(12),
-
@LastNumber nvarchar(12),
-
@SlotsBalance int,
-
@TableBalance int,
-
@BonusBalance int,
-
@UserID int
-
AS
-
DECLARE @Result int
-
-
-- Declare variables used in error checking.
-
DECLARE @error_var int, @rowcount_var int
-
-
-
SET @Result = 0
-
BEGIN TRANSACTION
-
INSERT INTO BundlesIssued (MemberNumber, EventID, BundleSize, FirstNumber, LastNumber, DateIssued, UserID, Invalid)
-
VALUES (@MemberNum, @iEventID, @BatchSize, @FirstNumber, @LastNumber, GETDATE(), @UserID , 0x00)
-
if (@SlotsBalance>0)
-
-
-- Save the @@ERROR and @@ROWCOUNT values in local
-
-- variables before they are cleared.
-
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
-
-- check if an error occured and if the expected number of records were affected
-
IF @error_var <> 0 or @rowcount_var <> 1
-
BEGIN
-
ROLLBACK
-
-- PRINT "Warning: Error on Insert 1"
-
RETURN(-1)
-
END
-
-
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
-
VALUES (GETDATE(),@iEventID,@MemberNum, 'Slots', @SlotsBalance, 0, @UserID)
-
if (@TableBalance>0)
-
-
-- Save the @@ERROR and @@ROWCOUNT values in local
-
-- variables before they are cleared.
-
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
-
-- check if an error occured and if the expected number of records were affected
-
IF @error_var <> 0 or @rowcount_var <> 1
-
BEGIN
-
ROLLBACK
-
-- PRINT "Warning: Error on Insert 2"
-
RETURN(-2)
-
END
-
-
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
-
VALUES (GETDATE(),@iEventID,@MemberNum, 'Tables', @TableBalance, 0, @UserID)
-
if (@BonusBalance>0)
-
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
-
VALUES (GETDATE(),@iEventID,@MemberNum, 'Bonus', @BonusBalance, 0, @UserID)
-
-
-- Save the @@ERROR and @@ROWCOUNT values in local
-
-- variables before they are cleared.
-
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
-
-- check if an error occured and if the expected number of records were affected
-
IF @error_var <> 0 or @rowcount_var <> 1
-
BEGIN
-
ROLLBACK
-
-- PRINT "Warning: Error on Insert 3"
-
RETURN(-3)
-
END
-
-
-
COMMIT TRANSACTION
-
SET @Result = @BatchSize
-
RETURN @Result
-
GO
-
-
-- The function will return your batchSize (that was passed to it, or a Negative number if an error occured
-
-
3 2525
After each insert you got IF clause:
if (@SlotsBalance>0)
if (@TableBalance>0)
etc...
I couldn't get the idea behind these?
After each insert you got IF clause:
if (@SlotsBalance>0)
if (@TableBalance>0)
etc...
I couldn't get the idea behind these?
Sorry Guys, These lines should have been before the insert & I have no idea why they are now appearing after the insert. The insert is dependant on that value being non zero.
Therefore the code should read -
ALTER PROCEDURE spIssueScannedTickets
-
-
@iEventID int,
-
-
@MemberNum nvarchar(12),
-
-
@BatchSize int,
-
-
@FirstNumber nvarchar(12),
-
-
@LastNumber nvarchar(12),
-
-
@SlotsBalance int,
-
-
@TableBalance int,
-
-
@BonusBalance int,
-
-
@UserID int
-
-
AS
-
-
DECLARE @Result int
-
-
-
-
-- Declare variables used in error checking.
-
-
DECLARE @error_var int, @rowcount_var int
-
-
-
-
-
-
SET @Result = 0
-
-
BEGIN TRANSACTION
-
INSERT INTO BundlesIssued (MemberNumber, EventID, BundleSize, FirstNumber, LastNumber, DateIssued, UserID, Invalid)
-
-
VALUES (@MemberNum, @iEventID, @BatchSize, @FirstNumber, @LastNumber, GETDATE(), @UserID , 0x00)
-
-
-
-
-- Save the @@ERROR and @@ROWCOUNT values in local
-
-
-- variables before they are cleared.
-
-
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
-
-
-- check if an error occured and if the expected number of records were affected
-
-
IF @error_var <> 0 or @rowcount_var <> 1
-
-
BEGIN
-
-
ROLLBACK
-
-
-- PRINT "Warning: Error on Insert 1"
-
-
RETURN(-1)
-
-
END
-
-
if (@SlotsBalance>0)
-
-
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
-
VALUES (GETDATE(),@iEventID,@MemberNum, 'Slots', @SlotsBalance, 0, @UserID)
-
-
-
-
-- Save the @@ERROR and @@ROWCOUNT values in local
-
-
-- variables before they are cleared.
-
-
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
-
-
-- check if an error occured and if the expected number of records were affected
-
-
IF @error_var <> 0 or @rowcount_var <> 1
-
-
BEGIN
-
-
ROLLBACK
-
-
-- PRINT "Warning: Error on Insert 2"
-
-
RETURN(-2)
-
-
END
-
-
if (@TableBalance>0)
-
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
-
VALUES (GETDATE(),@iEventID,@MemberNum, 'Tables', @TableBalance, 0, @UserID)
-
-
if (@BonusBalance>0)
-
-
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
-
VALUES (GETDATE(),@iEventID,@MemberNum, 'Bonus', @BonusBalance, 0, @UserID)
-
-
-
-
-- Save the @@ERROR and @@ROWCOUNT values in local
-
-
-- variables before they are cleared.
-
-
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
-
-
-- check if an error occured and if the expected number of records were affected
-
-
IF @error_var <> 0 or @rowcount_var <> 1
-
-
BEGIN
-
-
ROLLBACK
-
-
-- PRINT "Warning: Error on Insert 3"
-
-
RETURN(-3)
-
-
END
-
-
-
-
-
-
COMMIT TRANSACTION
-
-
SET @Result = @BatchSize
-
-
RETURN @Result
-
GO
-
-
-
-- The function will return your batchSize (that was passed to it, or a Negative number if an error occured
-
-
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: deprins |
last post by:
Hello,
I have wrote a stored procedure but its real slow. Its activated by a
button on web page but its takes to long to process and the web server
gives a timeout message after 5 minutes.
Is...
|
by: serge |
last post by:
I have an SP that is big, huge, 700-800 lines.
I am not an expert but I need to figure out every possible way that
I can improve the performance speed of this SP.
In the next couple of weeks I...
|
by: marc |
last post by:
I've been developing a stored procedure that uses a user defined
function in the query portion of the procedure. However, since the end
product needs to allow for dynamic table names, the UDF will...
|
by: Eli |
last post by:
Hi all
We currently have a strange problem with calling a Stored Procedure
(SQL Database) in our C# Project. The only error I get is "System
error" which says a lot :)
Background:
We have...
|
by: joun |
last post by:
Hi all, i'm using this code to insert records into an Access table from
asp.net, using a
stored procedure, called qry_InsertData:
PARAMETERS Long, Long, Text(20), Long,
DateTime;
INSERT...
|
by: Peter D.C. |
last post by:
Hi
I want to update data hold in several textbox controls on an asp.net form.
But it seems like it is the old textbox values that is "re-updates" through a
stored procedure who updates a SQL...
|
by: syntego |
last post by:
We commonly use triggers to log changes to our main tables to
historical log tables.
In the trigger, we create a concatenated string of the old values by
casting them as follows:
...
|
by: svgeorge |
last post by:
I want to update several tables using one stored procedure.
How can i do this I mean the syntax.etc. declaration etc.
I know the basic syntax as below
CREATE PROCEDURE <Procedure_Name, sysname,...
|
by: Bogdan |
last post by:
Hi,
I have a stored procedure that uses JOINs to return columns from multiple
tables. I also have another stored proc that that takes a series of params
and updates multiple tables. I used the...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |