SQL事务错误处理

你们认为我处理以下存储过程中错误的方式有什么问题吗?我对SQL仍然没有足够的信心来意识到我是否做错了什么。谢谢:)

CREATE PROCEDURE [dbo].[UserAccounts_Create]
    @username varchar(255),
    @email varchar(255),
    @password nvarchar(127),
    @id bigint OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @now smalldatetime
    SET @now = GETDATE()

    DECLARE @userId bigint
    DECLARE @transactionSuccess bit
    SET @transactionSuccess = 0

    BEGIN TRANSACTION       
        INSERT INTO UserAccounts([username], [email], [password], [createDate], [lastLoginDate])
            VALUES (@username, @email, @password, @now, @now)

        SET @userId = SCOPE_IDENTITY()

        INSERT INTO Users([id], [firstName], [lastName])
            VALUES (@userId, '', '')

        SET @transactionSuccess = 1;

    COMMIT TRANSACTION

    IF (@transactionSuccess = 1)
        RETURN 0; -- Success.
    ELSE
        RETURN 1; -- Some unknown error occured.
END