another question about stored procedures!!

[jgoodman00]

In an append query, access tells you the n of n records will be inserted into the table. If an error occurs during the insert procedure, it tells you how many records were & were not inserted successfully. Can this be achieved in SQL Server? If so how would one go about achieving this for the following stored procedure:

ALTER PROCEDURE dbo.GenerateBrambleRecords
AS INSERT INTO dbo.tblInspection
(AgreeCoppice, Area, CommenceCycleYear, Cutdown, Dead, DesignRequired, Exposure, FireRisk, HeightM, KillBramble, LatinName, MaintCycleYears,
PlantGaps, RemoveCutVeg, RemoveUnwantedSpecies, ReplantingRequired, ReseedSpecialMix, Retain, RetainWantedSpecies, Risk, RiskScore, Soil,
Spray, TreatAllStumps, VisitsPerYear, ModUser, ModDate, RefNo, InspectionType, InspectionDate, Surveyor, Budget, Rate, Remarks, WorkStatus)
SELECT dbo.Pnasource___TblAssetBramble.AgreeCoppice, dbo.Pnasource___TblAssetBramble.Area, dbo.Pnasource___TblAssetBramble.CommenceCycleYr,
dbo.Pnasource___TblAssetBramble.Cutdown, dbo.Pnasource___TblAssetBramble.Dead, dbo.Pnasource___TblAssetBramble.DesignRequired,
dbo.Pnasource___TblAssetBramble.Exposure, dbo.Pnasource___TblAssetBramble.FireRisk, dbo.Pnasource___TblAssetBramble.HeightM,
dbo.Pnasource___TblAssetBramble.KillBramble, dbo.Pnasource___TblAssetBramble.LatinName, dbo.Pnasource___TblAssetBramble.MaintCycleYrs,
dbo.Pnasource___TblAssetBramble.PlantGaps, dbo.Pnasource___TblAssetBramble.RemCutVeg,
dbo.Pnasource___TblAssetBramble.RemUnwantedSpec, dbo.Pnasource___TblAssetBramble.ReplantingRequired,
dbo.Pnasource___TblAssetBramble.ReseedSpecialMix, dbo.Pnasource___TblAssetBramble.Retain,
dbo.Pnasource___TblAssetBramble.RetainWantedSpecies, dbo.Pnasource___TblAssetBramble.Risk, dbo.Pnasource___TblAssetBramble.RiskScore,
dbo.Pnasource___TblAssetBramble.Soil, dbo.Pnasource___TblAssetBramble.Spray, dbo.Pnasource___TblAssetBramble.TreatAllStumps,
dbo.Pnasource___TblAssetBramble.VisitsPerYr, dbo.Pnasource___Pnamstr.ModUser, dbo.Pnasource___Pnamstr.ModDate,
dbo.Pnasource___Pnamstr.RefNo, dbo.Pnasource___tblInspection.IDWorkType, dbo.Pnasource___tblInspection.LastInspection,
dbo.Pnasource___tblInspection.Surveyor, dbo.Pnasource___tblRemarks.Budget, dbo.Pnasource___tblRemarks.Rate,
dbo.Pnasource___tblRemarks.Remarks, dbo.Pnasource___tblRemarks.[Work Status]
FROM dbo.Pnasource___Pnamstr INNER JOIN
dbo.Pnasource___TblAssetBramble ON dbo.Pnasource___Pnamstr.MasterID = dbo.Pnasource___TblAssetBramble.MasterID INNER JOIN
dbo.Pnasource___tblInspection ON dbo.Pnasource___Pnamstr.MasterID = dbo.Pnasource___tblInspection.MasterID INNER JOIN
dbo.Pnasource___tblRemarks ON dbo.Pnasource___Pnamstr.MasterID = dbo.Pnasource___tblRemarks.MasterID



Thanks in advance again... James Goodman
j.goodman00@btinternet.com

 

[KRISF]

By default, SQL Server will insert all records or nothing. SQL Server will create an implicit transaction for the procedure and if there is an error on the second to last record - the entire transaction will be rolled back (which can be problematic).

If you want those records that can be inserted to be committed and counts to be returned - use a cursor. (See

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp

for syntax)

Test the @@error value after each record and increment counters appropriately. This will slightly degrade performance, as SQL Server is not designed for record-by-record processing; but use of a 'fast_forward' cursor will minimize that.