新建数据集--数据库查询,调用存储过程报错

报错信息,及调用语法如图,请指点哪里出错了,谢谢!

Snipaste_2020-02-18_09-31-13.jpg

此报错信息是在,数据预览的时候出现的。正确的存储过程调用应该如何写?

脚本如下:

CREATE PROCEDURE [lc0219999].[ERP_LHDF_YMRKTJBB] (@KSRQ varchar (8),@JSRQ varchar (8)) 


AS

BEGIN


CREATE TABLE #JSD

(

BFJSD1_ZDRQ NVARCHAR (8),

BFJSD1_JYBG NVARCHAR (20),

BFJSD1_LSBH NVARCHAR (10),

BFJSD1_SJDH NVARCHAR (20),

BFJSD2_SL FLOAT,

BFJSD2_JSSL FLOAT,

BFJSD2_GBLS NVARCHAR (100),

BFJSD2_C1B FLOAT,

BFJSD2_C2B FLOAT,

BFJSD2_C3B FLOAT,

BFJSD1_BZ NVARCHAR (100),

BFJSD1_DWBH NVARCHAR (30),

BFJSD2_DJ FLOAT,

BFJSD2_JE FLOAT

)


INSERT INTO #JSD (BFJSD1_ZDRQ,BFJSD1_JYBG,BFJSD1_LSBH,BFJSD1_SJDH,BFJSD2_SL,BFJSD2_JSSL,BFJSD2_GBLS,

BFJSD2_C1B,BFJSD2_C2B,BFJSD2_C3B,BFJSD1_BZ,BFJSD1_DWBH,BFJSD2_DJ,BFJSD2_JE)

SELECT BFJSD1_ZDRQ,BFJSD1_JYBG,BFJSD1_LSBH,BFJSD1_SJDH,BFJSD2_SL,BFJSD2_JSSL,BFJSD2_GBLS,

BFJSD2_C1B,BFJSD2_C2B,BFJSD2_C3B,BFJSD1_BZ,BFJSD1_DWBH,BFJSD2_DJ,BFJSD2_JE

FROM BFJSD1,BFJSD2

WHERE BFJSD1_LSBH=BFJSD2_LSBH

AND (BFJSD1_ZDRQ>=@KSRQ

AND BFJSD1_ZDRQ<=@JSRQ)




CREATE TABLE #JYBG

(

ZJJYBG1_SJDH NVARCHAR (20),

ZJJYBG3_LSBH NVARCHAR (10),

SCSF FLOAT,

SCZZ FLOAT

)



INSERT INTO #JYBG (ZJJYBG1_SJDH,ZJJYBG3_LSBH,SCSF,SCZZ)

SELECT DISTINCT 

ZJJYBG1_SJDH,ZJJYBG3_LSBH,

convert(float,MAX(CASE WHEN ZJJYBG3_XMBH='01' THEN ZJJYBG3_JYSJ END)) SCSF,

convert(float,MAX(CASE WHEN ZJJYBG3_XMBH='02' THEN ZJJYBG3_JYSJ END)) SCZZ

FROM ZJJYBG1,ZJJYBG3

WHERE (ZJJYBG3_XMBH='01' OR ZJJYBG3_XMBH='02')

AND ZJJYBG1_SJDH IN (SELECT BFJSD1_JYBG FROM #JSD)

AND ZJJYBG1_LSBH=ZJJYBG3_LSBH

GROUP BY ZJJYBG1_SJDH,ZJJYBG3_LSBH




CREATE TABLE #GBD

(

BFGBGL_LYDH NVARCHAR (20),

BFGBD_LSBH NVARCHAR (10),

BFGBD_SJDH NVARCHAR (20),

BFGBD_KZ FLOAT,

BFGBD_CLPH NVARCHAR (30)

)




INSERT INTO #GBD (BFGBGL_LYDH,BFGBD_LSBH,BFGBD_SJDH,BFGBD_KZ,BFGBD_CLPH)

SELECT BFGBGL_LYDH,BFGBD_LSBH,BFGBD_SJDH,BFGBD_KZ,BFGBD_CLPH

FROM BFGBD,BFGBGL

WHERE

BFGBD_LSBH=BFGBGL_LSBH

AND BFGBD_LJBM='CGGBD'

AND BFGBD_WLBH='010100001'

AND BFGBD_LSBH IN (SELECT BFJSD2_GBLS FROM #JSD )




CREATE TABLE #RKD

(

KCRKD1_DJRQ NVARCHAR (8),

KCRKD1_LSBH NVARCHAR (10),

KCRKD1_SJDH NVARCHAR (20),

KCRKD1_CKBH NVARCHAR (20),

KCRKD2_GBDLS NVARCHAR (10),

KCRKD2_SSSL FLOAT

)




INSERT INTO #RKD (KCRKD1_DJRQ,KCRKD1_LSBH,KCRKD1_SJDH,KCRKD1_CKBH,KCRKD2_GBDLS,KCRKD2_SSSL)

SELECT DISTINCT KCRKD1_DJRQ,KCRKD1_LSBH,KCRKD1_SJDH,KCRKD1_CKBH,KCRKD2_GBDLS,KCRKD2_SSSL

FROM KCRKD1,KCRKD2 

WHERE 

KCRKD1_LSBH=KCRKD2_LSBH

AND KCRKD2_GBDLS IN (SELECT BFGBD_LSBH FROM #GBD)




CREATE TABLE #YMRKTJ

(

BFGBD_CLPH NVARCHAR (30),

LSCKZD_CKMC NVARCHAR (40),

BFJSD1_DWBH NVARCHAR (30),

BFJSD1_BZ NVARCHAR (100),

BFJSD1_ZDRQ NVARCHAR (8),

BFJSD1_JYBG NVARCHAR (20),

BFJSD1_SJDH NVARCHAR (20),

BFGBGL_LYDH NVARCHAR (20),

BFGBD_SJDH NVARCHAR (20),

KCRKD1_DJRQ NVARCHAR (8),

KCRKD1_SJDH NVARCHAR (20),

YSSL FLOAT,

BFJSD2_JSSL FLOAT,

BFJSD2_C1B FLOAT,

BFJSD2_C2B FLOAT,

BFJSD2_C3B FLOAT,

BFJSD2_DJ FLOAT,

BFJSD2_JE FLOAT,

SCSF FLOAT,

SCZZ FLOAT

)




INSERT INTO #YMRKTJ

(

BFGBD_CLPH,LSCKZD_CKMC,BFJSD1_DWBH,BFJSD1_BZ,  

BFJSD1_ZDRQ,BFJSD1_JYBG, 

BFJSD1_SJDH,BFGBGL_LYDH,BFGBD_SJDH,KCRKD1_DJRQ,

KCRKD1_SJDH,YSSL,BFJSD2_JSSL, 

BFJSD2_C1B, BFJSD2_C2B, BFJSD2_C3B,  

BFJSD2_DJ, BFJSD2_JE, 

SCSF, SCZZ

)

SELECT BFGBD_CLPH,LSCKZD_CKMC, BFJSD1_DWBH, BFJSD1_BZ, BFJSD1_ZDRQ, BFJSD1_JYBG, 

--BFJSD1_LSBH, 

BFJSD1_SJDH, BFGBGL_LYDH,BFGBD_SJDH, KCRKD1_DJRQ, 

KCRKD1_SJDH,BFJSD2_SL+BFGBD_KZ AS YSSL, BFJSD2_JSSL, 

BFJSD2_C1B, BFJSD2_C2B, BFJSD2_C3B,  

BFJSD2_DJ, BFJSD2_JE, 

SCSF, SCZZ

FROM #JSD,#JYBG,#GBD,#RKD,LSCKZD,ZWWLDW

WHERE LSCKZD_CKBH=KCRKD1_CKBH

AND BFGBD_LSBH=BFJSD2_GBLS

AND BFJSD1_JYBG=ZJJYBG1_SJDH

AND KCRKD2_GBDLS=BFGBD_LSBH 

AND ZWWLDW_DWBH=BFJSD1_DWBH 







SELECT BFGBD_CLPH AS 车辆牌号, 

LSCKZD_CKMC AS 仓库名称,

BFJSD1_DWBH AS 客户编号, 

BFJSD1_BZ AS 合同客户, 

BFJSD1_ZDRQ AS 结算日期, 

BFJSD1_JYBG AS 报告单号, 

BFJSD1_SJDH AS 结算单号, 

BFGBGL_LYDH AS 到货单号, 

BFGBD_SJDH AS 过磅单号, 

KCRKD1_DJRQ AS 入库日期, 

KCRKD1_SJDH AS 入库单号, 

YSSL AS 应收数量, 

BFJSD2_JSSL AS 实收数量, 

BFJSD2_C1B AS 扣水数量, 

BFJSD2_C2B AS 扣杂数量, 

BFJSD2_C3B AS 扣生霉粒, 

BFJSD2_DJ AS 单价, 

BFJSD2_JE AS 结算金额, 

SCSF AS 实测水分, 

SCZZ AS 实测杂质

FROM #YMRKTJ

ORDER BY BFJSD1_SJDH


END


FineReport Starshowercn 发布于 2020-2-18 09:31 (编辑于 2020-2-19 09:48)
1min目标场景问卷 立即参与
回答问题
悬赏:4 F币 + 添加悬赏
提示:增加悬赏、完善问题、追问等操作,可使您的问题被置顶,并向所有关注者发送通知
共1回答
最佳回答
0
ooshanghaiLv5初级互助
发布于2020-2-18 10:19(编辑于 2020-2-19 16:41)


SELECT BFJSD1_ZDRQ,BFJSD1_JYBG,BFJSD1_LSBH,BFJSD1_SJDH,BFJSD2_SL,BFJSD2_JSSL,BFJSD2_GBLS,

BFJSD2_C1B,BFJSD2_C2B,BFJSD2_C3B,BFJSD1_BZ,BFJSD1_DWBH,BFJSD2_DJ,BFJSD2_JE

INTO #JSD

FROM BFJSD1,BFJSD2

WHERE BFJSD1_LSBH=BFJSD2_LSBH

AND (BFJSD1_ZDRQ>=@KSRQ

AND BFJSD1_ZDRQ<=@JSRQ)



SELECT DISTINCT 

ZJJYBG1_SJDH,ZJJYBG3_LSBH,

convert(float,MAX(CASE WHEN ZJJYBG3_XMBH='01' THEN ZJJYBG3_JYSJ END)) SCSF,

convert(float,MAX(CASE WHEN ZJJYBG3_XMBH='02' THEN ZJJYBG3_JYSJ END)) SCZZ

INTO #JYBG

FROM ZJJYBG1,ZJJYBG3

WHERE (ZJJYBG3_XMBH='01' OR ZJJYBG3_XMBH='02')

AND ZJJYBG1_SJDH IN (SELECT BFJSD1_JYBG FROM #JSD)

AND ZJJYBG1_LSBH=ZJJYBG3_LSBH

GROUP BY ZJJYBG1_SJDH,ZJJYBG3_LSBH



SELECT BFGBGL_LYDH,BFGBD_LSBH,BFGBD_SJDH,BFGBD_KZ,BFGBD_CLPH

INTO #GBD

FROM BFGBD,BFGBGL

WHERE BFGBD_LSBH=BFGBGL_LSBH

AND BFGBD_LJBM='CGGBD'

AND BFGBD_WLBH='010100001'

AND BFGBD_LSBH IN (SELECT BFJSD2_GBLS FROM #JSD )


SELECT DISTINCT KCRKD1_DJRQ,KCRKD1_LSBH,KCRKD1_SJDH,KCRKD1_CKBH,KCRKD2_GBDLS,KCRKD2_SSSL

INTO #RKD

FROM KCRKD1,KCRKD2 

WHERE KCRKD1_LSBH=KCRKD2_LSBH

AND KCRKD2_GBDLS IN (SELECT BFGBD_LSBH FROM #GBD)






SELECT BFGBD_CLPH,LSCKZD_CKMC, BFJSD1_DWBH, BFJSD1_BZ, BFJSD1_ZDRQ, BFJSD1_JYBG, 

--BFJSD1_LSBH, 

BFJSD1_SJDH, BFGBGL_LYDH,BFGBD_SJDH, KCRKD1_DJRQ, 

KCRKD1_SJDH,BFJSD2_SL+BFGBD_KZ AS YSSL, BFJSD2_JSSL, 

BFJSD2_C1B, BFJSD2_C2B, BFJSD2_C3B,  

BFJSD2_DJ, BFJSD2_JE, 

SCSF, SCZZ

INTO #YMRKTJ

FROM #JSD,#JYBG,#GBD,#RKD,LSCKZD,ZWWLDW

WHERE LSCKZD_CKBH=KCRKD1_CKBH

AND BFGBD_LSBH=BFJSD2_GBLS

AND BFJSD1_JYBG=ZJJYBG1_SJDH

AND KCRKD2_GBDLS=BFGBD_LSBH 

AND ZWWLDW_DWBH=BFJSD1_DWBH 



SELECT BFGBD_CLPH AS 车辆牌号, 

LSCKZD_CKMC AS 仓库名称,

BFJSD1_DWBH AS 客户编号, 

BFJSD1_BZ AS 合同客户, 

BFJSD1_ZDRQ AS 结算日期, 

BFJSD1_JYBG AS 报告单号, 

BFJSD1_SJDH AS 结算单号, 

BFGBGL_LYDH AS 到货单号, 

BFGBD_SJDH AS 过磅单号, 

KCRKD1_DJRQ AS 入库日期, 

KCRKD1_SJDH AS 入库单号, 

YSSL AS 应收数量, 

BFJSD2_JSSL AS 实收数量, 

BFJSD2_C1B AS 扣水数量, 

BFJSD2_C2B AS 扣杂数量, 

BFJSD2_C3B AS 扣生霉粒, 

BFJSD2_DJ AS 单价, 

BFJSD2_JE AS 结算金额, 

SCSF AS 实测水分, 

SCZZ AS 实测杂质

FROM #YMRKTJ


ORDER BY BFJSD1_SJDH



DROP TABLE INTO #JSD

DROP TABLE INTO #JYBG

DROP TABLE INTO #GBD

DROP TABLE INTO #RKD

DROP TABLE INTO #YMRKTJ



  • Starshowercn Starshowercn(提问者) 不好意思!刚才我试了一下,还是报错!(# ̄~ ̄#) exec lc0219999.ERP_LHDF_YMRKTJBB \'${KSRQ}\',\'${JSRQ}\'
    2020-02-18 10:36 
  • ooshanghai ooshanghai 回复 Starshowercn(提问者) 格式正确,看报错也执行了,但是你的过程里没有返回结果集,检查过程啊,随便写个select 0 结尾,调试下,我估计就不会报错了,如果是的话,就确认你的存储过程并没有返回结果啊
    2020-02-18 10:49 
  • Starshowercn Starshowercn(提问者) 回复 ooshanghai 存储过程前面的参数都带着,过程只写select 0 能返回结果。我原来的过程中有临时表,会不会是因为临时表出问题了。单独执行存储过程是没有问题的,能够得到正确的结果集。
    2020-02-18 11:30 
  • ooshanghai ooshanghai 回复 Starshowercn(提问者) 过程里有临时表应该不会影响,建议你直接把存储过程放到帆软的sql书写处,临时表,变量啥的都一模一样的,没啥区别
    2020-02-18 12:11 
  • Starshowercn Starshowercn(提问者) 回复 ooshanghai 好郁闷呢!我按照您说的把存储过程放到SQL查询里面了,预览的时候从前到后也都执行,可到最后还是提示没有返回结果集。和前面直接调用存储过程一样。
    2020-02-18 15:11 
  • 2关注人数
  • 442浏览人数
  • 最后回答于:2020-2-19 16:41
    请选择关闭问题的原因
    确定 取消
    返回顶部