ALTER PROCEDURE [dbo].[CM_EMPLOYEE_Ins] @p_EMP_CODE varchar(15) = NULL, @p_EMP_NAME nvarchar(50) = NULL, @p_BRANCH_ID varchar(15) = NULL, @p_DEP_ID varchar(15) = NULL, @p_NOTES nvarchar(1000) = NULL, @p_RECORD_STATUS varchar(1) = NULL, @p_AUTH_STAT 
US varchar(1) = NULL, @p_MAKER_ID varchar(15) = NULL, @p_CREATE_DT VARCHAR(20) = NULL, @p_CHECKER_ID varchar(15) = NULL, @p_APPROVE_DT VARCHAR(20) = NULL
 AS 
--Validation is here 
DECLARE @ERRORSYS NVARCHAR(15) = '' IF ( EXISTS ( SELECT * FROM CM_EMP 
LOYEE WHERE EMP_CODE=@p_EMP_CODE AND RECORD_STATUS ='1')) 
SET @ERRORSYS = 'EMP-0001' IF @ERRORSYS <> '' BEGIN SELECT ErrorCode Result, '' EMP_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS RETURN '0' END BEGIN TRANSACTION DECLARE @l_EMP_ID VARCHAR(15) 
EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID out IF @l_EMP_ID='' OR @l_EMP_ID IS NULL GOTO ABORT print @l_EMP_ID INSERT INTO CM_EMPLOYEE([EMP_ID],[EMP_CODE],[EMP_NAME],[BRANCH_ID],[DEP_ID],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[ 
CREATE_DT],[CHECKER_ID],[APPROVE_DT]) VALUES(@l_EMP_ID ,@p_EMP_CODE ,@p_EMP_NAME ,@p_BRANCH_ID ,@p_DEP_ID ,@p_NOTES ,@p_RECORD_STATUS ,@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103 
) ) IF @@Error <> 0 GOTO ABORT COMMIT TRANSACTION SELECT '0' as Result, @l_EMP_ID EMP_ID, '' ErrorDesc RETURN '0' ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' EMP_ID, '' ErrorDesc RETURN '-1' End