473,322 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Stored Procedure to reliably update two tables.

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.
Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE spIssueScannedTickets
  2.         @iEventID int,
  3.         @MemberNum nvarchar(12),
  4.         @BatchSize int,
  5.         @FirstNumber nvarchar(12),
  6.         @LastNumber nvarchar(12),
  7.         @SlotsBalance int,
  8.         @TableBalance int,
  9.         @BonusBalance int,
  10.         @UserID int
  11. AS
  12. DECLARE @Result int
  13.  
  14. -- Declare variables used in error checking.
  15. DECLARE @error_var int, @rowcount_var int
  16.  
  17.  
  18. SET @Result = 0
  19. BEGIN TRANSACTION
  20. INSERT INTO BundlesIssued (MemberNumber, EventID, BundleSize, FirstNumber, LastNumber, DateIssued, UserID, Invalid)
  21.         VALUES (@MemberNum, @iEventID, @BatchSize, @FirstNumber, @LastNumber, GETDATE(), @UserID , 0x00)
  22. if  (@SlotsBalance>0)
  23.  
  24. -- Save the @@ERROR and @@ROWCOUNT values in local
  25. -- variables before they are cleared.
  26. SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
  27. -- check if an error occured and if the expected number of records were affected
  28. IF @error_var <> 0 or @rowcount_var <> 1
  29. BEGIN
  30.    ROLLBACK
  31. --   PRINT "Warning: Error on Insert 1"
  32.    RETURN(-1)
  33. END
  34.  
  35. INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
  36.          VALUES (GETDATE(),@iEventID,@MemberNum, 'Slots', @SlotsBalance, 0, @UserID)
  37. if  (@TableBalance>0)
  38.  
  39. -- Save the @@ERROR and @@ROWCOUNT values in local
  40. -- variables before they are cleared.
  41. SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
  42. -- check if an error occured and if the expected number of records were affected
  43. IF @error_var <> 0 or @rowcount_var <> 1
  44. BEGIN
  45.    ROLLBACK
  46. --   PRINT "Warning: Error on Insert 2"
  47.    RETURN(-2)
  48. END
  49.  
  50. INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
  51.          VALUES (GETDATE(),@iEventID,@MemberNum, 'Tables', @TableBalance, 0, @UserID)
  52. if  (@BonusBalance>0)
  53. INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
  54.          VALUES (GETDATE(),@iEventID,@MemberNum, 'Bonus', @BonusBalance, 0, @UserID)
  55.  
  56. -- Save the @@ERROR and @@ROWCOUNT values in local
  57. -- variables before they are cleared.
  58. SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
  59. -- check if an error occured and if the expected number of records were affected
  60. IF @error_var <> 0 or @rowcount_var <> 1
  61. BEGIN
  62.    ROLLBACK
  63. --   PRINT "Warning: Error on Insert 3"
  64.    RETURN(-3)
  65. END
  66.  
  67.  
  68. COMMIT TRANSACTION
  69. SET @Result = @BatchSize
  70. RETURN @Result
  71. GO
  72.  
  73. -- The function will return your batchSize (that was passed to it, or a Negative number if an error occured
  74.  
  75.  
Jul 24 '08 #1
3 2525
ck9663
2,878 Expert 2GB
One word: TRIGGERS

-- CK
Jul 24 '08 #2
After each insert you got IF clause:

if (@SlotsBalance>0)
if (@TableBalance>0)

etc...

I couldn't get the idea behind these?
Jul 24 '08 #3
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
Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE spIssueScannedTickets
  2.  
  3.     @iEventID int,
  4.  
  5.     @MemberNum nvarchar(12),
  6.  
  7.     @BatchSize int,
  8.  
  9.     @FirstNumber nvarchar(12),
  10.  
  11.     @LastNumber nvarchar(12),
  12.  
  13.     @SlotsBalance int,
  14.  
  15.     @TableBalance int,
  16.  
  17.     @BonusBalance int,
  18.  
  19.     @UserID    int
  20.  
  21. AS
  22.  
  23. DECLARE @Result int
  24.  
  25.  
  26.  
  27. -- Declare variables used in error checking.
  28.  
  29. DECLARE @error_var int, @rowcount_var int
  30.  
  31.  
  32.  
  33.  
  34.  
  35. SET @Result = 0
  36.  
  37. BEGIN TRANSACTION
  38. INSERT INTO BundlesIssued (MemberNumber, EventID, BundleSize, FirstNumber, LastNumber, DateIssued, UserID, Invalid)
  39.  
  40.     VALUES (@MemberNum, @iEventID, @BatchSize, @FirstNumber, @LastNumber, GETDATE(), @UserID , 0x00)
  41.  
  42.  
  43.  
  44. -- Save the @@ERROR and @@ROWCOUNT values in local 
  45.  
  46. -- variables before they are cleared.
  47.  
  48. SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
  49.  
  50. -- check if an error occured and if the expected number of records were affected
  51.  
  52. IF @error_var <> 0 or @rowcount_var <> 1 
  53.  
  54. BEGIN
  55.  
  56.    ROLLBACK
  57.  
  58. --   PRINT "Warning: Error on Insert 1"
  59.  
  60.    RETURN(-1)
  61.  
  62. END
  63.  
  64. if  (@SlotsBalance>0)
  65.  
  66. INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
  67.      VALUES (GETDATE(),@iEventID,@MemberNum, 'Slots', @SlotsBalance, 0, @UserID)
  68.  
  69.  
  70.  
  71. -- Save the @@ERROR and @@ROWCOUNT values in local 
  72.  
  73. -- variables before they are cleared.
  74.  
  75. SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
  76.  
  77. -- check if an error occured and if the expected number of records were affected
  78.  
  79. IF @error_var <> 0 or @rowcount_var <> 1 
  80.  
  81. BEGIN
  82.  
  83.    ROLLBACK
  84.  
  85. --   PRINT "Warning: Error on Insert 2"
  86.  
  87.    RETURN(-2)
  88.  
  89. END
  90.  
  91. if  (@TableBalance>0)
  92. INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
  93.      VALUES (GETDATE(),@iEventID,@MemberNum, 'Tables', @TableBalance, 0, @UserID)
  94.  
  95. if  (@BonusBalance>0)
  96.  
  97. INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
  98.      VALUES (GETDATE(),@iEventID,@MemberNum, 'Bonus', @BonusBalance, 0, @UserID)
  99.  
  100.  
  101.  
  102. -- Save the @@ERROR and @@ROWCOUNT values in local 
  103.  
  104. -- variables before they are cleared.
  105.  
  106. SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
  107.  
  108. -- check if an error occured and if the expected number of records were affected
  109.  
  110. IF @error_var <> 0 or @rowcount_var <> 1 
  111.  
  112. BEGIN
  113.  
  114.    ROLLBACK
  115.  
  116. --   PRINT "Warning: Error on Insert 3"
  117.  
  118.    RETURN(-3)
  119.  
  120. END
  121.  
  122.  
  123.  
  124.  
  125.  
  126. COMMIT TRANSACTION
  127.  
  128. SET @Result = @BatchSize
  129.  
  130. RETURN @Result
  131. GO    
  132.  
  133.  
  134. -- The function will return your batchSize (that was passed to it, or a Negative number if an error occured
  135.  
  136.  
  137.  
Jul 24 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

4
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...
12
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...
4
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...
2
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...
9
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...
7
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...
2
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: ...
0
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,...
5
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
0
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...
1
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)...
1
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...
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.