CREATE DATABASE EMPLOYEE

CREATE TABLE EMPINFO
(
ROWID INT IDENTITY(1,1) NOT NULL,
EMPID INT NOT NULL,
EMPNAME VARCHAR(50) NOT NULL,
EMPDEPT VARCHAR(3) NOT NULL,
EMPSAL INT NOT NULL,
EMPADDRESS VARCHAR(100) NOT NULL,
ACTIVE CHAR(1) NOT NULL DEFAULT ‘T’
)

CREATE PROC EMPINFO_ADD_EDIT
(
@iEMPID INT,
@vEMPNAME VARCHAR(50),
@vEMPDEPT VARCHAR(3),
@iEMPSAL INT ,
@vEMPADDRESS VARCHAR(100),
@cACTIVE CHAR(1)
)
AS
DECLARE @CNT INT;
BEGIN
SET @CNT = 0;
SELECT @CNT = COUNT(*) FROM EMPINFO WHERE EMPID = @iEMPID;
IF(@CNT = 0)
BEGIN

INSERT INTO EMPINFO(EMPID,EMPNAME,EMPDEPT,EMPSAL,EMPADDRESS,ACTIVE)VALUES @iEMPID,@vEMPNAME,@vEMPDEPT,@iEMPSAL,@vEMPADDRESS,@cACTIVE)

END
ELSE
BEGIN

UPDATE EMPINFO SET EMPNAME = @vEMPNAME,EMPDEPT = @vEMPDEPT,EMPSAL = @iEMPSAL, EMPADDRESS = @vEMPADDRESS,ACTIVE = @cACTIVE WHERE EMPID = @iEMPID

END

END