EXPERT RESPONSE
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
|
