Does anyone know if it's possible to nest stored procedures inside a
transaction and have that transaction affect all of the nested stored
procedures? For example, I have this stored procedure ...
CREATE PROCEDURE sp_InsertCD
@strCDTitle varchar(50),
@strBand varchar(50),
@strSong1 varchar(50),
AS
DECLARE
@strReturnValue varchar(50)
BEGIN
BEGIN TRANSACTION
INSERT INTO CDs
(
CDTitle,
Band
)
Values
(
@strCDTitle,
@strBand
)
IF @@ROWCOUNT <> 1
BEGIN
Select @strReturnValue = "Error"
END
ELSE
BEGIN
EXECUTE @strReturnValue = sp_InsertSongs
@@IDENTITY, @strSong1, ""
END
IF @strReturnValue = "Error"
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
If the call to sp_InsertSongs returns an error code can I get the rollback
transaction statement to rollback the sp_InsertSongs stored procedure? It
isn't working as displayed above.
Thanks!
Robyn Follen
Web Applications Developer
PreVision Marketing Inc.
55 Old Bedford Road
Lincoln, MA 01773
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
