VBA调用sql server存储过程得不到正确的返回结果

superlsf · 发表于 2018-8-9 22:21

Sub 多参数的存储过程()
Dim i As Long
If conn Is Nothing Then Connect '一个连接数据库的过程
If conn.State = 0 Then conn.Open
' 在SQL Server Management Studio中执行以下存储过程正常，能返回很多记录
' execute dbo.proc_jggz @as_odr_no = :as_odr_no,
' @as_pur_no = :as_pur_no,
' @as_date_start = :as_date_start,
' @as_date_end = :as_date_end,
' @as_vendermid = :as_vendermid,
' @as_style = :as_style,
' @as_contract_no = :as_contract_no,
' @as_matno = :as_matno,
' @as_matsx = :as_matsx,
' @unit = :as_unit,
' @ls_baozhuang = :ls_baozhuang
cmd.commandText = "proc_jggz" '存储过程的名称
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
'cmd.Prepared = True
cmd.Parameters.Append cmd.CreateParameter("as_odr_no", adVarWChar, adParamInput, 16, "")
cmd.Parameters.Append cmd.CreateParameter("as_pur_no", adVarWChar, adParamInput, 256, "")
cmd.Parameters.Append cmd.CreateParameter("as_date_start", adVarWChar, adParamInput, 10, "2018-08-01")
cmd.Parameters.Append cmd.CreateParameter("as_date_end", adVarWChar, adParamInput, 10, "2018-08-08")
cmd.Parameters.Append cmd.CreateParameter("as_vendermid", adVarWChar, adParamInput, 8, "")
cmd.Parameters.Append cmd.CreateParameter("as_style", adVarWChar, adParamInput, 16, "")
cmd.Parameters.Append cmd.CreateParameter("as_contract_no", adVarWChar, adParamInput, 64, "")
cmd.Parameters.Append cmd.CreateParameter("as_matno", adWChar, adParamInput, 26, "")
cmd.Parameters.Append cmd.CreateParameter("as_matsx", adVarWChar, adParamInput, 1, "")
cmd.Parameters.Append cmd.CreateParameter("unit", adVarWChar, adParamInput, 8, "")
cmd.Parameters.Append cmd.CreateParameter("ls_baozhuang", adVarWChar, adParamInput, 8, "")
cmd.Execute
rst.Open cmd, , adOpenStatic, adLockBatchOptimistic
Sheet1.Cells.Clear
If Not rst.EOF Then
For i = 0 To rst.Fields.Count - 1
Sheet1.Cells(1, i + 1).Value = rst.Fields(i).Name
Next
Sheet1.Cells(2, 1).CopyFromRecordset rst
End If
rst.Close
conn.Close
Set rst = Nothing
Set cmd = Nothing
Set conn = Nothing
End Sub
存储过程【proc_jggz】有一句动态sql
set @sSQL = N'insert into #tab_jgmx (Pur_no, Sub_no, Pur_date, Pur_kind,Pur_lb,VendermId, Sg_no, Odr_no, Matno,Unit,Qty,Dj_mk,
Price, Pur_hb, Sjmoney,Note,deptno, Hqty,tqty, dqty, fact, jlzz, use_qty,contract_no,
dg_kind, bl_kind,style ,price_no,dgname,ciq_kind ,zafei,IsMY,Qty_ZP,Qty_BL,Qty_ZS,IsZpYb,chargemode
,Offset_YN,Offset_HgYsNo,PrintNum,PrintedBy,PrintedON)
SELECT Pur_no, Sub_no, Pur_date, Pur_kind,Pur_lb,VendermId, Sg_no, Odr_no, Matno,Unit,Qty,Dj_mk,
Price, Pur_hb, Sjmoney,Note,deptno, Hqty,tqty, dqty, fact, jlzz, use_qty,contract_no,
dg_kind, bl_kind,style ,price_no ,dgname,ciq_kind,zafei,IsMY,Qty_ZP,Qty_BL,Qty_ZS,IsZpYb,chargemode
,Offset_YN,Offset_HgYsNo,PrintNum,PrintedBy,PrintedON
from purd with (nolock)
where qty > 0
exec (@sSQL + @sWhere) '如果这句执行的话，vba中就得不到返回数据
如果直接运行以下形式的sql,则vba中就能得返回数据
insert into #tab_jgmx （XXXX）
Select ... From XXX Where ......

复制代码

		自动登录	找回密码
密码			注册