/*
[CM_EMPLOYEE_Upd]
@p_EMP_ID= 'EMP000000000002' ,
@p_EMP_CODE	 ='EMP2013/0001',
@p_EMP_NAME	 = NULL ,
@p_BRANCH_ID= NULL ,
@p_DEP_ID= NULL ,
@p_NOTES= NULL ,
@p_RECORD_STATUS= NULL ,
@p_AUTH_STATUS = NULL ,
@p_MAKER_ID	= NULL ,
@p_CREATE_DT = NULL,
@p_CHECKER_ID= NULL ,
@p_APPROVE_DT = NULL
*/
ALTER PROCEDURE [dbo].[CM_EMPLOYEE_Upd]
@p_EMP_ID	varchar(15) = null ,
@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_STATUS	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_EMPLOYEE WHERE EMP_CODE = @p_EMP_CODE AND EMP_ID!=@p_EMP_ID))
	BEGIN
		SET @ERRORSYS = 'EMP-0001'
	END
	IF @ERRORSYS <> '' 
	BEGIN
		--SELECT ErrorCode Result, ''  EMP_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		SELECT @ERRORSYS Result, ''  EMP_ID, N'Nhân viên có mã: '+ @p_EMP_CODE + N' đã tồn tại trong hệ thống. Vui lòng kiểm tra lại thông tin' ErrorDesc 
		RETURN '-1'
	END
BEGIN TRANSACTION
		UPDATE CM_EMPLOYEE SET [EMP_CODE] = @p_EMP_CODE,[EMP_NAME] = @p_EMP_NAME,[BRANCH_ID] = @p_BRANCH_ID,[DEP_ID] = @p_DEP_ID,[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,[AUTH_STATUS] = @p_AUTH_STATUS,[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103)
WHERE  EMP_ID= @p_EMP_ID
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
		SELECT '0' as Result, @p_EMP_ID  EMP_ID, '' ErrorDesc
		RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' EMP_ID, '' ErrorDesc
		RETURN '-1'
End
¿
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_STATUS	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_EMPLOYEE WHERE EMP_CODE=@p_EMP_CODE AND RECORD_STATUS = '1'))
	 SET @ERRORSYS = 'EMP-0001'
IF @ERRORSYS <> '' 
BEGIN
	SELECT @ERRORSYS Result, ''  EMP_ID, N'Nhân viên có mã: '+ @p_EMP_CODE + N' đã tồn tại trong hệ thống. Vui lòng kiểm tra lại thông tin' ErrorDesc 
	RETURN '-1'
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