Best way to combine Insert, Update and Delete transaction in SQL Server 2008?

Question

I have login process for my application that should run three different transactions if user successfully passed the authentication. Here is example of my current code:

<cftransaction action="begin">  
    <cftry>
        <cfset local.appStruct = structNew()>
        <cfset local.appStruct.AccountID = UserResults.AccountID>
        <cfset local.appStruct.UserName = UserResults.UserName>
        <cfset local.appStruct.Email = UserResults.Email>
        <cfset session.AccountInfo = appStruct>
        <cfset session.LoggedIn = true>

        <cfquery name="trackLogin" datasource="#dsn#">
            DELETE
            FROM FailedLogins
            WHERE LoginUN = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(arguments.username)#" maxlength="50">

            UPDATE Accounts
            SET LockedUntil = NULL
            WHERE UserName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(arguments.username)#" maxlength="50">

            INSERT INTO AccountLogins (
                AccountID,
                Login
            )VALUES(
                <cfqueryparam cfsqltype="cf_sql_idstamp" value="#UserResults.AccountID#">,
                CURRENT_TIMESTAMP
            )
        </cfquery>

        <cfset local.fnResults = {status : "200"}>

        <cfcatch type="any">
            <cftransaction action="rollback" />
                <cfset local.fnResults = {error : cfcatch, status : "400", message : "Error! Please contact your administrator."}>
        </cfcatch>
    </cftry>
</cftransaction>

In the code above first I set some session variables and then I clean up the Failed logins, then update the flag and insert some info in Account login table. First I would like to move this in Store Procedure. Then I'm wondering if there is a good way to combine these three transactions in one or they should be separate Stored procedures? If anyone have advise or good reason why one way or the other please let me know. Thank you.

Answer 1

To prevent errors, in your stored procedure, you can use try catch blocks like that:

BEGIN TRY
     BEGIN TRAN
    'T-SQL code
    COMMIT TRAN
END TRY
BEGIN CATCH
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRAN;

    THROW; -- raise error
END CATCH