@@error Function

[sleezy]

How do l write the records that Failed into an audit table so l can address them
after the load


--
CREATE TABLE Test (
Name1 char (7) NOT NULL ,
Number Int NULL
)
GO


IF Object_Id('prcAdd_StudentNames') is not null
Drop proc prcAdd_StudentNames
Go

Set NoCount On
Go


Create Procedure prcAdd_StudentNames
AS

-- Execute the INSERT statement.
Truncate Table Test

INSERT INTO Test (Name1,Number)
Values ('Ray',1)

INSERT INTO Test (Name1,Number)
Values ('John','2')

INSERT INTO Test (Name1,Number)
Values (Null,4)

-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT 'An error occurred loading the new Student information'
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT 'The new Student information has been loaded'
RETURN(0)
END
GO

--Select * From test

[skhanal]

You have to check @@error value after each insert statement then insert into a separate table.

[sleezy]

IF Object_Id('prcAdd_StudentNames') is not null
Drop proc prcAdd_StudentNames
Go

Set NoCount On
Go

Create Procedure prcAdd_StudentNames
AS
-- Execute the INSERT statement.
Truncate Table Test

INSERT INTO Test (Name1,Number)
Values ('Ray','')

-- If @@error > 0
-- Begin
-- Print 'Not possible'
-- end

INSERT INTO Test (Name1,Number)
Values ('John','2')

INSERT INTO Test (Name1,Number)
Values (Null,4)

-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT 'An error occurred loading the new Student information'
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT 'The new Student information has been loaded'
RETURN(0)
END


when l run the procedure l get the following

Server: Msg 515, Level 16, State 2, Procedure prcAdd_StudentNames, Line 17
Cannot insert the value NULL into column 'Name1', table 'Test.dbo.test'; column does not allow nulls. INSERT fails.
The statement has been terminated.
An error occurred loading the new Student information


What l want to do is to insert into an audit table all the records that
fail. So it should print

'There were 1200 Records Inserted date. Job ran for 10mins ..or 1hr etc'
'There were 12 Records Rejected'


Insert into Audit

[skhanal]

If you use a single INSERT statement to insert all rows then either all of them fail or all succeed.

To audit the number of rows inserted or rejected you have to write a loop to insert row by row and keep a count of records inserted or rejected. When you come out of the loop populate the audit table. Make sure that the insert into audit table is outside the transaction boundary of your insert into test tables to prevent audit records from rolling back in case you rollback for a failure.