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

Reputation: 75

'TRY - CATCH' statements not working as expected inside a stored procedure

This code is working without any error. But I wanted to see the output of a try-catch statement and wrapped the body of the procedure with a try-catch statement and did a mistake purposely. However, the error doesn't display as an output in the grid result set. Still, it comes out in the default way. I need to know whether my usage of this statement is correct. If not, can someone explain to me about the try-catch statements usage.

ALTER PROCEDURE UpdateStudent
    (@UpdateStudentDetails [UDTT_Stud] READONLY)
AS
BEGIN TRY
    BEGIN
        UPDATE dbo.StudentDetails        --Deleted 'A' of UPDATE to give an  error
        SET FirstName = upStud.FirstName,
            LastName = upStud.Lastname,
            HomeAddress = upStud.HomeAddress,
            EmailAddress = upStud.EmailAddress
        FROM
            StudentDetails stud
        INNER JOIN
            @UpdateStudentDetails upStud ON stud.IndexNumber = upStud.IndexNumber
    END
END TRY
BEGIN CATCH
    SELECT
        ERROR_MESSAGE() AS 'Error Message',
        ERROR_STATE() AS 'Error State',
        ERROR_SEVERITY() AS 'Error Severity'
END CATCH

This is the output I get

Msg 102, Level 15, State 1, Procedure UpdateStudent, Line 5 [BatchStart Line 0]
Incorrect syntax near 'UPDATE'

Upvotes: 0

Views: 185

Answers (1)

Marc Guillot
Marc Guillot

Reputation: 6455

As the comments say, you have a syntax error and not a runtime error that could be caught by the try...catch.

You have set an alias for the StudentDetails table, so you should use it instead of UPDATE StudentDetails. I have also removed an unnecessary BEGIN END block inside the BEGIN TRY block, and added the outer BEGIN END for the entire stored procedure.

ALTER PROCEDURE UpdateStudent
    (@UpdateStudentDetails [UDTT_Stud] READONLY)
AS 
BEGIN -- The entire stored procedure should be inside a BEGIN END block
   BEGIN TRY
       UPDATE stud -- We use the alias
       SET FirstName = upStud.FirstName,
           LastName = upStud.Lastname,
           HomeAddress = upStud.HomeAddress,
           EmailAddress = upStud.EmailAddress
       FROM
           StudentDetails stud
       INNER JOIN
           @UpdateStudentDetails upStud ON stud.IndexNumber = upStud.IndexNumber
   END TRY
   BEGIN CATCH
       SELECT
           ERROR_MESSAGE() AS 'Error Message',
           ERROR_STATE() AS 'Error State',
           ERROR_SEVERITY() AS 'Error Severity'
   END CATCH
END

Upvotes: 1

Related Questions