让ADO为临时存储过程生成参数？

I'm trying to get ADO to recognize parameters from a stored procedure on SQL Sever. If I do this with a normal stored procedure, it works fine:

我正在尝试让ADO识别SQL Sever上存储过程的参数。如果我使用普通的存储过程执行此操作，它可以正常工作：

conn.Execute "create proc NormalSP (@i int output) as set @i = 3"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = conn
cmd.CommandText = "NormalSP"
cmd.Parameters.Refresh 'parameters list now has @RETURN_VALUE and @i
cmd.Execute
Debug.Print cmd("@i")

But if I try the same thing with a temporary stored procedure, it can't get the parameters:

但是，如果我使用临时存储过程尝试相同的操作，则无法获取参数：

conn.Execute "create proc #TempSP (@i int output) as set @i = 3"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = conn
cmd.CommandText = "#TempSP"
cmd.Parameters.Refresh 'parameters list remains empty
cmd.Execute 'error: command expects parameter '@i', which was not supplied
Debug.Print cmd("@i") 'error: item cannot be found in the collection

What more do I need to get Parameters.Refresh to work with a temp SP as it does with a normal SP?

我还需要什么来使用Parameters.Refresh来处理临时SP，就像使用普通SP一样？

3 个解决方案

#1

Asking two black boxes (legacy ADO, the OLEDB/ODBC provider) why they don't like your interpretation of what context should expose is not likely to provide a very satisfactory answer, but since the original intent is unclear...

问两个黑盒子（传统ADO，OLEDB / ODBC提供商）为什么他们不喜欢你对上下文应该暴露的解释不太可能提供一个非常令人满意的答案，但由于最初的意图不明确......

Assuming you can accept a short-lived, global temporary stored procedure to ask it for temporary parameters (and in contradiction to what is presumably your downvote above), just connect to the right db in the first place:

假设您可以接受一个短暂的全局临时存储过程来询问它的临时参数（并且与上面的推文相反），只需首先连接到正确的数据库：

Complete, repeat-safe sample (using SQL 2012 native client):

完整，重复安全的示例（使用SQL 2012本机客户端）：

Public Sub DoITemporarilyHaveParametersForADO()

    Dim conn    As ADODB.Connection
    Dim cmd     As ADODB.Command
    Dim prm     As ADODB.Parameter

    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command

    conn.ConnectionString = "Driver={SQL Server Native Client 11.0};Server=(local);Database=tempdb;Trusted_Connection=yes;"
    conn.CursorLocation = adUseServer
    conn.Open

    conn.Execute "IF OBJECT_ID('TempSP') IS NOT NULL DROP PROC TempSP"
    conn.Execute "create proc TempSP (@i int output) as set @i = 3;"
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "TempSP"
    cmd.Parameters.Refresh
    For Each prm In cmd.Parameters
        Debug.Print prm.Name & ": " & prm.Value
    Next prm
    Debug.Print "cmd.Parameters.Count: " & cmd.Parameters.Count
    cmd.Execute
    Debug.Print cmd("@i")

    conn.Execute "IF OBJECT_ID('TempSP') IS NOT NULL DROP PROC TempSP"
    conn.Close

    Set prm = Nothing
    Set cmd = Nothing
    Set conn = Nothing

End Sub

Immediate Window output:

立即窗口输出：

@RETURN_VALUE: 
@i: 
cmd.Parameters.Count: 2
 3

Some useful clarification from MSFT:

MSFT的一些有用说明：

Temporary stored procedures are useful when connecting to earlier versions of SQL Server that do not support the reuse of execution plans for Transact-SQL statements or batches. Applications connecting to SQL Server 2000 and higher should use the sp_executesql system stored procedure instead of temporary stored procedures.

连接到不支持重用Transact-SQL语句或批处理的执行计划的早期版本的SQL Server时，临时存储过程很有用。连接到SQL Server 2000及更高版本的应用程序应使用sp_executesql系统存储过程而不是临时存储过程。

....

If a stored procedure not prefixed with # or ## is created directly in the tempdb database, the stored procedure is automatically deleted when SQL Server is shut down because tempdb is re-created every time SQL Server is started. Procedures created directly in tempdb exist even after the creating connection is terminated.

如果直接在tempdb数据库中创建了不带＃或##前缀的存储过程，则在SQL Server关闭时会自动删除存储过程，因为每次启动SQL Server都会重新创建tempdb。即使在创建连接终止后，也会在tempdb中直接创建过程。

Meaning of course that if you throw a breakpoint into that VBA sample round about line 27, flip over to SSMS, connect to (local), switch to tempdb, and EXEC TempSP, you will be told

当然，如果你在第27行的VBA示例中抛出一个断点，转到SSMS，连接到（本地），切换到tempdb和EXEC TempSP，你会被告知

Procedure or function 'TempSP' expects parameter '@i', which was not supplied.

过程或函数'TempSP'需要参数'@i'，这是未提供的。

instead of

代替

Could not find stored procedure 'TempSP'.

找不到存储过程'TempSP'。

before and after sample execution.

在样本执行之前和之后。

#2

Try making it a global temporary SP:

尝试将其设为全球临时SP：

conn.Execute "create proc ##TempSP (@i int output) as set @i = 3"
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "##TempSP"
cmd.Parameters.Refresh 'parameters list remains empty
cmd.Execute
Debug.Print cmd("@i")

#3

Put cmd.Parameters(1).Value = intYourValue before cmd.Execute

在cmd.Execute之前放入cmd.Parameters（1）.Value = intYourValue

#1