Stored procedure using multi-valued optional parameter

Question

Stored procedure using multi-valued optional parameter

slsmithstone

SSC-Addicted

Points: 431
More actions
September 18, 2020 at 7:13 pm
Go to Answer
#3789540

Hello -
I have a stored procedure that has a multi-valued parameter that is optional. I cannot get it to work.
Below is a simplified version of it. Potential values for "Status" are Cleared, Submitted, Void, Open, Received.
Create procedure MyStoredProcedure
@status nvarchar(15) -- Potential Statuses are: Cleared, Submitted, Void, Open, Received
As
SELECT
Detail_Status AS [Status],
FROM
Details_Table
(various TABLE joins)
WHERE
Detail_Status IN (dbo.fnSplitStringList(@Status))
I am not sure the udf is correct. Also, I DO know I don't have it designated above correctly.
Any help on how to do this will be greatly appreciated!
Thanks!
juniormint
Andrew P

SSCarpal Tunnel

Points: 4649
More actions
September 22, 2020 at 12:08 am
Answer
#3789961
You don't want to be using "IN (SELECT PO_Detail_Status FROM dbo.DelimitedSplitN4K(@status,','))". You want it to be SELECTing the column that comes out from DelimitedSplitN4K.
Could you run the below and tell me what the column name is? Use that instead of PO_Detail_Status in the above text.
```
SELECT * FROM dbo.DelimitedSplitN4K('test',',')
```
For example, if the column returned from the split function is named "value", use the below statement. Note that I'm aliasing the output of the function so I can be explicit about where the column should be coming from, so I don't accidentally run into the same issue you're facing.
WHERE Details_Table.PO_Detail_Status IN (SELECT splitresults.value FROM dbo.DelimitedSplitN4K(@status,',') AS splitresults)
What your last query is doing, is checking whether the output of DelimitedSplitN4K contains a column named PO_Detail_Status, it doesn't, so it then looks for the PO_Details_Status reference in your outer objects. It finds it in Details_Table, so returns that column's value from the IN(...) statement, and then checks that against... the same column value. So what it's currently doing is equivalent to the below.
```
WHERE Details_Table.PO_Detail_Status IN (SELECT PO_Detail_Status WHERE @status IS NOT NULL)
```
Best,
Andrew P.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

Hi juniormint,

This is nearly there. To correct your procedure, you need to have a "SELECT <column name> FROM " in front of "dbo.fnSplitStringList(@Status)". The column name is what the column is called in the SELECT statement returned by the UDF, and you can see this by using "SELECT * FROM dbo.fnSplitStringList(@Status)".

You're using a length of 15, but you've said this can be multi-valued. A parameter length of 15 is long enough for only the two shortest statuses ("Void,Open" is 9 characters) and will error if I add a third value to that list, so you might want to make it a bit longer.

If your database has a database compatibility level of 130 or higher, and you can make the input separate the items in the list with a single-character ("," instead of ", "), then using "SELECT VALUE FROM STRING_SPLIT(@Status, ',')" should give better performance than a custom UDF.

Best,

Andrew P.

ScottPletcher SSC Guru Points: 101130 More actions · Answer 2

Btw, you're wasting bytes making that column nvarchar rather than varchar, since the only values possible don't include any non-standard chars.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

slsmithstone SSC-Addicted Points: 431 More actions · Answer 3

Hi Andrew -

Thank you so much for replying. I made your suggested changes and am getting closer --

The function I am using, however, is not working. SQL Server does not recognize it as a function.

I cannot use String_Split because I am running SQL Server 2011 and String_Split didn't come about until 2016.

Can you point me in the direction of a split string function that works?

Again, thank you for your reply and look forward to hearing from you!

juniormint

slsmithstone SSC-Addicted Points: 431 More actions · Answer 4

Thank you for your suggestion - I made change accordingly!

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 5

I use (and am grateful for) the string splitting function made by Jeff Moden on our pre-SQL 2016 instances. I suggest taking a look at his article on this here, with the download at the bottom of that article.

slsmithstone SSC-Addicted Points: 431 More actions · Answer 6

Hi Andrew-

Thanks for turning me on to Jeff Modon. Loved his article and sense of humor.

I corrected the length of the @status parameter to 250.

I put the following after the Where clause: PO_Detail_Status IN (SELECT PO_Detail_Status FROM dbo.DelimitedSplitN4K(@status,','))

It's still not working. What am I doing wrong?

juniormint

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

slsmithstone wrote:

Hi Andrew-
Thanks for turning me on to Jeff Modon. Loved his article and sense of humor.
I corrected the length of the @status parameter to 250.
I put the following after the Where clause: PO_Detail_Status IN (SELECT PO_Detail_Status FROM dbo.DelimitedSplitN4K(@status,','))
It's still not working. What am I doing wrong?
juniormint

PO_Detail_Status IN (SELECT PO_Detail_Status FROM dbo.DelimitedSplitN4K(@status,','))

...should be...

PO_Detail_Status IN (SELECT Item FROM dbo.DelimitedSplitN4K(@status,','))

... and thank you for the feedback on the article (except that it's "Moden", not "Modon" :D)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

slsmithstone SSC-Addicted Points: 431 More actions · Answer 8

Hi Andrew -

My dense self finally got it!

Thank you so much for your time and patience. You explained it perfectly!

juniormint

slsmithstone SSC-Addicted Points: 431 More actions · Answer 9

Hi Jeff -

Sorry I misspelled your name!

Thanks for your response. I was going to mark your response as an answer too, but I couldn't find the option, once I marked Andrew's answer.

Your writing is outstanding. You have the wonderful ability to paint a picture with words. Have you written any novels or short stories?

juniormint