当您需要检查多个值时，如何按位置传递参数值？

I created a stored procedure (spBalanceRange) with 2 optional parameters. They've been set to a default value and the sp works fine when I pass only 1 value per parameter by position. However, I have a situation where I'm trying to pass, by position, two strings immediately followed by a wildcard. I want the user to be able to search for Vendor names that start with either 'C%' or 'F%'. Here's the gist of the CREATE PROC statement:

我创建了一个带有2个可选参数的存储过程(spBalanceRange)。它们被设置为默认值,当我按位置每个参数只传递1个值时,sp工作正常。但是,我有一种情况,我试图按位置传递两个字符串,然后紧跟一个通配符。我希望用户能够搜索以“C%”或“F%”开头的供应商名称。这是CREATE PROC声明的要点:

CREATE PROC spBalanceRange 
    @VendorVar varchar(40) = '%',
    @BalanceMin money = 1.0

...

Here's what I've tried so far, but doesn't work:

这是我到目前为止尝试过的,但不起作用:

EXEC spBalanceRange '(C%|F%)', 200.00;
EXEC spBalanceRange 'C%|F%', 200.00;

Is there a way to check for 2 or more string values with a wildcard when passed by position? Thanks.

有没有办法在按位置传递时使用通配符检查2个或更多字符串值?谢谢。

2 个解决方案

#1

EDIT: According to your comments you are looking for the first letter of a vendor's name only.

In this special case I could suggest an easy, not well performing but really simple approach. CHARINDEX returns a number greater than zero, if a character appears within a string. So you just have to pass in all your lookup-first-characters as a simple "chain":

在这个特殊情况下,我可以建议一个简单,不好的但非常简单的方法。如果字符出现在字符串中,CHARINDEX将返回大于零的数字。因此,您只需将所有查找优先字符作为简单的“链”传递:

DECLARE @DummyVendors TABLE(VendorName VARCHAR(100));
INSERT INTO @DummyVendors VALUES
 ('Camel Industries')
,('Fritz and Fox')
,('some other');

DECLARE @ListOfFirstLetters VARCHAR(100)='CF';

SELECT VendorName
FROM @DummyVendors AS dv
WHERE CHARINDEX(LEFT(dv.VendorName,1),@ListOfFirstLetters)>0

This was the former answer

Checking against more than one value needs either a dedicated list of compares

检查多个值需要专用的比较列表

WHERE val=@prm1 OR val=@prm2 OR ... (you know the count before)

...or you use the IN-clause

...或者您使用IN子句

WHERE LEFT(VenoderName,1) IN ('C','F', ...)

...but you cannot pass the IN-list with a parameter like ... IN(@allValues)

...但是你不能用像...这样的参数传递IN列表(@allValues)

You might think about a created TYPE to pass in all your values like a table and use an INNER JOIN as filter: https://*.com/a/337864/5089204 (and a lot of other examples there...)

您可能会想到创建的TYPE传递所有值,如表格,并使用INNER JOIN作为过滤器:https://*.com/a/337864/5089204(还有许多其他示例...)

Or you might think of dynamic SQL: https://*.com/a/5192765/5089204

或者你可能会想到动态SQL:https://*.com/a/5192765/5089204

And last but not least you might think of one of the many split string approaches. This is one of my own answers, section "dynamic IN-statement": https://*.com/a/33658220/5089204

最后但并非最不重要的是,您可能会想到许多分裂字符串方法中的一种。这是我自己的答案之一,“动态IN语句”部分:https://*.com/a/33658220/5089204

#2

I'm answering my own question, and maybe other solutions exist but here is what had to happen with my stored procedure in order to pass variables by position:

我正在回答我自己的问题,也许存在其他解决方案但是这里是我的存储过程必须发生的事情,以便按位置传递变量:

CREATE PROC spBalanceRange
@VendorVar varchar(40) = '%',
@BalanceMin money = 1.0
AS
IF (@VendorVar = '%' AND @BalanceMin IS NULL OR @BalanceMin = '')
BEGIN
  PRINT 'BalanceMin cannot be null.';
END
IF (@VendorVar = % AND @BalanceMin IS NOT NULL)
BEGIN
  (sql statement using parameters)
END

EXEC spBalanceRange '[C,F]%', 200.00;

That's what I know.

这就是我所知道的。