CODEWITHSUNDEEP
sql-serverstored-procedurestry-catch
Chanuka Naveen Koswatta
Chanuka Naveen Koswatta

Reputation: 75

Why is the TRY-CATCH statement in the Stored Procedure is not working?

I have created a stored procedure which is used to update a value in a table and enter a record to another table. It should be done as follows.

After updating whether the mark is checked or not in the Marks table, a record should enter in the table UpdatedMarkDetails with the changed MarkCode and the changed value(previous value)
Also, I want to know when a non-existing MarkCode is entered. So I put a TRY-CATCH statement, but it is not working also. I'm also getting a 'Could not be bound' error in the query editor.
The Code is as follows

ALTER PROCEDURE ChangeStatusOfMarks(@GivenMarkCode AS INT, @Value AS NVARCHAR(3))
AS
BEGIN TRY
    BEGIN
        UPDATE dbo.Marks
            SET dbo.Marks.IsChecked = @Value
            WHERE dbo.Marks.MarkCode = @GivenMarkCode
    END
    BEGIN
        INSERT INTO [dbo].[UpdatedMarkDetails]
            ([UpdatedMarkCode],[MarkCode],[ExistedStatus])
            VALUES
            (NEWID(),
            SELECT Marks.MarkCode FROM dbo.Marks,
            SELECT Marks.IsChecked FROM dbo.Marks
            )
    END
END TRY
BEGIN CATCH
    SELECT
        ERROR_LINE() AS 'ERROR LINE',
        ERROR_MESSAGE() AS 'ERROR MESSAGE',
        ERROR_SEVERITY() AS 'ERROR SEVERITY'
END CATCH

I get the error as follows

Msg 156, Level 15, State 1, Procedure ChangeStatusOfMarks, Line 14 [Batch Start Line 0] Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure ChangeStatusOfMarks, Line 15 [Batch Start Line 0] Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure ChangeStatusOfMarks, Line 16 [Batch Start Line 0] Incorrect syntax near ')'.

Upvotes: 0

Views: 818

Answers (1)

Thom A
Thom A

Reputation: 95557

Because you have Syntax error, which means that the SQL to ALTER the SP is failing. It's nothing to do with the TRY...CATCH. Fix your syntax error:

ALTER PROCEDURE ChangeStatusOfMarks (@GivenMarkCode AS int,
                                     @Value AS nvarchar(3))
AS
BEGIN TRY
    BEGIN
        UPDATE dbo.Marks
        SET dbo.Marks.IsChecked = @Value
        WHERE dbo.Marks.MarkCode = @GivenMarkCode;
    END;
    BEGIN
        INSERT INTO [dbo].[UpdatedMarkDetails] ([UpdatedMarkCode],
                                                [MarkCode],
                                                [ExistedStatus])
        VALUES (NEWID(), (SELECT Marks.MarkCode FROM dbo.Marks), (SELECT Marks.IsChecked FROM dbo.Marks));
    END;
END TRY
BEGIN CATCH
    SELECT ERROR_LINE() AS [ERROR LINE],
           ERROR_MESSAGE() AS [ERROR MESSAGE],
           ERROR_SEVERITY() AS [ERROR SEVERITY];
END CATCH;

That assumes that the table dbo.Marks can only ever contain 1 row.

Personally, however, I would write the INSERT as:

        INSERT INTO [dbo].[UpdatedMarkDetails] ([UpdatedMarkCode],
                                                [MarkCode],
                                                [ExistedStatus])
        SELECT NEWID(),
               MarkCode,
               IsChecked
        FROM dbo.Marks;

I'm also not sure why you have the TRY...CATCH here. You don't THROW the error, meaning that if the above fails, the error won't be propagated (unless that is your goal?). nor do you rollback the transaction on failure (or start one). That however, is a different question/answer, but just raising my point in answer that address the question asked.

Upvotes: 3

Related Questions