• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

can someone show me how to create calling the 3 sprocs within a transaction ...

i have a sproc below, a cursor loop through each record and call 3 store procedures, each will return 0 if it has no error, else it return a negative number.  My question is can someone show me how to create calling the 3 sprocs within a transaction , if any of them return a negative number, i want to roll back and go to the next record, also, insert the CustomerName into a log table

PROCEDURE example1
AS
BEGIN
      DECLARE cur_OracleToOnyx CURSOR
      FOR
      SELECT CustomerName,ShippingAddress,ProductName FROM temp_table
      
      DECLARE @CustomerName varchar(255)
      DECLARE @ShippingAddress varchar(255)
      DECLARE @ProductName varchar(255)
      
      DECLARE @_ReturnCode int
      
      OPEN cur_example1
      FETCH NEXT FROM cur_example1
      INTO CustomerName,
      ShippingAddress,
      ProductName
      
      WHILE @@FETCH_STATUS = 0
            BEGIN
                  SELECT @_ReturnCode = 0 -- reset to zero
                  
                  EXEC @_ReturnCode = sp_insertCustomer @CustomerName -- if success, it will return 0 or it will return a negative number
                  EXEC @_ReturnCode = sp_insertShippingAddress @ShippingAddress -- if success, it will return 0 or it will return a negative number
                  EXEC @_ReturnCode = sp_insertProductName @ProductName -- if success, it will return 0 or it will return a negative number
                  
            END

FETCH NEXT FROM cur_example1
INTO CustomerName,
      ShippingAddress,
      ProductName
END

CLOSE cur_example1
DEALLOCATE cur_example1
                  
0
musclejack
Asked:
musclejack
1 Solution
 
musclejackAuthor Commented:
show me how to call the 3 sprocs within a transaction , if any of them return a negative number, i want to roll back the transaction, insert the CustomerName into a log table, skip this record and go to the next record, also,
0
 
musclejackAuthor Commented:
show me how to call the 3 sprocs within a transaction , if any of them return a negative number, i want to roll back the transaction, insert the CustomerName into a log table, skip this record and go to the next record.

0
 
stevetheskiCommented:
Thats simple but why would i give you the answer if you arent going to give me points?

Steve
0
 
beeelCommented:
Declare 3 variables
@_ReturnCode1, @_ReturnCode2, @_ReturnCode3 and set them all to zeros.


BEGIN TRANSACTION InProc

EXEC @_ReturnCode1 = sp_insertCustomer @CustomerName -- if success, it will return 0 or it will return a negative number
EXEC @_ReturnCode2 = sp_insertShippingAddress @ShippingAddress -- if success, it will return 0 or it will return a negative number
EXEC @_ReturnCode3 = sp_insertProductName @ProductName -- if success, it will return 0 or it will return a negative number

If @_ReturnCode1 < 0 OR @_ReturnCode2 < 0 OR @_ReturnCode < 0
ROLLBACK TRANSACTION InProc
ELSE
COMMIT TRANSACTION InProc

I thinks that should do

0
 
oceanyangCommented:
first:you DECLARE a "cur_OracleToOnyx" CURSOR,But you open a "cur_example1" CURSOR
second:
...............
     WHILE @@FETCH_STATUS = 0
          BEGIN
               SELECT @_ReturnCode = 0 -- reset to zero
               
      EXEC @_ReturnCode = sp_insertCustomer @CustomerName -- if success, it will return 0 or it will return a negative number

      if (@_ReturnCode<>0)
            rollback
            insert into log file
               
      EXEC @_ReturnCode = sp_insertShippingAddress @ShippingAddress -- if success, it will return 0 or it will return a negative number

      if (@_ReturnCode<>0)
            rollback
            insert into log file
             
      EXEC @_ReturnCode = sp_insertProductName @ProductName -- if success, it will return 0 or it will return a negative number
               
      if (@_ReturnCode<>0)
            rollback
            insert into log file

          END

FETCH NEXT FROM cur_example1
INTO CustomerName,
     ShippingAddress,
     ProductName

...............

sorry,I did not test,you can do it yourself.Good luck:)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now