USE [Intelledox_DB]
GO
/****** Object:  StoredProcedure [dbo].[test_db_stamp]    Script Date: 03/05/2016 9:17:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[test_db_stamp]
     @Employee_ID nchar(10) = null
    ,@Employee_Name nvarchar(50) = null
    ,@Employee_TEL nvarchar(10) = null
    ,@Employee_Email nvarchar(50) = null
    ,@Option nchar(10) = null

AS
BEGIN

  IF (@Option = 'ADD')
      BEGIN
          
        DECLARE @RUN int = null
        SET @RUN = (SELECT COUNT(Employee_ID) FROM IDOX_Employee);
        SET @RUN = @RUN + 1;

        IF (@RUN < 9)
            BEGIN
                SET @Employee_ID = 'CT000'+ convert (nchar ,@RUN) ;
            END
        ELSE IF (@RUN < 99)
            BEGIN
                SET @Employee_ID = 'CT00'+ convert (nchar ,@RUN) ;
            END
        ELSE IF (@RUN < 999)
            BEGIN
                SET @Employee_ID = 'CT0'+ convert (nchar ,@RUN) ;
            END
        ELSE BEGIN
          SET @Employee_ID = 'CT'+ convert (nchar ,@RUN) ;
            END

        SET NOCOUNT ON;
        INSERT INTO [dbo].[IDOX_Employee]
            ([Employee_ID]
            ,[Employee_Name]
            ,[Employee_TEL]
            ,[Employee_Email]
            ,[Team_ID])
        VALUES
            (@Employee_ID
            ,@Employee_Name
            ,@Employee_TEL
            ,@Employee_Email
            ,'CTS')
     END

  ELSE IF (@Option = 'UPDATE')
      BEGIN
        SET NOCOUNT ON;
            update [dbo].[IDOX_Employee]
            SET Employee_ID = @Employee_ID, Employee_Name = @Employee_Name, Employee_TEL = @Employee_TEL , Employee_Email = @Employee_Email, Team_ID = 'CTS'
            WHERE Employee_ID = @Employee_ID
    
      END

  ELSE
      BEGIN
        
        SET NOCOUNT ON;
        DELETE FROM [dbo].[IDOX_Employee] WHERE Employee_ID = @Employee_ID
      END


END