SQL 将字符串作为字符串传递给 CreateParameter 的正确方法

correct way to pass as string to CreateParameter

提问by Roland

If I call a stored procedure by

如果我调用存储过程

DECLARE @return_value int

EXEC @return_value = [dbo].[GetValueProc]
  @startDate = '1/1/2010',
  @endDate = '12/31/2010',  
  @groupNo = N'02'
SELECT 'Return Value' = @return_value

From sql command window it works great, but using VB6 it returns an empty record set.

从 sql 命令窗口它工作得很好，但使用 VB6 它返回一个空记录集。

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = cn

cmd.CommandText = "GetValueProc"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@startDate", adDate, adParamInput, 0, startDate)
cmd.Parameters.Append cmd.CreateParameter("@endDate", adDate, adParamInput, 0, endDate)
cmd.Parameters.Append cmd.CreateParameter("@groupNo", adVarChar, adParamInput, 3, "02")

Set RstRecordSet = New ADODB.Recordset
With RstRecordSet        
  .CursorType = adOpenStatic
  .CursorLocation = adUseClient
  .LockType = adLockOptimistic
  .Open cmd
End With

I get an empty Record set from vb. However, from sql command window it works as expected.

我从 vb 得到一个空的记录集。但是，从 sql 命令窗口它按预期工作。

Note: If I comment out the line with the parameter in question it also works as expected.

注意：如果我注释掉带有相关参数的行，它也会按预期工作。

the stored procedure looks like:

存储过程如下所示：

ALTER PROCEDURE [dbo].[GetValueProc] 
@startDate as date,
@endDate as date,
@proposalNo nvarchar(30) = null, 
@groupNo nvarchar(3) = null

select...
where ...
   and j.[Global Dimension 1 Code] = COALESCE(@groupNo,[Global Dimension 1 Code])

[Global Dimension 1 Code] is a varchar(20)

【全局维度1代码】是一个varchar(20)

I have also tried

我也试过

cmd.Parameters.Append cmd.CreateParameter("@groupNo", adVarChar, adParamInput, 4, "'02'")

doesn't help.

没有帮助。

Any help about the correct way to pass the parameter would be great!

任何有关传递参数的正确方法的帮助都会很棒！

Thanks

谢谢

回答by onedaywhen

I note you are omitting the @proposalNoparameter in the call. Therefore, I think you need to flag that you are using named parameters, otherwise they will be assumed to be in ordinal position order i.e. @groupNoin the call is the third in the Parameterscollection and therefore will be used for the third param in the stored proc which is actually @proposalNo.

我注意到您@proposalNo在调用中省略了参数。因此，我认为您需要标记您正在使用命名参数，否则它们将被假定为顺序位置顺序，即@groupNo在调用中是Parameters集合中的第三个，因此将用于存储过程中的第三个参数实际上是@proposalNo。

Try inserting this line

尝试插入这一行

cmd.NamedParameters = True

immediately before the cmd.Parameters.Append...lines. Note only the later versions of ADO (e.g. 2.8) support named parameters.

就在行之前cmd.Parameters.Append...。请注意，只有更高版本的 ADO（例如 2.8）才支持命名参数。

Here's a repro:

这是一个再现：

First, change your stored proc to simply select the parameters e.g.

首先，更改您的存储过程以简单地选择参数，例如

ALTER PROCEDURE [dbo].[GetValueProc] 
@startDate as date,
@endDate as date,
@proposalNo nvarchar(30) = null, 
@groupNo nvarchar(3) = null
AS
BEGIN
SELECT @startDate, @endDate, @proposalNo, @groupNo;
END;

Second, try this VBA (need to edit it for your connection string) which corrects your typos:

其次，试试这个 VBA（需要为你的连接字符串编辑它），它可以纠正你的错字：

Sub hngorhio()
  Dim cmd As New ADODB.Command
  Set cmd = New ADODB.Command
  cmd.ActiveConnection = "Your connection string here"

  cmd.CommandText = "GetValueProc"
  cmd.CommandType = adCmdStoredProc
  cmd.NamedParameters = True  ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< '
  cmd.Parameters.Append _
      cmd.CreateParameter("@startDate", adDate, adParamInput, 0, Date)
  cmd.Parameters.Append _
      cmd.CreateParameter("@endDate", adDate, adParamInput, 0, Date + 1)
  cmd.Parameters.Append _
      cmd.CreateParameter("@groupNo", adVarChar, adParamInput, 3, "02")

  Dim RstRecordSet As New ADODB.Recordset
  Set RstRecordSet = New ADODB.Recordset
  With RstRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmd
    If Not .EOF Then
      MsgBox .GetString(, , , , "<NULL>")
    End If
  End With
End Sub

If you comment and uncomment the NamedParameterslines you'll see the 02and <NULL>switch places in the messagebox, proving that the SQL engine is uses ordinal position of parameters when NamedParametersis not enabled.

如果您注释和取消注释这些NamedParameters行，您将在消息框中看到02和<NULL>切换位置，证明 SQL 引擎在NamedParameters未启用时使用参数的顺序位置。

UPDATED:

更新：

Perhaps this stored proc gives a more explicit result with the same VBA code above:

也许这个存储过程使用上面相同的 VBA 代码给出了更明确的结果：

ALTER PROCEDURE [dbo].[GetValueProc] 
@startDate as date,
@endDate as date,
@proposalNo nvarchar(30) = null, 
@groupNo nvarchar(3) = null
AS
BEGIN
SELECT '@proposalNo = ' + COALESCE(@proposalNo, '<NULL>') + CHAR(10) 
       + '@groupNo = ' + COALESCE(@groupNo, '<NULL>');
END;