I have a storedProc transaction in a SQL Server db that I am exec-ing via DBD::ODBC. All cool.

Now I want to do it all in my script. I rewrote the multiple sql statements in my script, ran it, got an error that prepare failed, scratched my head, Google-d, and discovered the following --

Turn Autocommit off, RaiseError on, create a separate statement handle for each statement, wrap it all in eval, run it, check for errors, commit if everything is copacetic.

eval {
  $sth_1->execute;
  $sth_2->execute;
  $sth_3->execute;
};
if ($@) 
  $dbh->rollback; 
} else { 
  $dbh->commit; 
}

[download]

What a pain! I have almost 40 statements that make up the transaction. So, my question -- is there another way or is the above the only way? (and yes, I know I can use a storedProc -- I already was -- I just want to be able to do this in the script as well).

Many thanks for any and all advice.
Update: I have added the following text to make this OP more "intelligent" (sheepish smile follows). This text is the same as my subsequent post below --

I should have elaborated more in my OP. The pain was simply having to rewrite the entire script as separate statements. Some of the statements update or grab values that are then used in subsequent statements. Sample SQL code below --- 

ALTER PROCEDURE sp @foo VARCHAR(250), @bar VARCHAR(250) AS 

BEGIN TRAN

DECLARE @baz NUMERIC, @quux NUMERIC

SELECT @baz = col FROM table1 WHERE condition
SELECT @quux = col FROM table2 WHERE condition = @baz

INSERT INTO table3 (col1, col2, col3) 
VALUES (@foo, @bar, @quux)

UPDATE someother nonsense

COMMIT TRAN

[download]
update: This goes on for 40 or 50 statements.

I spent a good while getting all my SQL ducks in order getting it so it worked properly. Now, I have to do this in the Perl script, so I have to now write these statements separately, run them one by one, grab values from them to run subsequent statements, check for errors, commit or rollback. I was hoping to just run the entire block of multiple statements as a single script. Hence, the pain.

In reply to Recoding a multi-sql-statement storedProc transaction in a script by punkish

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.