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

How to execute multiple stored procedure in ONE transaction stored procedure?

Hi

I have a VC# program which needs to
1. select data from multiple tables
2. update data for multiple tables
3. insert data for multiple tables

1. How should I write one transaction stored procedure to include all of the select, update and insert queries?
2. How to make sure first come first in? i.e. process the store procedure one by one
3. How to write
IF @@ERROR > 0 OR @@ROWCOUNT <> 1
    BEGIN
        IF @@TRANCOUNT = 1
            ROLLBACK TRANSACTION
        ELSE
            COMMIT TRANSACTION
        RETURN 1  
    END
ELSE
    BEGIN
        COMMIT TRANSACTION
        RETURN 0  
    END

Since there are over 100 queries in one second, how to solve the huge traffic and ensure the concurrency of the data?

I use VC#, MSSQL 2005 server and ASP.NET
0
techques
Asked:
techques
1 Solution
 
gregoryyoungCommented:
I am guessing that you are using ado.net see http://msdn.microsoft.com/en-us/library/86773566.aspx

Cheers,

Greg
0
 
techquesAuthor Commented:
I want all of my transaction code within my stored procedure, but not in VC#. How can I do that?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>1. How should I write one transaction stored procedure to include all of the select, update and insert queries?
a stored procedure can have plenty of sql code inside of it, I don't see the "problem"

>2. How to make sure first come first in? i.e. process the store procedure one by one
t-sql is sequential, so having 2 statements after each other will get them executed one after the other

>3. How to write  ...

let me show you how to do:
CREATE PROCEDURE your_proc
AS
BEGIN
  DECLARE @err INT
  DECLARE @row INT
 
  BEGIN TRANSACTION 
  SELECT ....  
  SET @err = @@ERROR 
  IF @err = 0
  BEGIN
    UPDATE ...
  
    SET @err = @@ERROR, @row = @@ROWCOUNT
    IF @row = 0  SET @err = 1
  END 

  IF @err = 0
  BEGIN
    INSERT ...
  
    SET @err = @@ERROR, @row = @@ROWCOUNT
    IF @row = 0  SET @err = 1
  END
 
  -- etc etc etc  
  IF @err = 0
  BEGIN
    COMMIT
    RETURN 0
  END
  ELSE
  BEGIN
    ROLLBACK   
    RETURN @err
  END
END

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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