organising SQL Batch statements

Question

I have a tsql code ,which is basically bunch of insert and update statements wrapped into a Stored procedures and execute it as SP,but if one insert fails then the whole SP Fails, so is there a better way to continue executing rest of code if one insert fails.

Code is something like this:

Create SP As 
{
insert into Table A 
--
--

insert into Table B
--
--

Insert into Table C
--
}

Answer 1

Maybe you can enclose every insert instruction inside a TRY CATCH block

DECLARE @Errors TABLE(
Instruction VARCHAR(MAX),
Error VARCHAR(MAX)
)

BEGIN TRY
INSERT INTO TABLE A
END TRY
BEGIN CATCH
INSERT INTO @Errors VALUES ('TABLE A', ERROR_MESSAGE())
END CATCH

BEGIN TRY
INSERT INTO TABLE B
END TRY
BEGIN CATCH
INSERT INTO @Errors VALUES ('TABLE B', ERROR_MESSAGE())
END CATCH

--....

SELECT * FROM @Errors

Hope this helps.