VFP 如何使用sql存储过程
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE PROC_TJ_TJBGCX_MXJG_DQ_RMYY
@as_tjbh varchar(30),@ai_tjcs int
AS
create table #mxjg(tjbh varchar(14) null,
tjcs int null,
djlsh varchar(12) null,
xm varchar(40) null,
xb varchar(1) null,
csnyr datetime null,
sfzh varchar(32) null,
tjxmbh varchar(6) null,
tjxm varchar(6) null,
jg text null,
mc varchar(40) null,
dw varchar(20) null,
ckxx varchar(20) null,
cksx varchar(20) null,
zcts varchar(255) null,
pdts varchar(20) null,
pgts varchar(20) null,
ckjg varchar(255) null,
disp_order_ks varchar(6) null,
disp_order_zhxm varchar(6) null,
disp_order_mxxm varchar(6) null,
lxbh varchar(6) null,
jcrq datetime null,
jcys varchar(6) null,
ckz varchar(40) null,
ts varchar(20) null,
xh int null,
jglx char(1) null ,
JCQKFZSM varchar(1000) null,
HBXMVARCHAR(255) NULL
)
insert #mxjg
SELECT TJ_TJDJB.TJBH,
TJ_TJDJB.TJCS,
TJ_TJDJB.DJLSH,
TJ_TJDJB.XM,
TJ_TJDJB.XB,
TJ_TJDJB.CSNYR,
TJ_TJDJB.PHONE,
TJ_TJJLB.TJXMBH,
TJ_TJJLMXB.TJXM,
TJ_TJJLMXB.JG,
TJ_TJXMB.MC,
CASE
WHEN LEN(RTRIM(LTRIM(TJ_TJJLMXB.DW))) > 0 THEN LEFT(TJ_TJJLMXB.DW,20)
ELSE TJ_TJXMB.DW
END AS 'DW',
CASE
WHEN TJ_TJDJB.XB = '1' THEN
CASE
WHEN TJ_TJXMB.NXCKXX IS NULL OR LEN(TJ_TJXMB.NXCKXX) = 0 THEN TJ_TJXMB.CKXX
ELSE TJ_TJXMB.NXCKXX
END
ELSE TJ_TJXMB.CKXX
END AS CKXX,
CASE
WHEN TJ_TJDJB.XB = '1' THEN
CASE
WHEN TJ_TJXMB.NXCKSX IS NULL OR LEN(TJ_TJXMB.NXCKSX) = 0 THEN TJ_TJXMB.CKSX
ELSE TJ_TJXMB.NXCKSX
END
ELSE TJ_TJXMB.CKSX
END AS CKSX,
TJ_TJXMB.ZCTS,
TJ_TJXMB.PDTS,
TJ_TJXMB.PGTS,
TJ_TJXMB.CKJG,
TJ_TJLXB.DISP_ORDER,
TJ_ZHXM_HD.DISP_ORDER,
TJ_TJXMB.DISP_ORDER,
TJ_TJJLB.LXBH,
TJ_TJJLB.JCRQ,
TJ_TJJLB.JCYS ,
case
WHEN TJ_TJJLMXB.CKZ IS null then
case
WHEN TJ_TJDJB.XB = '0' then
case
WHEN (TJ_TJXMB.CKXX IS null or ltrim(rtrim(TJ_TJXMB.CKXX)) = '') and (TJ_TJXMB.CKSX is null or ltrim(rtrim(TJ_TJXMB.CKSX)) = '') then ''
WHEN (TJ_TJXMB.CKXX IS null or ltrim(rtrim(TJ_TJXMB.CKXX)) = '' or convert(decimal(18,3), TJ_TJXMB.CKXX) = 0 ) and convert(decimal(18,3), TJ_TJXMB.CKSX) > 0 then '< ' + TJ_TJXMB.CKSX
WHEN convert(decimal(18,3), TJ_TJXMB.CKXX) > 0 and (TJ_TJXMB.CKSX IS null or ltrim(rtrim(TJ_TJXMB.CKSX)) = '' or convert(decimal(18,3), TJ_TJXMB.CKSX) = 0) then '> ' + TJ_TJXMB.CKXX
else TJ_TJXMB.CKXX + '-' + TJ_TJXMB.CKSX
end
WHEN TJ_TJDJB.XB = '1' then
case
WHEN (TJ_TJXMB.NXCKXX IS null or ltrim(rtrim(TJ_TJXMB.NXCKXX)) = '') and (TJ_TJXMB.NXCKSX is null or ltrim(rtrim(TJ_TJXMB.NXCKSX)) = '') then ''
WHEN (TJ_TJXMB.NXCKXX IS null or ltrim(rtrim(TJ_TJXMB.NXCKXX)) = '' or convert(decimal(18,3), TJ_TJXMB.NXCKXX) = 0) and convert(decimal(18,3), TJ_TJXMB.NXCKSX) > 0 then '< ' + TJ_TJXMB.NXCKSX
WHEN convert(decimal(18,3), TJ_TJXMB.NXCKXX) > 0 and (TJ_TJXMB.NXCKSX IS null or ltrim(rtrim(TJ_TJXMB.NXCKSX)) = '' or convert(decimal(18,3), TJ_TJXMB.NXCKSX) = 0) then '> ' + TJ_TJXMB.NXCKXX
else TJ_TJXMB.NXCKXX + '-' + TJ_TJXMB.NXCKSX
end
END
WHEN LEN(RTRIM(LTRIM(TJ_TJJLMXB.CKZ))) = 0 THEN
case
WHEN TJ_TJDJB.XB = '0' then
case
WHEN (TJ_TJXMB.CKXX IS null or ltrim(rtrim(TJ_TJXMB.CKXX)) = '') and (TJ_TJXMB.CKSX is null or ltrim(rtrim(TJ_TJXMB.CKSX)) = '') then ''
WHEN (TJ_TJXMB.CKXX IS null or ltrim(rtrim(TJ_TJXMB.CKXX)) = '' or convert(decimal(18,3), TJ_TJXMB.CKXX) = 0) and convert(decimal(18,3), TJ_TJXMB.CKSX) > 0 then '< ' + TJ_TJXMB.CKSX
WHEN convert(decimal(18,3), TJ_TJXMB.CKXX) > 0 and (TJ_TJXMB.CKSX IS null or ltrim(rtrim(TJ_TJXMB.CKSX)) = '' or convert(decimal(18,3), TJ_TJXMB.CKSX) = 0) then '> ' + TJ_TJXMB.CKXX
else TJ_TJXMB.CKXX + '-' + TJ_TJXMB.CKSX
end
WHEN TJ_TJDJB.XB = '1' then
case
WHEN (TJ_TJXMB.NXCKXX IS null or ltrim(rtrim(TJ_TJXMB.NXCKXX)) = '') and (TJ_TJXMB.NXCKSX is null or ltrim(rtrim(TJ_TJXMB.NXCKSX)) = '') then ''
WHEN (TJ_TJXMB.NXCKXX IS null or ltrim(rtrim(TJ_TJXMB.NXCKXX)) = '' or convert(decimal(18,3), TJ_TJXMB.NXCKXX) = 0) and convert(decimal(18,3), TJ_TJXMB.NXCKSX) > 0 then '< ' + TJ_TJXMB.NXCKSX
WHEN convert(decimal(18,3), TJ_TJXMB.NXCKXX) > 0 and (TJ_TJXMB.NXCKSX IS null or ltrim(rtrim(TJ_TJXMB.NXCKSX)) = '' or convert(decimal(18,3), TJ_TJXMB.NXCKSX) = 0) then '> ' + TJ_TJXMB.NXCKXX
else TJ_TJXMB.NXCKXX + '-' + TJ_TJXMB.NXCKSX
end
END
ELSE TJ_TJJLMXB.CKZ
END AS 'CKZ',
TS = CASE LEN(RTRIM(LTRIM(TJ_TJJLMXB.CKZ))) WHEN 0 THEN
SPACE(20)
ELSE
TJ_TJJLMXB.TS
END,
TJ_TJJLB.XH,
TJ_TJXMB.JGLX ,
TJ_TJJLB.JCQKFZSM,
TJ_TJJLB.HBXM
FROM TJ_TJXMB,
TJ_TJDJB,
TJ_TJJLB,
TJ_TJJLMXB,
TJ_TJLXB,
TJ_ZHXM_HD
WHERE ( TJ_TJDJB.TJBH = TJ_TJJLB.TJBH ) and
( TJ_TJDJB.TJCS = TJ_TJJLB.TJCS ) and
( TJ_TJJLB.XH = TJ_TJJLMXB.XH ) and
( TJ_TJJLMXB.TJXM = TJ_TJXMB.TJXM ) and
( ( TJ_TJJLB.ISOVER = '1' ) AND
( TJ_TJJLB.XMLX = '1' )
) AND TJ_TJJLB.TJXMBH = TJ_ZHXM_HD.BH
and TJ_TJJLB.LXBH = TJ_TJLXB.LXBH and
TJ_TJDJB.TJBH = @as_tjbh and isnumeric(TJ_TJLXB.DISP_ORDER) = 1 and
TJ_TJDJB.TJCS = @ai_tjcs and TJ_TJJLB.JCQKFZSM is null
DECLARE @xh int ,@lxbh varchar(6), @tjxmbh varchar(6),@tjxm varchar(60),@jgqz varchar(50) ,@jg varchar(1024)
DECLARE @zdbh varchar(255) ,@zdmc varchar(200),@xj varchar(2048)
DECLARE @disp_order int,@pirr_jgqz varchar(50),@curr_jgqz varchar(50),@disp_order1 int,@disp_order_xj int
DECLARE @disp_order_mxjg varchar(6),@disp_order_kk varchar(6),@disp_order_zx varchar(6)
DECLARE @djlsh varchar(12) ,@xm varchar(40) ,@xb varchar(1) ,@csnyr datetime, @sfzh varchar(20),@jglx varchar(1)
DECLARE @JCQKFZSM VARCHAR(1000), @HBXM VARCHAR(255)
DECLARE Mxjg_Cursor CURSOR FOR
SELECT DISTINCT xh ,djlsh,xm,xb,csnyr,sfzh,disp_order_ks,disp_order_zhxm, JCQKFZSM, HBXM FROM #mxjg
OPEN Mxjg_Cursor
FETCH NEXT FROM Mxjg_Cursor INTO @xh,@djlsh,@xm,@xb,@csnyr, @sfzh,@disp_order_kk,@disp_order_zx, @JCQKFZSM, @HBXM
WHILE @@FETCH_STATUS = 0
BEGIN
select @xj =''
SELECT @xj = CONVERT(TEXT,TJ_TJJLB.XJ) FROM TJ_TJJLB WHERE XH = @xh
SELECT @lxbh = (select top 1 lxbh from #mxjg where xh = @xh)
SELECT @tjxmbh = (select top 1 tjxmbh from #mxjg where xh = @xh)
insert #mxjg (djlsh,xm,xb,csnyr,sfzh,xh,lxbh,tjxmbh,tjxm,jg,disp_order_ks,disp_order_zhxm,disp_order_mxxm, JCQKFZSM, HBXM)
values(@djlsh,@xm,@xb,@csnyr, @sfzh,@xh,@lxbh,@tjxmbh,'999999',@xj,@disp_order_kk,@disp_order_zx,'999999', @JCQKFZSM , @HBXM)
FETCH NEXT FROM Mxjg_Cursor INTO @xh,@djlsh,@xm,@xb,@csnyr, @sfzh,@disp_order_kk,@disp_order_zx, @JCQKFZSM, @HBXM
END
CLOSE Mxjg_Cursor
DEALLOCATE Mxjg_Cursor
select * from #mxjg
drop table #mxjg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
