但小弟我想用存储过程　如最后直接的SQL语句一样想一次多查几个货品，怎么实现

但我想用存储过程　如最后直接的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 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]

OM  
(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') 

 

查看更多 下一篇