Re^2: Recoding a multi-sql-statement storedProc transaction in a script

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]

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.

Comment on Re^2: Recoding a multi-sql-statement storedProc transaction in a script Download Code

Replies are listed 'Best First'.
Re^3: Recoding a multi-sql-statement storedProc transaction in a script by VSarkiss (Monsignor) on Dec 15, 2004 at 01:57 UTC
Patient: Doctor, it hurts when I do this. Doctor: Don't do that. I guess I just don't get it. You had working SQL that did what you wanted in a stored proc that you called from your program. Now you want to take it apart, and reconstitute the pieces into your program, and you're finding it painful. So... Is there any reason why you're re-writing it, other than to prove that you don't like pain? Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply]
Re^4: Recoding a multi-sql-statement storedProc transaction in a script by punkish (Priest) on Dec 15, 2004 at 02:00 UTC
So... Is there any reason why you're re-writing it Because, in the earlier version I had access to the db to add my storedproc to it. Now, in this new instance, there is a possibility that I may not be able to muck around with the db, so I just want to be prepared with a working version of my SQLs in my script.	[reply]