ALTER PROCEDURE [dbo].[BID_MASTER_Upd]
@p_BID_ID VARCHAR(15),
@p_BID_CODE	varchar(50)  = NULL,
@p_BID_YPE	varchar(1)  = NULL,
@p_PROJECT_ID	varchar(15)  = NULL,
@p_TERM_BID	nvarchar(200)  = NULL,
@p_FORM	varchar(4)  = NULL,
@p_INPUT_DT	VARCHAR(20) = NULL,
@p_EXP_DT	VARCHAR(20) = NULL,
@p_OPEN_DT	VARCHAR(20) = NULL,
@p_BUDGET	decimal(18)  = NULL,
@p_TOTAL_AMT	decimal(18)  = NULL,
@p_GUARANTEE_AMT	decimal(18)  = NULL,
@p_GUARANTEE_PER	decimal(18)  = NULL,
@p_GUARANTEE_EXP	VARCHAR(20) = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(12)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_BID_CATEGORY VARCHAR(15)=NULL,
@p_Contractor_Det XML = NULL

AS
	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_Contractor_Det
	DECLARE ContractorDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ContractorDetail',2)
	WITH 
	(
		ID VARCHAR(15),
		SUP_ID	varchar(15)  ,
		SEND_DT	VARCHAR(20)  ,
		OFFERING_VALUE	decimal(18),
		FORM	varchar(4),
		EXP_DT	VARCHAR(20),
		BANK	nvarchar(200),
		VOUCHER	varchar(20) ,
		PROGRESS_TIME INT,
		ISVALID varchar(1),
		IS_BID_WIN	varchar(1) ,
		NOTES nvarchar(1000)
	)
	OPEN ContractorDetail
	-- Put validation here
	
BEGIN TRANSACTION
	----
	--IF(@p_BID_CATEGORY IS NOT NULL AND @p_BID_CATEGORY <>'')
	--BEGIN
		
		IF(NOT EXISTS(SELECT BID_ID FROM dbo.BID_MASTER WHERE BID_ID=@p_BID_ID AND BID_CATEGORY =@p_BID_CATEGORY))
		BEGIN
		EXEC dbo.BID_CODE_GenKey 'BID_MASTER', @p_BID_CATEGORY, @p_BID_CODE OUTPUT 
		END
		-- Kiem tra ma ke hoach co ton tai hay chua
	IF EXISTS(SELECT * FROM BID_MASTER WHERE [BID_CODE]=@p_BID_CODE AND [BID_ID] != @p_BID_ID)
	BEGIN
		ROLLBACK TRANSACTION
		SELECT ErrorCode Result, '' BID_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'BID-00001'
		RETURN '0'
	END

	----
	UPDATE BID_MASTER SET [BID_CODE] = @p_BID_CODE, [BID_TYPE] = @p_BID_YPE, [PROJECT_ID] = @p_PROJECT_ID,[TERM_BID] = @p_TERM_BID,[FORM] = @p_FORM,[INPUT_DT] = CONVERT(DATETIME, @p_INPUT_DT, 103),[EXP_DT] = CONVERT(DATETIME, @p_EXP_DT, 103),[OPEN_DT] = CONVERT(DATETIME, @p_OPEN_DT, 103),[BUDGET] = @p_BUDGET,[TOTAL_AMT] = @p_TOTAL_AMT,[GUARANTEE_AMT] = @p_GUARANTEE_AMT,[GUARANTEE_PER] = @p_GUARANTEE_PER,[GUARANTEE_EXP] = CONVERT(DATETIME, @p_GUARANTEE_EXP, 103),[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[AUTH_STATUS] = @p_AUTH_STATUS,[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),BID_CATEGORY=@p_BID_CATEGORY
	WHERE  BID_ID= @p_BID_ID
	--IF @@Error <> 0 GOTO ABORT
	--	DECLARE 
	--	@ID VARCHAR(15),
	--	@SUP_ID	varchar(15)  ,
	--	@SEND_DT	VARCHAR(20)  ,
	--	@OFFERING_VALUE	decimal(18),
	--	@FORM	varchar(4),
	--	@EXP_DT	VARCHAR(20),
	--	@BANK	nvarchar(200),
	--	@VOUCHER	varchar(20) ,
	--	@ISVALID varchar(1),
	--	@IS_BID_WIN	varchar(1) ,
	--	@PROGRESS_TIME INT,
	--	@NOTES nvarchar(1000)
	--	FETCH NEXT FROM ContractorDetail INTO @ID,@SUP_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
	--	@EXP_DT,@BANK,@VOUCHER,@PROGRESS_TIME,@ISVALID,@IS_BID_WIN,@NOTES
		
	--	DELETE FROM BID_CONTRACTOR_DT WHERE BID_ID = @p_BID_ID

	--	WHILE @@FETCH_STATUS = 0
	--	BEGIN
	--		IF(LEN(@ID) = 0)
	--		--INSERT NEW
	--		--EXEC BID_CONTRACTOR_DT_Ins @SUP_ID,@P_BID_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
	--		--@EXP_DT,@BANK,@VOUCHER,@IS_BID_WIN,@NOTES,NOW,'','','','',''
	--		--IF @@ERROR <> 0 GOTO ABORT
	--			EXEC SYS_CodeMasters_Gen 'BID_CONTRACTOR_DT', @ID out
	--			IF @ID='' OR @ID IS NULL GOTO ABORT
	--		--ELSE
	--		BEGIN
	--			IF @EXP_DT = '' SET @EXP_DT = NULL
	--			INSERT INTO BID_CONTRACTOR_DT([ID],[SUP_ID],[BID_ID],[SEND_DT],[OFFERING_VALUE],[FORM],[EXP_DT],
	--			[BANK],[VOUCHER],[ISVALID],[IS_BID_WIN],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],
	--			[CHECKER_ID],[APPROVE_DT],[PROGRESS_TIME])
	--			VALUES(@ID ,@SUP_ID ,@P_BID_ID ,CONVERT(DATETIME, @SEND_DT, 103) ,@OFFERING_VALUE ,@FORM,
	--			CASE WHEN @EXP_DT IS NOT NULL AND @EXP_DT <> '' then CONVERT(DATETIME, @EXP_DT, 103) ELSE NULL END ,@BANK ,@VOUCHER ,@ISVALID,@IS_BID_WIN ,
	--			@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,
	--			CONVERT(DATETIME, @p_APPROVE_DT, 103),@PROGRESS_TIME)
	--		END
	--		--IF @@ERROR <> 0 GOTO ABORT
	--		FETCH NEXT FROM ContractorDetail INTO @ID,@SUP_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
	--		@EXP_DT,@BANK,@VOUCHER,@PROGRESS_TIME,@ISVALID,@IS_BID_WIN,@NOTES
	--	END

	--	CLOSE ContractorDetail
	--	DEALLOCATE ContractorDetail

COMMIT TRANSACTION
SELECT '0' as Result, @P_BID_ID  BID_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		CLOSE ContractorDetail
		DEALLOCATE ContractorDetail
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' BID_ID, '' ErrorDesc
		RETURN '-1'
End

¿
ALTER PROCEDURE [dbo].[BID_MASTER_Ins]
@p_BID_CODE	varchar(50)  = NULL,
@p_BID_TYPE	varchar(1)  = NULL,
@p_PROJECT_ID	varchar(15)  = NULL,
@p_TERM_BID	nvarchar(200)  = NULL,
@p_FORM	varchar(4)  = NULL,
@p_INPUT_DT	VARCHAR(20) = NULL,
@p_EXP_DT	VARCHAR(20) = NULL,
@p_OPEN_DT	VARCHAR(20) = NULL,
@p_BUDGET	decimal(18)  = NULL,
@p_TOTAL_AMT	decimal(18)  = NULL,
@p_GUARANTEE_AMT	decimal(18)  = NULL,
@p_GUARANTEE_PER	decimal(18)  = NULL,
@p_GUARANTEE_EXP	VARCHAR(20) = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(12)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_BID_CATEGORY VARCHAR(15)=NULL,
@p_Contractor_Det XML = NULL
AS
	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_Contractor_Det
	DECLARE ContractorDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ContractorDetail',2)
	WITH 
	(
		SUP_ID	varchar(15)  ,
		SEND_DT	VARCHAR(20)  ,
		OFFERING_VALUE	decimal(18),
		FORM	varchar(4),
		EXP_DT	VARCHAR(20),
		BANK	nvarchar(200),
		VOUCHER	varchar(20) ,
		PROGRESS_TIME INT,
		ISVALID varchar(1),
		IS_BID_WIN	varchar(1) ,
		NOTES nvarchar(1000)
	)
	OPEN ContractorDetail

BEGIN TRANSACTION
	IF(@p_BID_CATEGORY IS NOT NULL AND @p_BID_CATEGORY <>'')
	BEGIN
		EXEC dbo.BID_CODE_GenKey 'BID_MASTER',@p_BID_CATEGORY,@p_BID_CODE OUTPUT 
	END
	IF EXISTS(SELECT * FROM BID_MASTER WHERE [BID_CODE]=@p_BID_CODE)
	BEGIN
		ROLLBACK TRANSACTION
		SELECT ErrorCode Result, '' BID_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'BID-00001'
		RETURN 0
	END
DECLARE @l_BID_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'BID_MASTER', @l_BID_ID out
		IF @l_BID_ID='' OR @l_BID_ID IS NULL GOTO ABORT
		IF @p_CREATE_DT = '' SET @p_CREATE_DT = NULL
		INSERT INTO BID_MASTER([BID_ID],[BID_CODE],[BID_TYPE],[PROJECT_ID],[TERM_BID],[FORM],[INPUT_DT],[EXP_DT],[OPEN_DT],[BUDGET],[TOTAL_AMT],[GUARANTEE_AMT],[GUARANTEE_PER],[GUARANTEE_EXP],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],BID_CATEGORY)
		VALUES(@l_BID_ID ,@p_BID_CODE , @p_BID_TYPE,@p_PROJECT_ID ,@p_TERM_BID ,@p_FORM ,CONVERT(DATETIME, @p_INPUT_DT, 103) ,CONVERT(DATETIME, @p_EXP_DT, 103) ,CONVERT(DATETIME, @p_OPEN_DT, 103) ,@p_BUDGET ,@p_TOTAL_AMT ,@p_GUARANTEE_AMT ,@p_GUARANTEE_PER ,CONVERT(DATETIME, @p_GUARANTEE_EXP, 103) ,@p_NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),@p_BID_CATEGORY )
		IF @@Error <> 0 GOTO ABORT
		DECLARE 
		@SUP_ID	varchar(15)  ,
		@SEND_DT	VARCHAR(20)  ,
		@PROGRESS_TIME INT,
		@OFFERING_VALUE	decimal(18),
		@FORM	varchar(4),
		@EXP_DT	VARCHAR(20),
		@BANK	nvarchar(200)  ,
		@VOUCHER	varchar(20) ,
		@ISVALID varchar(1),
		@IS_BID_WIN	varchar(1) ,
		@NOTES nvarchar(1000)
		FETCH NEXT FROM ContractorDetail INTO @SUP_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
		@EXP_DT,@BANK,@VOUCHER,@PROGRESS_TIME,@ISVALID,@IS_BID_WIN,@NOTES

		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF(@OFFERING_VALUE =0)
			BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' BID_ID, N'Giá chào thầu bắc buộc nhập' ErrorDesc
					RETURN -1
			END
			--EXEC BID_CONTRACTOR_DT_Ins @SUP_ID,@l_BID_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
			--@EXP_DT,@BANK,@VOUCHER,@IS_BID_WIN,@NOTES,NOW,'','','','',''
			DECLARE @l_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'BID_CONTRACTOR_DT', @l_ID out
			IF @l_ID='' OR @l_ID IS NULL GOTO ABORT
			IF @SEND_DT = '' SET @SEND_DT = NULL
			IF @EXP_DT = '' SET @EXP_DT = NULL
			INSERT INTO BID_CONTRACTOR_DT([ID],[SUP_ID],[BID_ID],[SEND_DT],[OFFERING_VALUE],[FORM],[EXP_DT],
			[BANK],[VOUCHER],[ISVALID],[IS_BID_WIN],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],
			[CHECKER_ID],[APPROVE_DT],[PROGRESS_TIME])
			VALUES(@l_ID ,@SUP_ID ,@l_BID_ID ,CONVERT(DATETIME, @SEND_DT, 103) ,@OFFERING_VALUE ,@FORM,
			CONVERT(DATETIME, @EXP_DT, 103) ,@BANK ,@VOUCHER ,@ISVALID, @IS_BID_WIN ,
			@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,
			@p_CHECKER_ID ,NULL,@PROGRESS_TIME )
			
			--IF @@ERROR <> 0 GOTO ABORT

			FETCH NEXT FROM ContractorDetail INTO @SUP_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
			@EXP_DT,@BANK,@VOUCHER,@PROGRESS_TIME,@ISVALID,@IS_BID_WIN,@NOTES
		END

		CLOSE ContractorDetail
		DEALLOCATE ContractorDetail

COMMIT TRANSACTION
SELECT '0' as Result, @l_BID_ID  BID_ID, @p_BID_CODE ErrorDesc
RETURN 0
ABORT:
BEGIN
		CLOSE ContractorDetail
		DEALLOCATE ContractorDetail
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' BID_ID, '' ErrorDesc
		RETURN -1
End