March 23, 2017 at 1:40 pm
I have a procedure where if one employee fails we are keeping into EmpException table that belongs to specific ID
at the same time I want to keep all the students in the StdException table for that ID
In this procedure how to take all students from dbo.StdTemp table for that ID and keep in StdException table
CREATE PROCEDURE [dbo].[Error]
@ID nvarchar(50)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION
IF EXISTS(SELECT 1 FROM dbo.EMPException WHERE ID=RID)
BEGIN
DELETE FROM dbo.EMPException WHERE ID = @ID
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
END
INSERT INTO dbo.EMPException
(
col1,col2)
SELECT
COl1,
Col2
FROM dbo.EmpTEMP
WHERE ID = @ID
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
FINISH:
COMMIT TRANSACTION
RETURN 0 ERROR:
ROLLBACK TRANSACTION
RETURN -1
end
March 23, 2017 at 2:09 pm
You could achieve this with a MERGE statement.
There is no need for an explicit transaction, as it is one one statement that will succeed or rollback as a whole.
CREATE PROCEDURE dbo.Error
@ID nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
MERGE INTO dbo.EMPException AS dest
USING (SELECT ID, Col1, Col2
FROM dbo.EmpTEMP
WHERE ID = @ID
) AS src ON dest.ID = src.ID
WHEN MATCHED
THEN UPDATE SET dest.Col1 = src.Col1
, dest.Col2 = src.Col2
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID, Col1, Col2)
VALUES (src.ID, src.Col1, src.Col2);
END;
EDIT: Fixed spelling error.
March 23, 2017 at 2:29 pm
But we are not ready using Merge statement..
In the same way can I use delte from exception and again inserting into std exception table what we are doing now with EmpException?
March 23, 2017 at 9:55 pm
mcfarlandparkway - Thursday, March 23, 2017 2:29 PMIn the same way can I use delte from exception and again inserting into std exception table what we are doing now with EmpException?
Yes, you can use MERGE to do the same with Exception and StdException tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply