这样的VB语句要求用存储过程实现

[erdong988]

rs.open "select zccalgs,xmbh,Zccalcondition from ##List_temp with(nolock) where cjftype='KDC' order by iorder",conn,3,2
do while not rs.eof
   conn.execute "update HSW_farorder set fquantity=isnull(fquantity,0) + (select isnull(sum(" & rs!zccalgs & "),0) from v_HSW_PurPrice_l where cPcode='" & strCcode & "' and cpriceitemcode='" & rs!xmbh & "' and  (" & rs!Zccalcondition & " )) where ccode='" & ccode & "'"
loop

这样的VB语句要求用存储过程实现，应该怎么写？

[Rotel-刘志东]

直接调用存储过程。

[erdong988]

[Quote=引用 2 楼 lzd_83 的回复:]
直接调用存储过程。
[/Quote]
存储过程应该怎么写啊？........搜索了一下，看到的都不是想要的........

[nevergu]

你确定 这个update 语句没有问题吗？怎么感觉怪怪的。

cpriceitemcode='" & rs!xmbh & "' and (" & rs!Zccalcondition & " ))

conn.execute "update HSW_farorder set fquantity=isnull(fquantity,0) + (select isnull(sum(" & rs!zccalgs & "),0) from v_HSW_PurPrice_l where cPcode='" & strCcode & "' and cpriceitemcode='" & rs!xmbh & "' and  (" & rs!Zccalcondition & " )) where ccode='" & ccode & "'"
loop

[nevergu]

写了一个。未测试。你自己测下吧。。传入2个参数


where cPcode='" & strCcode & "'
where ccode='" & ccode & "'

CREATE PROCEDURE test
 @strCcode varchar(5000),
 @ccode varchar(5000)
AS
DECLARE @zccalgs int
DECLARE @xmbh varchar(50)
Declare @Zccalcondition int

	


DECLARE Sourcedata_test INSENSITIVE CURSOR FOR
select zccalgs,xmbh,Zccalcondition from ##List_temp with(nolock) where cjftype='KDC' order by iorder
OPEN Sourcedata_test
FETCH NEXT FROM Sourcedata_test
INTO @zccalgs,@xmbh,@Zccalcondition
WHILE @@FETCH_STATUS = 0
BEGIN

update HSW_farorder set fquantity=isnull(fquantity,0) + (select isnull(sum(@zccalgs),0) from v_HSW_PurPrice_l where cPcode=@strCcode 
and cpriceitemcode=@xmbh and (@Zccalcondition)) where ccode=@ccode

FETCH NEXT FROM Sourcedata_test
INTO @zccalgs,@xmbh,@Zccalcondition
END


CLOSE Sourcedata_test
DEALLOCATE Sourcedata_test





GO

[dawugui]

一个传参的存储过程,参考如下:

B. 使用带有参数的简单过程
下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。

USE pubs
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'au_info' AND type = 'P')
   DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info 
   @lastname varchar(40), 
   @firstname varchar(20) 
AS 
SELECT au_lname, au_fname, title, pub_name
   FROM authors a INNER JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER JOIN titles t
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
   WHERE  au_fname = @firstname
      AND au_lname = @lastname
GO

au_info 存储过程可以通过以下方法执行：

EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

如果该过程是批处理中的第一条语句，则可使用：

au_info 'Dull', 'Ann'
-- Or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'

[nevergu]

能把你的


update HSW_farorder set fquantity=isnull(fquantity,0) + (select isnull(sum(" & rs!zccalgs & "),0) from v_HSW_PurPrice_l where cPcode='" & strCcode & "' and cpriceitemcode='" & rs!xmbh & "' and (" & rs!Zccalcondition & " )) where ccode='" & ccode & "'

这句输出一个给我看看吗？ and (" & rs!Zccalcondition & " )) 这个地方有点奇怪

[幸运的意外]

把select部分写成存储过程就是了。

[erdong988]

问题已解决
把 7 楼的那句改为先拼接 再exec执行，调试一番终于搞定了
rs!Zccalcondition 是个条件参数，默认的是 1=1。
多谢 nevergu ，多谢各位！

PS：
用字符串连接SQL语句并用EXEC执行时，出现名称'...'不是有效的标识符
---------------写 exec (@sql) 就不会出错了！

[erdong988]

[Quote=引用 5 楼 nevergu 的回复:]
写了一个。未测试。你自己测下吧。。传入2个参数

[/Quote]
检查的时候提示这一句有错误：')' 附近有语法错误。(最后一个半括号)
看不出错在哪里...

update HSW_farorder set fquantity=isnull(fquantity,0) + (select isnull(sum(@zccalgs),0) from v_HSW_PurPrice_l where cPcode=@strCcode 
and cpriceitemcode=@xmbh and (@Zccalcondition)) where ccode=@ccode