April 5, 2017 at 12:55 pm
I have a requiremnt where i am trying to modify this procedure
1.Data Validation - if rowcount = 0 then i have to keep all rec in exception table for example - SET @NoOfRowssUpdated = @@ROWCOUNT; if the @NoOfRowssUpdated count is 0 then i have to insert into exception - Example - if i have 50 records from dbo.Student table;Out of 50 ; 40 are matched and 10 are not matched. i need only these 10 records that to keep in excpetion table
2. Techincal Validation - In the Catch block - If some thing fails i have to keep all the records from dbo.Student and keep in Exception table.If error occurs nothing has to update evrything should rollback..
Exception table schema
EMployeeCd
EMpNum
Createddate
How to modify the belwo code with all transactions?
ALTER PROCEDURE [dbo].[UpdateEmployees]
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorRows INT = 0,
@NoOfRowssUpdated INT = 0
BEGIN TRY
UPDATE EM
SET EM.EMployeeCd = ST.EMployeeCd,
EM.EmpNum = ST.EMpNum
FROM dbo.Employee EM
INNER JOIN dbo.Student ST
ON EM.ID = ST.ID
SET @NoOfRowssUpdated = @@ROWCOUNT;
END TRY
BEGIN CATCH
--Insert into Exception---
SET @ErrorRows = @@ROWCOUNT;
END CATCH
END
April 5, 2017 at 1:24 pm
1) Please provide create table statements, INSERTs for sample data, and expected outcomes that cover all logic paths.
2) Review OUTPUT in Books Online. I didn't read closely, but that may come in handy.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 5, 2017 at 2:00 pm
dbo.Sudent Schema
ID
EMployeeCd
EmpNum
Insert into dbo.Student values
(1, 1245678334,001)
(2,678345678,002)
dboEmployee Scehma
ID
EMployeeCd
EmpNum
Insert into dbo.Employee values
(1, NULL,NULL)
(2,NULL,NULL)
based u on matcing ID EMployee tabe will be updated.
Exception table schema
EMployeeCd
EMpNum
Createddate
In the aove procedure - based n data provided -
1.Data Validation - if rowcount = 0 then i have to keep all rec in exception table for example - SET @NoOfRowssUpdated = @@ROWCOUNT; if the @NoOfRowssUpdated count is 0 then i have to insert into exception - Example - if i have 50 records from dbo.Student table;Out of 50 ; 40 are matched and 10 are not matched. i need only these 10 records that to keep in excpetion table
2. Techincal Validation - In the Catch block - If some thing fails i have to keep all the records from dbo.Student and keep in Exception table.If error occurs nothing has to update evrything should rollback
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply