Link to home
Start Free TrialLog in
Avatar of techques
techques

asked on

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
Avatar of gregoryyoung
gregoryyoung
Flag of Canada image

I am guessing that you are using ado.net see http://msdn.microsoft.com/en-us/library/86773566.aspx

Cheers,

Greg
Avatar of techques
techques

ASKER

I want all of my transaction code within my stored procedure, but not in VC#. How can I do that?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial