Passing Multiple Choices to a Stored Procedure

Question

Passing Multiple Choices to a Stored Procedure

saintor1

Mr or Mrs. 500

Points: 562
More actions
January 3, 2023 at 8:26 pm
Go to Answer
#4133189
In my stored procedure, I have something like
```
WHERE W.Warehouse=@WH1
```
It works well when it is simple as
```
EXEC dbo.MyProcedure @WH1='S0'
```
I need to select multiple warehouses. For example.
```
EXEC dbo.MyProcedure @WH1='S0' OR W.Warehouse='T0' OR W.Warehouse='T0' OR W.Warehouse='Z0'
```
I would expect that the prefix of first warehouse would be handled in the procedure so after the WHERE clause, it should work as
```
W.Warehouse='S0' OR W.Warehouse='T0' OR W.Warehouse='T0' OR W.Warehouse='Z0'
```
However I have at least a syntax problem to define my @WH1 variable properly, and I can't put my finger on it. I am not even sure that I am using the right method. How should it be done? TIA.
autoexcrement

SSCertifiable

Points: 5956
More actions
January 3, 2023 at 9:35 pm
Answer
#4133226
None of us have any idea what your stored procedure or your tables look like. But here's an idea:
```
DECLARE @MyList VARCHAR(MAX);

SET @MyList = 'S0,T0,t0,W0,';

WITH CTE AS
(
SELECT Warehouse = [value] 
FROM STRING_SPLIT(@MyList, ',')
WHERE LEN([value])>0
)
SELECT W.* 
FROM Warehouses AS W
INNER JOIN CTE ON W.Warehouse = CTE.Warehouse
;
```
"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Be cautious about building this kind of catch-all query. Here's some great advice from Gail Shaw on the topic. Note that she has multiple posts on the topic. It's a somewhat dangerous performance hole you might be preparing to crawl down.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 2

STRING_SPLIT saves the day.... great. Thank you.

WHERE W.Warehouse IN (SELECT value FROM STRING_SPLIT(@WH1, ',') WHERE LEN([value])>0)

ratbak Ten Centuries Points: 1358 More actions · Answer 3

STRING_SPLIT() is great. But be aware of potential performance impacts of using a table-valued function or variable directly -- You may find it better to insert the values from the STRING_SPLIT() into a temp table, & then use that. Test & see which is better.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

saintor1 wrote:

In my stored procedure, I have something like
WHERE W.Warehouse=@WH1
It works well when it is simple as
EXEC dbo.MyProcedure @WH1='S0'
I need to select multiple warehouses. For example.
EXEC dbo.MyProcedure @WH1='S0' OR W.Warehouse='T0' OR W.Warehouse='T0' OR W.Warehouse='Z0'
I would expect that the prefix of first warehouse would be handled in the procedure so after the WHERE clause, it should work as
W.Warehouse='S0' OR W.Warehouse='T0' OR W.Warehouse='T0' OR W.Warehouse='Z0'
However I have at least a syntax problem to define my @WH1 variable properly, and I can't put my finger on it. I am not even sure that I am using the right method. How should it be done? TIA.

Quick question, how many options will you be passing to the procedure?

😎

Further to Grant's fine comment, do you need a catch-all or can you do this with a lookup table?

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 5

The number of options is up to 7. I build a string based using check boxes in my sheet, so it will be consistent.