如何将多个值传递给从VBA运行的存储过程参数？ [英] How to pass multiple values to stored procedure parameter that running from VBA?

问题描述

我遇到了情况。我已经创建了VBA，它使用2个参数调用SP。如何将2个值传递给每个参数？

以下代码为ex，将单个值传递给每个参数：

  cmd.Parameters.Append cmd.CreateParameter（@ Class，adVarChar，adParamInput，40）
 cmd.Parameters（@ Class）。Value =H 
 
 
 cmd.Parameters.Append cmd.CreateParameter（@ Color，adVarChar，adParamInput，40）
 cmd.Parameters（@ Color）Value =Black 
  

现在我想通过M，黄色也。我该如何实现？
我还需要对SP进行任何更改。请帮助。

提前感谢

解决方案

问题是存储过程只需要传递一个值，而不是一系列的值。如果SQL允许一个IN子句传递一个像这样的值的数组，那么你将会生效：

  select * from mytable在@parameters中的id 
  

但不幸的是它不会这样不起作用。您可以只是消除存储过程，只是拼凑在一起的SQL字符串，这是一个SQL注入风险，但如果您的代码只在内部使用，那将不会是一个担心。如在注释中提到的，如果要使用SP as-is，那么您可能只需多次运行查询，并将结果附加到本地列表/数组/记录集/无论您每次运行它。还有其他的想法，但是它们基本上都是相同的，因为你正在拼凑一个自定义的SQL字符串，这是比较复杂的，并没有真正使用SP参数，如他们设计的。

如何在存储过程SQL Server 2008中使用`IN`运算符传递字符串参数

存储过程中的SQL IN子句

I got situation. I have created VBA that calls SP with 2 parameters. How Can I pass 2 values to each parameter?

below code as ex, passing single value to each parameter:

cmd.Parameters.Append cmd.CreateParameter("@Class", adVarChar, adParamInput, 40)
cmd.Parameters("@Class").Value = "H"


cmd.Parameters.Append cmd.CreateParameter("@Color", adVarChar, adParamInput, 40)
cmd.Parameters("@Color").Value = "Black"

Now I want to pass "M","Yellow" also. How can I achieve this? Do I need to do any changes to SP also. Please help.

Thanks in advance

解决方案

The problem is that the stored procedure only expects one value to be passed, not a series of them. If SQL allowed an IN clause to be passed an array of values like this then you'd be in business:

select * from mytable where id in @parameters

But unfortunately it doesn't so that won't work. You could just eliminate the stored procedure and just cobble together a SQL string, which is a SQL injection risk, but if your code is only being used in-house then that won't be as big of a worry. As alluded to in the comments, if you want to use the SP as-is, then you might just have to run the query multiple times and append your results to a local list/array/recordset/whatever each time you run it. There are other ideas but they're all basically the same in that you're cobbling together a custom SQL string, which is more complex and not really using the SP parameters like they're designed.

How to pass string parameter with `IN` operator in stored procedure SQL Server 2008

SQL IN Clause In Stored Procedure

这篇关于如何将多个值传递给从VBA运行的存储过程参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！