Insert, Update, Delete using single query

Here is an example of a sql query, which uses all three modes inside it i.e. Insert, Update & Delete.

Here is the query for that

CREATE PROCEDURE Procedure1
(
@MODE VARCHAR(10)='',
@PAGENO INT=0,
@PAGESIZE INT=0,
@EMPID INT=0,
@FIRSTNAME VARCHAR(20)='',
@LASTNAME VARCHAR(20)='',
@ADDRESS VARCHAR(20)='',
@BIRTHDATE DATETIME=NULL,
@PHONENO VARCHAR(15)='',
@EMAIL VARCHAR(20)='',
@SALARY NUMERIC(9)=0,
@MARIALSTATUS VARCHAR(10)='',
@COUNTRY VARCHAR(10)='',
@STATE VARCHAR(10)=''
)
AS
BEGIN
DECLARE @REC_NO INT
SET @REC_NO=1
IF @MODE='SELECT'
BEGIN
  SELECT I_EMPID, V_FIRSTNAME, V_LASTNAME, V_ADDRESS, V_EMAIL, I_PHONENO FROM SP_TBL_FIRSTEMP
END
IF @MODE='INSERT'
BEGIN
  INSERT INTO SP_TBL_FIRSTEMP (V_FIRSTNAME, V_LASTNAME, V_ADDRESS,
    D_BIRTHDATE, I_PHONENO, V_EMAIL, N_SALARY, V_MARIALSTATUS,
    V_COUNTRY, V_STATE)
  VALUES(@FIRSTNAME, @LASTNAME, @ADDRESS, @BIRTHDATE, @PHONENO, @EMAIL, @SALARY,
   @MARIALSTATUS, @COUNTRY, @STATE)
END
IF @MODE='DELETE'
BEGIN
  DELETE FROM SP_TBL_FIRSTEMP WHERE I_EMPID = @EMPID
END
IF @MODE='UPDATE'
BEGIN
  UPDATE SP_TBL_FIRSTEMP SET
  V_FIRSTNAME = @FIRSTNAME,
  V_LASTNAME =@LASTNAME,
  V_ADDRESS = @ADDRESS,
  D_BIRTHDATE = @BIRTHDATE,
  I_PHONENO = @PHONENO,
  V_EMAIL = @EMAIL,
  N_SALARY = @SALARY,
  V_MARIALSTATUS = @MARIALSTATUS,
  V_COUNTRY = @COUNTRY,
  V_STATE = @STATE
  WHERE I_EMPID=@EMPID  
END
END

GO