dbo.sp_getdata_modulbc30

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_getdata_modulbc30
(@id bigint, @car varchar(50), @bc30dir varchar(500))
AS
BEGIN
  if(select COUNT(*) from [tbl_bc_out_hdr] where CAR=@car) = 0
  begin
 select '07' as status, 'DATA TIDAK DITEMUKAN' AS description
 return
  end

  SET ANSI_WARNINGS ON
  SET ANSI_NULLS ON
  
  exec(
' declare @cnt int set @cnt=( select count(*) from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @bc30dir +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblPebHdr where CAR = '''+@car+
''' ) if @cnt = 0 begin select ''07'' as status, ''DATA DI MODUL TIDAK DITEMUKAN'' AS description return end declare @NODAFT varchar(10) declare @TGDAFT date select top 1 @NODAFT=NODAFT, @TGDAFT=TGDAFT from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @bc30dir +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblPebHdr where CAR = '''+@car+
''' update dbo.tbl_bc_out_hdr set NODAFT=@NODAFT, TGDAFT=@TGDAFT where id_bc_out_hdr='+@id+
' if(select count(*) from tbl_bc_out_con where id_bc_out_hdr = '+@id+
') = 0 begin insert into tbl_bc_out_con ( id_bc_out_hdr, CAR, NoCont, [Size], Type, Stuff ) select '+@id+
', CAR, NoCont, [Size], Type, Stuff from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @bc30dir +
'; Jet OLEDB:Database Password=MumtazFarisHana;Persist Security Info=False;'' )...tblPebCon where CAR = '''+@car+
''' end select ''00'' as status, ''SUKSES'' AS description ')
  
  
 
END

GO