You must Sign In to post a response.
  • Category: SQL Server

    Nested Stored Procedure

    I had One Stored Procedure Main and a Nested Stored Procedure say Main 1 i am calling the Nested Stored procedure in Main Procedure in Main1 Procedure i have some Custom messages that should be displayed in Front end and it will rollback in the Next Statement and in the Catch Exception i am having another Rollback . But i am getting error that No matching Begin Transaction for the Rollback transaction so for the exception i had put the flag or @@transcount so the exception gone but the Custom message which is output parameter i am unable to get in the front end . so any Suggestions

    Main stored Procedure
    begin
    {

    exec Main1


    catch

    rollback

    }

    in Main1

    Main1
    (
    begin

    stat 1

    stat 2

    message = "some message"
    rollback

    catch

    if @flagrolled =0
    begin

    end
    else
    begin
    rollback
    end
    )

    but i want to retrieve the message output parameter not only one message i want to retrieve more than one messages are there
  • #762458
    Hi Srirama,

    I have checked your SQL stored procedure code and noticed there is completely wrong way of implementation with the begin and rollback tran.

    Also, all the syntax of your posted code is not correct. Kindly, post us your exact code so that, we can easily provide a solution to resolve your problem.

    Before implementing a transaction kindly, go through this reference site,

    http://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling

    Refer the sample code which you need to use in proper way,

    CREATE PROCEDURE SP_Main

    AS
    BEGIN
    BEGIN TRAN T1

    BEGIN TRY
    EXEC SP_Main1
    COMMIT TRAN T1
    END TRY
    BEGIN CATCH
    ROLLBACK TRAN T1
    END CATCH
    END
    GO


    Thanks,
    Damu

    Regards,
    V.M. Damodharan
    "Your talent will be worthless, when you have fear and tension."


Sign In to post your comments