CODEWITHSUNDEEP
sqlsql-servererror-handlingraiserror
batuhan343434
batuhan343434

Reputation: 67

SQL Server @@error not working. Doesn't go into "if"

I wrote a very simple procedure. I am deliberately making a mistake in the procedure. but the error is not working. I want to return the error I want with raiserror. but it doesn't even go inside the "if".

ALTER PROCEDURE dene
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO bddksektor ([tcmbkodu], [aktif], [bddksektorkodu]) 
    VALUES ('a', 1, 1)

    -- I knowingly made a mistake. normally the 1st parameter is int.
    -- I'm entering varchar so that it can enter "if" and return an error. but it doesn't

    IF @@ERROR <> 0
    BEGIN
        RAISERROR('Hata', 16, 1, 61106)
        RETURN 61106
    END
    ELSE
    BEGIN
        SELECT 
            [tcmbkodu], [aktif], [bddksektorkodu]
        FROM 
            [dbfactoringtest].[dbo].[bddksektor]
        ORDER BY
            tcmbkodu ASC
    END

    SET NOCOUNT OFF
END

SQL Server returns its own error when I run the procedure. It does not return the error I wrote because it does not enter the "if"

Error:

Msg 245, Level 16, State 1, Procedure dene, Line 11 [Batch Start Line 1]
Conversion failed when converting the varchar value 'a' to data type int

Upvotes: 1

Views: 273

Answers (1)

Thom A
Thom A

Reputation: 95820

As I mention in the comment, use a TRY...CATCH. For your SQL if the first INSERT fails the batch will be aborted, and so the IF won't be entered, because it won't be reached. I also recommend switching to THROW instead of RAISERROR as noted in the documentation:

Note
The RAISERROR statement does not honor SET XACT_ABORT. New applications should use THROW instead of RAISERROR.

This gives you something like this:

CREATE OR ALTER PROCEDURE dbo.dene AS --Always schema qualify
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        INSERT INTO dbo.bddksektor ([tcmbkodu],[aktif],[bddksektorkodu]) --Always schema qualify
        VALUES ('a',1,1);

        SELECT [tcmbkodu]
              ,[aktif]
              ,[bddksektorkodu]
        FROM [dbfactoringtest].[dbo].[bddksektor] --Why is this using 3 part naming? Is this copy of bddksektor in a different database?
        ORDER BY tcmbkodu ASC;
    END TRY
    BEGIN CATCH
        THROW 61106, N'Hata', 16;
        RETURN 61106;
    END CATCH;

    SET NOCOUNT OFF;
END

Upvotes: 1

Related Questions