Track errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Track errors

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-07-30 : 05:20:09

Hi,

Can someone please help me with the following?

I want to add a table to my database called ErrorsLog.
I have another table Candidate where firstname,lastname and some other fields are mandatory. If an attempt is made to insert a user without the required fields I want to insert the details such as: Date when attempt was made to insert; name of user into the ErrorsLog table and obviously the insert into the Candidate table will fail.

My current sp that inserts into the Candidate table is as follows but i want to modify it and if there is an error insert some details into the ErrorsLog table.

Create    procedure     [dbo].[insCand]
@Title int,
@SurName nvarchar(50) not null,
@Given_Names nvarchar(50) not null,
@Address1 nvarchar(100) not null,
@city nvarchar(50), 
@Post_Code nvarchar(50) not null, 
@Country_id int not null,
@State_Code nvarchar(20),
@State_Zone nvarchar(20),
@Tel_Home  nchar(35), 
@Age_Group int,
@Years_of_experience int, 
@Qualifications int,
@Advertising_Code nvarchar(20),
@Email nvarchar (50) not null,
@Business_Tel nchar (24)  ,
@Tel_Mobile nchar (24) ,
@Fax nchar (24)  ,
@Address2 nvarchar (50) ,
@Business_Name nvarchar (50) ,
@Notes text  ,
@Vat_No nvarchar (50),
@User_Id int,
@ERM int

as

INSERT INTO Candidate (Candidate_id,Title, SurName, Given_Names, 
            Address1, city, Post_Code, Country_id, State_code,State_Zone,
	    Tel_Home,Age_Group, Years_of_experience,
            Qualifications,Advertising_Code,
	    Email,Business_Tel,Tel_Mobile,Fax,Address2,Business_Name,
            Notes,Vat_No, status , Sub_status,
	    Date_Status,Date_Sub_status,SourceSystem,
	    Date_Updated,User_Id_Upd, Date_Entered,User_Id_Ent,ERM) 
           select (IDENT_CURRENT('candidate')),@Title, @SurName,
	    @Given_Names, @Address1,@city, @Post_Code, @Country_id,@State_Code,
        case when (@State_Zone= '') then null else  @State_Zone end,
        @Tel_Home, @Age_Group,
	    @Years_of_experience, @Qualifications,@Advertising_Code,
	    @Email,@Business_Tel,@Tel_Mobile,@Fax,@Address2,@Business_Name,
 	    @Notes,@Vat_No,0,0, NULL,NULL,1,
	    NULL,0, convert(datetime,getdate(),103),@User_Id,@ERM

Structure of Candidate table:

CREATE TABLE [dbo].[Candidate](
[Candidate_id] [int] NOT NULL,
[Internal_candidate_id] [int] IDENTITY(1,1) NOT NULL,
[Title] [int] NULL CONSTRAINT [DF_Candidate_Title] DEFAULT ((0)),
[Given_Names] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[SurName] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[sex] [nchar](1) COLLATE Hebrew_CI_AS NULL,
[Date_of_Birth] [datetime] NULL,
[Age_Group] [int] NULL CONSTRAINT [DF_Candidate_Age_Group] DEFAULT ((0)),
[Email] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Tel_Type] [int] NULL CONSTRAINT [DF_Candidate_Tel_Type] DEFAULT ((0)),
[Business_Tel] [nchar](35) COLLATE Hebrew_CI_AS NULL,
[Tel_Home] [nchar](35) COLLATE Hebrew_CI_AS NULL,
[Tel_Mobile] [nchar](35) COLLATE Hebrew_CI_AS NULL,
[Fax] [nchar](35) COLLATE Hebrew_CI_AS NULL,
[Address1] [nvarchar](100) COLLATE Hebrew_CI_AS NULL,
[Address2] [nvarchar](100) COLLATE Hebrew_CI_AS NULL,
[Country_id] [int] NULL CONSTRAINT [DF_Candidate_Country_id] DEFAULT ((0)),
[State_Code] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[State_Zone] [nvarchar](20) COLLATE Hebrew_CI_AS NULL CONSTRAINT [DF_Candidate_State_code] DEFAULT (NULL),
[city] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Post_Code] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Business_Name] [nvarchar](100) COLLATE Hebrew_CI_AS NULL,
[Years_of_experience] [int] NULL CONSTRAINT [DF_Candidate_Years_of_experience] DEFAULT ((0)),
[Qualifications] [int] NULL CONSTRAINT [DF_Candidate_Qualifications] DEFAULT ((0)),
[Status] [int] NULL CONSTRAINT [DF_Candidate_Status] DEFAULT ((0)),
[Sub_status] [int] NULL CONSTRAINT [DF_Candidate_Sub_status] DEFAULT ((0)),
[Action_Status_id] [int] NULL CONSTRAINT [DF_Candidate_Action_Status_id] DEFAULT ((0)),
[Date_Status] [datetime] NULL,
[Date_Sub_status] [datetime] NULL,
[Date_action_status] [datetime] NULL,
[Notes] [text] COLLATE Hebrew_CI_AS NULL,
[Candidate_Withdrawn] [bit] NULL CONSTRAINT [DF_Candidate_Candidate_Withdrawn] DEFAULT ((0)),
[Advertising_Code] [nvarchar](20) COLLATE Hebrew_CI_AS NULL,
[Date_Invited] [datetime] NULL,
[IBT] [bit] NULL CONSTRAINT [DF_Candidate_IBT] DEFAULT ((0)),
[Vat_No] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Payment_MethodID] [int] NULL CONSTRAINT [DF_Candidate_Payment_MethodID] DEFAULT ((0)),
[Subscruption_Amount] [smallmoney] NULL,
[STOSetUpDate] [datetime] NULL,
[Payment_Frequency] [int] NULL CONSTRAINT [DF_Candidate_Payment_Frequency] DEFAULT ((0)),
[Currency_for_payment] [nvarchar](10) COLLATE Hebrew_CI_AS NULL,
[Bank_name] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Bank_Address] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Bank_Address2] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Bank_City] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Bank_State] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Bank_Postal_Code] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Sort_Code] [nvarchar](100) COLLATE Hebrew_CI_AS NULL,
[Account_Name] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Account_No] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[STO_Reference] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Creadit_Card_No] [bigint] NULL CONSTRAINT [DF_Candidate_Creadit_Card_No] DEFAULT ((0)),
[Swift_No] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[BIC_No] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[IBAN_No] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Credit_Card_Owner_Name] [nvarchar](50) COLLATE Hebrew_CI_AS NULL,
[Valid_Through] [datetime] NULL,
[Owner_Id] [int] NULL,
[CDLSetUpDate] [datetime] NULL,
[SubsReminder1_Date] [datetime] NULL,
[SubsReminder2_Date] [datetime] NULL,
[SubsChaser_Date] [datetime] NULL,
[SubsLastChance_Date] [datetime] NULL,
[Accrediation_Active] [bit] NULL CONSTRAINT [DF_Candidate_Accrediation_Active] DEFAULT ((0)),
[Expiration_Date] [datetime] NULL,
[ListExceptionFlag] [bit] NULL CONSTRAINT [DF_Candidate_ListExceptionFlag] DEFAULT ((0)),
[FullInvoiceRequired] [bit] NULL CONSTRAINT [DF_Candidate_FullInvoiceRequired] DEFAULT ((0)),
[Internal_Region_id] [int] NULL CONSTRAINT [DF_Candidate_Internal_Region_id] DEFAULT ((0)),
[Mentor_Id] [int] NULL CONSTRAINT [DF_Candidate_Mentor_Id] DEFAULT ((0)),
[Assigned_On_Date] [datetime] NULL,
[Proforma_Invoice_Date_reminder] [datetime] NULL,
[SourceSystem] [int] NULL CONSTRAINT [DF_Candidate_SourceSystem] DEFAULT ((0)),
[ERM] [int] NULL CONSTRAINT [DF_Candidate_ERM] DEFAULT ((0)),
[ERM_email_alloc_date] [datetime] NULL,
[DLM_SentDate] [datetime] NULL,
[Date_Entered] [datetime] NULL CONSTRAINT [DF_Candidate_Date_Entered] DEFAULT ((0)),
[User_Id_Ent] [int] NULL CONSTRAINT [DF_Candidate_User_Id_Ent] DEFAULT ((0)),
[Date_Updated] [datetime] NULL,
[User_Id_Upd] [int] NULL CONSTRAINT [DF_Candidate_User_Id_Upd] DEFAULT ((0)),
CONSTRAINT [PK_Candidate] PRIMARY KEY CLUSTERED
(
[Candidate_id] ASC
)

Thanks :-)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 05:42:57

add the insert into a try... catch block and if an error happens
insert the error into the error table in the catch block

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 05:45:40

IDENT_CURRENT('candidate') happens AFTER you have inserted the record.

DECLARE	@CandidateID INT

INSERT	Candidate
	(
		Title,
		SurName,
		Given_Names,
		Address1,
		city,
		Post_Code,
		Country_id,
		State_code,
		State_Zone,
		Tel_Home,
		Age_Group,
		Years_of_experience,
		Qualifications,
		Advertising_Code,
		Email,
		Business_Tel,
		Tel_Mobile,
		Fax,
		Address2,
		Business_Name,
		Notes,
		Vat_No,
		status,
		Sub_status,
		Date_Status,
		Date_Sub_status,
		SourceSystem,
		Date_Updated,
		User_Id_Upd,
		Date_Entered,
		User_Id_Ent,
		ERM
	) 
select	@Title,
	@SurName,
	@Given_Names,
	@Address1,
	@city,
	@Post_Code,
	@Country_id,
	@State_Code,
        NULLIF(@State_Zone, ''),
        @Tel_Home,
        @Age_Group,
	@Years_of_experience,
	@Qualifications,
	@Advertising_Code,
	@Email,
	@Business_Tel,
	@Tel_Mobile,
	@Fax,
	@Address2,
	@Business_Name,
	@Notes,
	@Vat_No,
	0,
	0,
	NULL,
	NULL,
	1,
	NULL,
	0,
	DATEADD(DAY, DATEDIFF(DAY, 0, current_timestamp), 0),
	@User_Id,
	@ERM

SET	@CandidateID = SCOPE_IDENTITY()

UPDATE	Candidate
SET	Candidate_ID = @CandidateID
WHERE	Internal_candidate_id = @CandidateID

E 12°55'05.25"
N 56°04'39.16"

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-07-30 : 05:52:56

Peso thanks alot for tidying up the sp :-)
Spirit1 thanks i will use a try catch block.

Thanks guys :-)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources