按以下执行存储过种程错误
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([email protected]+')) 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')