1

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:

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.

SammyPayne
  • 90
  • 11

2 Answers2

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":

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

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

...but you cannot pass the IN-list with a parameter like ... 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://stackoverflow.com/a/337864/5089204 (and a lot of other examples there...)

Or you might think of dynamic SQL: https://stackoverflow.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://stackoverflow.com/a/33658220/5089204

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • thanks for the detailed answers and links to more SQL. I probably should have been more detailed when I set up my answer but I was in a rush. Aaand, I can't post the whole procedure because the comments area is too short. In this case I could only pass variables by position within the DECLARE statement (it was for homework so I had to prove I could do it in this way). I couldn't search for characters in the WHERE clause, but what you provided makes sense. In my opinion, passing variables w/o assigning them explicitly seems like sloppy coding. – SammyPayne Feb 20 '16 at 05:33
1

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.

SammyPayne
  • 90
  • 11