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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: