I recently read your article "Tuning stored procedures: Structured exception handling in SQL Server 2005" and I was trying to implement the concept discussed. However, it seems the control doesn't enter into the catch block. What should I do?

    Requires Free Membership to View

    Login

Try a much simpler example first. This example will fail because we attempt to insert a NULL value into a column that does not allow NULLs:

CREATE TABLE dbo.emp (
empno int NOT NULL)
go
create procedure  dbo.p_emp_insert
(@empno int)
as
begin
 -- CODE THAT MAY FAIL
  begin try

  insert into emp (empno)
            values (@empno)
      end try

 -- EXECUTED IF THERE IS A FAILURE
 begin catch

    SELECT ERROR_NUMBER()as ERROR_NUMBER,
    ERROR_SEVERITY()as ERROR_SEVERITY,
    ERROR_STATE()as ERROR_STATE,
    ERROR_MESSAGE()as ERROR_MESSAGE

 end catch

end

-- First exec should work with a valid empno …
exec dbo.p_emp_insert 1003

-- This should fail because the column does not allow NULLs
-- We should see the error information returned
exec p_emp_insert NULL

This was first published in October 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top