Sample SP for inserting/updating records

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 303
The below SP will take one input as digit/number.
SP to insert or update values into EMPLOYEES table.

-- If the employee_id is not passed then insert record, if employee_id passed then update the that record.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('UPDATE_EMP')IS NOT NULL
DROP PROCEDURE UPDATE_EMP;
GO
CREATE PROCEDURE UPDATE_EMP (@P_EMP INT)
AS
BEGIN
IF @P_EMP=(SELECT EMPLOYEE_ID FROM emp_fake WHERE EMPLOYEE_ID=@P_EMP)
BEGIN
UPDATE emp_fake SET SALARY=SALARY*1.15 WHERE EMPLOYEE_ID=@P_EMP;
END
ELSE
INSERT INTO emp_fake VALUES(@P_EMP, 'Chandu','Bandi','B',520.123,GETDATE(),'P_KING',50000,NULL,102,40);
END
GO

EXECUTE UPDATE_EMP 300;

SELECT * FROM emp_fake;

Comments or Responses

Login to post response