时间戳

首页 > 解决方案 > 使用 Procedure 子句调用 SQL 存储过程

sql-server - 使用 Procedure 子句调用 SQL 存储过程

问题描述

我正在尝试为 IT 设备库存建立一个数据库。它使用 MS Access Office 365 前端和 SQL 2017 后端。

在数据库中，我们不想删除记录，只需将它们归档到另一个表中。为此，我在 SSMS 中创建了一个存储过程，并验证它是否正确地完成了这项工作。

我希望 VBA 调用这个存储过程。对于这个过程，我需要传递它的识别信息。在 VBA 中，我试图将服务器名称值从表单分配给可以传递给存储过程调用的变量。我找到了使用该命令的示例，EXEC但 Access 告诉我必须使用该Procedure子句。

Private Sub Command148_Click()
    Dim SrvNameVar As String
    Dim strSQL As String
    Dim strParm As String
    SrvNameVar = Me.SrvName
    strParm = "PARAMETERS [Server Name] As CHAR;"

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Set dbs = CurrentDb
    strSQL = strParm & "PROCEDURE dbo.sp_ArchiveServer [Server Name];"
    Set qdf = dbs.CreateQueryDef("SrvArchive", strSQL)

    dbs.Execute ("SrvArchive")
End Sub

在 SSMS 中正常运行的存储过程：

CREATE PROCEDURE sp_ArchiveServer @Server nvarchar(30) AS
   BEGIN TRANSACTION;
   INSERT INTO FSC.dbo.Archive_Servers ([SrvID],[SID],[SrvName],[Make],
            [Model],[SN],[SrvIP],[RemoteMgmt],[OSID],[IsDP],[IsIEMRelay],
            [IsGUP],[DatePurch],[WarrantyExp],[RAIDConfig],[PrintSrv],
            [ConnectedToUPS],[VirtHost],[VirtMachine])
   SELECT FSC.dbo.Servers.*
   FROM FSC.dbo.Servers
   WHERE FSC.dbo.Servers.SrvName = @Server;

   DELETE FROM FSC.dbo.Servers
   WHERE FSC.dbo.Servers.SrvName = @Server;

   COMMIT;

标签： sql-servervbams-accessstored-procedures

解决方案

目前，您将 MS Access SQL 方言与 SQL Server 方言混为一谈。只有 MS Access SQL 查询支持PARAMETERS. 但是，您正在尝试运行 SQL Server 查询，特别是为了执行存储过程。

MS Access 确实允许对后端数据库进行直通查询，因此您可以调整 QueryDef（默认为 Access 后端）以连接到 MSSQL 数据库，然后运行EXEC命令。所有直通查询都应符合后端的 SQL 方言。

Private Sub Command148_Click()
   Dim dbs As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim SrvNameVar, strSQL As String

   SrvNameVar = Me.SrvName
   strSQL = "EXEC dbo.sp_ArchiveServer @Server='" & SrvNameVar &"'"

   Set dbs = CurrentDb
   Set qdf = dbs.CreateQueryDef("SrvArchive")

   ' ASSIGN ODBC DSN CONNECTION
   qdf.Connect = "ODBC; DATABASE=database; UID=user; PWD=password; DSN=datasourcename;" 
   qdf.SQL = strSQL
   qdf.Execute
End Sub

为了有效地使用参数化，请考虑使用不同的 API，即ADO（可扩展到任何后端数据库）而不是 DAO（更适合 Access 数据库）。

Private Sub Command148_Click()
   ' SET REFERENCE TO Microsoft ActiveX Data Object #.# Library
   Dim conn As ADODB.Connection, cmd As ADODB.Command
   Dim SrvNameVar As String

   SrvNameVar = Me.SrvName

   ' OPEN DRIVER OR DSN CONNECTION
   Set conn = New ADODB.Connection         
   conn.Open "DRIVER={SQL Server};server=servername;database=databasename;UID=username;PWD=password;"
   ' conn.Open "DSN=datasourcename"

   ' OPEN AND DEFINE COMMAND OBJECT
   Set cmd = New ADODB.Command     
   With cmd
       .ActiveConnection = conn
       .CommandText = "sp_ArchiveServer"
       .CommandType = adCmdStoredProc

       ' BIND PARAMETERS BY POSITION AND NOT NAME
       .Parameters.Append .CreateParameter("param1", adVarchar, adParamInput, 255, SrvNameVar)
       .Execute
   End With

   conn.close()
   Set cmd = Nothing: Set conn = Nothing
End Sub

推荐阅读