在Access中使用SQL Server存储过程的输出参数

Question

我将尝试从Access 2013 VBA执行SQL Server 2008存储过程，并获取输出变量(新的身份列)。但是，我并不完全了解所有adodb内容，并且我已经尝试了许多不同文章中的方法，但都没有成功。以下是目前的情况...

存储过程：

PROCEDURE [proc_NewClient]
    @Type INT, 
    @PhoneIntakeID INT,
    @ClientID INT = NULL,
    @NewPSID INT = null OUTPUT (2 possible ways to call the proc, one does not produce an output variable)

   INSERT INTO tblClientDetails (ClientID, PhoneIntakeID, Client_Status) 
   VALUES (@ClientID, @PhoneIntakeID, 'Applicant')

   DECLARE @NewClientDetailID int
   SELECT @NewClientDetailID = SCOPE_IDENTITY()

   INSERT INTO tblPS_Psychosocial (ClientDetailID, Client) 
   VALUES (@NewClientDetailID, @ClientID)

   SELECT @NewPSID = SCOPE_IDENTITY()

   INSERT INTO tblPS_AbuseHistory (PsychosocialID) 
   VALUES (@NewPSID)

   INSERT INTO tblPS_FamilyHistory(PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_FinancialHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_LegalHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_MedicalHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_PsychiatricHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_SocialHistory (PsychosocialID)  VALUES (@NewPSID)
   INSERT INTO tblPS_SpiritualHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_SubstanceHistory (PsychosocialID) VALUES (@NewPSID)

   INSERT INTO tblVocAssessment(ClientDetailID) VALUES (@NewClientDetailID)

我从表单中得到的信息：

    Dim cnn As ADODB.Connection
            Dim cmd As New ADODB.Command, rs As New ADODB.Recordset, param1 As New ADODB.Parameter, param2 As New ADODB.Parameter, param3 As New ADODB.Parameter, param4 As New ADODB.Parameter
            Set cnn = New ADODB.Connection
            cnn.ConnectionString = "DRIVER=SQL Server;SERVER=SRV-DB01;DATABASE=TWHClientMgmt;Trusted_Connection=Yes"

            cnn.Open cnn.ConnectionString

            Set cmd = New ADODB.Command
            cmd.ActiveConnection = cnn
            cmd.CommandType = adCmdStoredProc
            cmd.CommandText = "[THEWOMENSHOME\jboyd].proc_NewClient"


            Set param1 = cmd.CreateParameter("@Type", adinteger, adparamInput, , 2)
            cmd.Parameters.Append param1
            Set param2 = cmd.CreateParameter("@PhoneIntakeID", adinteger, adparamInput, , Me.PhoneIntakeID)
            cmd.Parameters.Append param2
            Set param3 = cmd.CreateParameter("@ClientID", adinteger, adparamInput, , intNewID)
            cmd.Parameters.Append param3
            Set param4 = cmd.CreateParameter("@NewPSID", adinteger, adParamOutput)
            cmd.Parameters.Append param4

rs.open cmd

到这里为止，代码可以正常工作，新的记录被生成等等。当我从SQL Server运行存储过程时，它返回了正确的自增列号，但我已经尝试多种方式在VBA中引用输出，但最终结果总是空值。如何正确地在这里引用它？

- NPO_Coder

---

请尝试使用cmd.Execute代替rs.Open，然后使用cmd.Parameters("@NewPSID").Value来检索输出参数的值。注意不要改变原意，让内容更易懂。 - Gord Thompson

您还可以使用新的ID数据使SP返回一个带有一行/一列记录集，并使用透传查询中的select子句读取此数据。 - Sergey S.

@GordThompson 谢谢您！我之前尝试了这个组合，但参数中没有 .value，那就是原因所在！！现在它完美的运行了！ - NPO_Coder

@SergeyS。我该如何编写类似的穿透查询，既执行插入又返回值？我只需将返回记录设置为true，提供所有参数并以select结束存储过程就可以执行吗？在VBA中如何引用该值？如果这样可以工作，那么它似乎比我一直在做的更简单。 - NPO_Coder

使用返回结果集并使用透传查询调用存储过程的折衷方案是，透传查询无法进行参数化，因此您必须每次使用动态SQL来“拼接”EXEC语句。因此，您需要对所有输入进行转义（“消毒”），正确引用它们（包括''或N''，取决于字符串的类型），等等。 - Gord Thompson

是的，只需在过程末尾使用带有选择的程序，在 VBA 代码中使用常规的 OpenRecordset 进行查询定义。将查询定义的“sql”属性设置为 select <stored_procedure(parameters) as ret> 或先传递查询名称。是的，您无法对此类查询进行参数化处理。 - Sergey S.

Answer 1

要检索存储过程的OUTPUT参数的值，只需执行ADODB.Command并检索相应的ADODB.Parameter的.Value，如下所示：

cmd.Execute
' retrieve and display the returned OUTPUT parameter value
Debug.Print cmd.Parameters("@NewPSID").Value

- Gord Thompson

---

在 .CommandType = adCmdText 模式下，是否可以检索输出参数？ - Muflix

在 Rs = cmd.Execute() 之后，您还可以使用它来返回输出参数值。 Debug.Print param4 - Kelvin Ng

Answer 2

调用存储过程的简单DAO解决方案：

Public Function RunSqlQuery(q As String) As Variant

Dim cdb As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As Recordset

Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("dbo_Setup").Connect  ' your attached table
qdf.SQL = q
qdf.ReturnsRecords = True
Set rs = qdf.OpenRecordset()
RunSqlQuery = rs.Fields(0)  ' you also can output more than one parameter from a stored procedure as fields
rs.Close
Set rs = Nothing
Set qdf = Nothing
Set cdb = Nothing

End Function

现在，调用函数，以与在 SQL 服务器上执行查询相同的方式定义查询：

? RunSQLQuery("DECLARE @param1 varchar(16);   exec proc_MyStoredProcedure @param1 OUTPUT;  SELECT @param1")

- Zoran Hrvoic