执行存储过程并返回结果集 [英] Execute stored procedure and return resultset

问题描述

我是一个完整的 VBScript 新手，我正在尝试执行一个存储过程并读取结果集.我使用在线文章尝试了多种不同的方法，但没有任何效果，我很难过.数据库是 SQL Server 2008 R2，应用程序是现成的 ERP 系统，但我可以向其中添加自己的代码.

I'm a complete VBScript newbie and I am trying to execute a stored procedure and read the resultset. I have tried numerous different approaches using articles online but nothing works and I'm stumped. The database is SQL Server 2008 R2, the application is an off-the-shelf ERP system but I'm able to add my own code to it.

我可以通过以下方式执行代码:

I can execute code by using:

connection.execute"insert into blah blah blah"

我可以使用以下方法读取结果集:

And I can read result set by using:

Set objRS = CreateObject("ADODB.Recordset")

objRS.Open "select a, b, c FROM blahblah", Connection, adOpenStatic, adLockBatchOptimistic,    adCmdText
If objRS.EOF = False Then
    a = objRS.Fields("a").Value
    b = objRS.Fields("b").Value
    c = objRS.Fields("c").Value
End If
objRS.Close

有问题的存储过程实际上是一个选择语句，例如:

The stored procedure in question is in effect a select statement e.g.:

create procedure [dbname].[dbo].[sptestproc] 
as 
    @Option1 Varchar(10) = NULL,
    @Option2 Varchar(10) = NULL
AS
BEGIN
    select first, second 
    from table1 
    where a = @option1 and b = @toption2
End

到目前为止我的代码:

Dim sql

sql = "EXEC [dbname].[dbo].[sptestproc] '" & Opt1 & "','" & Opt2 & "'"
Set RS = CreateObject("ADODB.Recordset")
RS.Open sql, Connection, adOpenStatic, adLockBatchOptimistic, adCmdText
Do While Not RS.EOF
    Call App.MessageBox("first",vbInformation,"Data updated")
    Call App.MessageBox("second",vbInformation,"Data updated")
    RS.MoveNext
Loop

但我终生无法获得一个程序来执行和读取结果.

But I cannot for the life of me get a procedure to execute and read the results.

有人可以帮忙吗?

谢谢

推荐答案

adCmdText 用于 SQL 查询，如果你想执行一个存储过程，那么你必须使用 adCmdStoredProc (取而代之的是4)

adCmdText would be for SQL query if you want to execute a stored procedure then you have to use adCmdStoredProc (value 4 instead)

'Set the connection
'...............

'Set the command
DIM cmd
SET cmd = Server.CreateObject("ADODB.Command")
SET cmd.ActiveConnection = Connection

'Set the record set
DIM RS
SET RS = Server.CreateObject("ADODB.recordset")

'Prepare the stored procedure
cmd.CommandText = "[dbo].[sptestproc]"
cmd.CommandType = 4  'adCmdStoredProc

cmd.Parameters("@Option1 ") = Opt1 
cmd.Parameters("@Option2 ") = Opt2 

'Execute the stored procedure
SET RS = cmd.Execute
SET cmd = Nothing

'You can now access the record set
if (not RS.EOF) THEN
    first = RS("first")
    second = RS("second")
end if

'dispose your objects
RS.Close
SET RS = Nothing

Connection.Close
SET Connection = Nothing

这篇关于执行存储过程并返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！