send batch of sql statements to sql server

Discussion in 'ASP General' started by JT, Sep 27, 2005.

  1. JT

    JT Guest

    can someone point me in the right direction for learning more about how to
    go about sending a batch of sql statements from asp to sql server, and
    having errors appropriately handled?

    for example,

    i want to send the following stored procedure calls to sql server in a
    single batch from my asp code, rather than using a loop in asp to execute
    each procedure individiually. if one of these fails, they all should fail.

    exec myProc @param1 = 'A'
    exec myProc @param1 = 'B'
    exec myProc @param1 = 'C'
    exec myProc @param1 = 'D'
    exec myProc @param1 = 'E'
    exec myProc @param1 = 'F'

    thanks,

    JT
     
    JT, Sep 27, 2005
    #1
    1. Advertising

  2. JT wrote:
    > can someone point me in the right direction for learning more about
    > how to go about sending a batch of sql statements from asp to sql
    > server, and having errors appropriately handled?
    >
    > for example,
    >
    > i want to send the following stored procedure calls to sql server in a
    > single batch from my asp code, rather than using a loop in asp to
    > execute each procedure individiually. if one of these fails, they
    > all should fail.
    >
    > exec myProc @param1 = 'A'
    > exec myProc @param1 = 'B'
    > exec myProc @param1 = 'C'
    > exec myProc @param1 = 'D'
    > exec myProc @param1 = 'E'
    > exec myProc @param1 = 'F'
    >
    > thanks,
    >
    > JT



    Personally, I would encapsulate these in a stored procedure, using a
    transaction:

    CREATE PROCEDURE RunProcs (
    @param1 char(1),
    ....,
    @param6 char(1)) AS

    declare @err int
    BEGIN TRANSACTION
    exec @err=myproc @param1
    if @err <> 0
    begin
    rollback transaction
    return 1
    end
    exec @err=myproc @param2
    if @err <> 0
    begin
    rollback transaction
    return 2
    end
    ....
    exec @err=myproc @param6
    if @err <> 0
    begin
    rollback transaction
    return 6
    end
    commit transaction

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Sep 27, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Neil Zanella
    Replies:
    8
    Views:
    1,195
    mfmehdi
    Oct 20, 2006
  2. Harry George
    Replies:
    6
    Views:
    384
    Bart Nessux
    Feb 23, 2004
  3. David C. Barber
    Replies:
    3
    Views:
    551
  4. Vince
    Replies:
    12
    Views:
    751
    Martin Gregorie
    Jan 21, 2008
  5. John Crichton
    Replies:
    6
    Views:
    267
    John Crichton
    Jul 12, 2010
Loading...

Share This Page