SQL 2005 try/catch block never reaches 'catch' despte bogus data tests.

Question

Hi.

OK, I am not that experienced with SQL 2005 error handling and am learning my way around try/catch statements.

I have written the below procedure but no matter what I pass to it, there is never any data in my ErrorLog table. I have passed all INT values, all datetime values, or data strings that are not in the DB and get '0 rows effected' with nothing reported in ErrorLog. It is as if the CATCH statement is never reached (for what it is worth, I have also tried commenting out the validation at the top).

Any ideas what I am doing wrong? Thanks.

ALTER PROCEDURE [dbo].[aspnet_Membership_UpdateLastActivityDate]   

@UserId nvarchar(256),
@UserName nvarchar(256),
@LastActivityDate datetime,
@ApplicationName nvarchar(256)

AS

DECLARE @Today DATETIME
DECLARE @MSG VARCHAR(255)
DECLARE @Severity INT
DECLARE @ErrorCode INT

BEGIN

SET XACT_ABORT ON   -- (I have also tried it without XACT_ABORT. No difference)

BEGIN TRY

    SET @ErrorCode = 0
    SELECT @Today = GetDate()

    IF (@UserId IS NULL)
    RETURN(1)

    IF (@UserName IS NULL)
    RETURN(1)

    IF (@LastActivityDate IS NULL)
    RETURN(1)

    BEGIN TRAN
        UPDATE dbo.aspnet_Users WITH (ROWLOCK)
        SET LastActivityDate = @LastActivityDate
        FROM dbo.aspnet_Users u
        INNER JOIN dbo.aspnet_Applications a
        ON u.ApplicationId = a.ApplicationId
        WHERE u.UserName = @UserName
        AND u.UserId = @UserId
        AND a.ApplicationName = @ApplicationName
    COMMIT TRAN

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRAN   

    SET @ErrorCode = Error_Number()
    SET @Severity = Error_Severity()
    SET @MSG = 'An error was thrown: '
        + 'Error(' + @ErrorCode + '):' + ERROR_MESSAGE()
        + ' Severity = ' + ERROR_SEVERITY() 
        + ' State = ' + ERROR_STATE()
        + ' Procedure = ' + ERROR_PROCEDURE()
        + ' Line Number = ' + ERROR_LINE()

    INSERT INTO [dbo].[ErrorLog]([errornum], [errortype], [errormsg],[errorsource], [errordate])
    VALUES (@ErrorCode, 'E', @MSG, Error_Procedure(), @Today)

    RAISERROR(@MSG, @Severity, 2)

END CATCH

END

RETURN @ErrorCode

Answer 1

It has been awhile since I've done a lot with SQL Error handling but I don't see any place that is likely to generate an error. Are you expecting the "Return" statements to be "Caught"? That isn't going to happen...they'll just return from the function. You'll need to raise an error, not trigger a Return.

Answer 2

Agreed with @Mark. Try changing this:

IF (@UserId IS NULL)
    RETURN(1)

To this:

IF (@UserId IS NULL)
BEGIN
    RAISERROR('No UserID was passed in.', 11, 1);
    RETURN 1;
END

Also see this article for a fantastic error handling primer by Erland Sommarskog:

http://www.sommarskog.se/error_handling_2005.html