但小弟我想用存储过程 如最后直接的SQL语句一样想一次多查几个货品，怎么实现
日期：2014-05-18 浏览次数：20705 次
但我想用存储过程 如最后直接的SQL语句一样想一次多查几个货品，如何实现
按以下执行存储过种程错误
EXEC cypqgoodsbuyd '16655,19266,3642,13163,3014,3643,19498,3644'
将 varchar 值 '16655,19266,3642,13163,3014,3643,19498,3644' 转换为数据类型为 int 的列时发生语法错误。
原因我明白，就是该字段是数值
EXEC cypqgoodsbuyd 16655 这样执就OK
但我想用存储过程 如最后直接的SQL语句一样想一次多查几个货品，如何实现
直接的SQL
(SELECT GOODSID, QTY,cast(PRICE as decimal(10,3)) PRICES,BILLID
FROM P_BILLD WHERE GOODSID in(16655,19266,3642,13163,3014,3643,19498,3644)) AS A
LEFT JOIN P_BILL as B ON b.BILLID=a.BILLID
LEFT JOIN VENDOR AS c ON c.VENDORID=b.VENDORID
LEFT JOIN TERM AS d ON d.TERMID=b.TERMID
LEFT JOIN cyVgoodsShort AS cvs ON CVS.GOODSID=A.GOODSID
[/code]
------解决方案--------------------
按以下执行存储过种程错误
EXEC cypqgoodsbuyd '16655,19266,3642,13163,3014,3643,19498,3644'
将 varchar 值 '16655,19266,3642,13163,3014,3643,19498,3644' 转换为数据类型为 int 的列时发生语法错误。
原因我明白，就是该字段是数值
EXEC cypqgoodsbuyd 16655 这样执就OK
但我想用存储过程 如最后直接的SQL语句一样想一次多查几个货品，如何实现
- SQL code
CREATE PROCEDURE cypQgoodsBuyD
--按货品代号列出这个货品的采购详细记录
@goodsid VARCHAR(1000)
AS
SELECT CVS.code, b.BILLDATE, a.QTY,A.PRICES,
d.[NAME],C.VENDORNO,
C.SHORTNAME, C.CONTACTOR,C.PHONE,C.MOBILEPHONE, C.FAX FROM
(SELECT GOODSID, QTY,cast(PRICE as decimal(10,3)) PRICES,BILLID
FROM P_BILLD WHERE GOODSID in(@goodsid)) AS A
LEFT JOIN P_BILL as B ON b.BILLID=a.BILLID
LEFT JOIN VENDOR AS c ON c.VENDORID=b.VENDORID
LEFT JOIN TERM AS d ON d.TERMID=b.TERMID
LEFT JOIN cyVgoodsShort AS cvs ON CVS.GOODSID=A.GOODSID
直接的SQL
- SQL code
SELECT CVS.code, b.BILLDATE, a.QTY,A.PRICES,
d.[NAME],C.VENDORNO,
C.SHORTNAME, C.CONTACTOR,C.PHONE,C.MOBILEPHONE, C.FAX FR[code=SQL]
(SELECT GOODSID, QTY,cast(PRICE as decimal(10,3)) PRICES,BILLID
FROM P_BILLD WHERE GOODSID in(16655,19266,3642,13163,3014,3643,19498,3644)) AS A
LEFT JOIN P_BILL as B ON b.BILLID=a.BILLID
LEFT JOIN VENDOR AS c ON c.VENDORID=b.VENDORID
LEFT JOIN TERM AS d ON d.TERMID=b.TERMID
LEFT JOIN cyVgoodsShort AS cvs ON CVS.GOODSID=A.GOODSID
[/code]
------解决方案--------------------
- SQL code
动态执行
exec(
'SELECT CVS.code, b.BILLDATE, a.QTY,A.PRICES,
d.[NAME],C.VENDORNO,
C.SHORTNAME, C.CONTACTOR,C.PHONE,C.MOBILEPHONE, C.FAX FR OM
(SELECT GOODSID, QTY,cast(PRICE as decimal(10,3)) PRICES,BILLID
FROM P_BILLD WHERE GOODSID in('+@goodsid+')) AS A
LEFT JOIN P_BILL as B ON b.BILLID=a.BILLID
LEFT JOIN VENDOR AS c ON c.VENDORID=b.VENDORID
LEFT JOIN TERM AS d ON d.TERMID=b.TERMID
LEFT JOIN cyVgoodsShort AS cvs ON CVS.GOODSID=A.GOODSID')
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
