Passing variable number of parameters to Stored proc

Question

I am trying to pass a variable amount of parameters into a MySql stored proc. I have done this in Sql Server by using xml or parsing a delimited list. I am guessing I could do the parsing of a list in MySql but are there other options?

What I am trying to do is I have a configuration that stores a filter of data to return. The filter is based off of categories which correspond to fields that need to be returned (variable number based on company). I pretty much want to write an "IN" clause with these configured category IDs, but am doing everything via Stored Proc.

Answer 1

There are a lot of code options, if you're going from C# to MySql. They basically take the form of creating a function that passes all the parameters. Then you wrap that in a mechanism that allows you to specify only the parameters you care about in a specific situation, setting defaults for the rest.

You can do this with object initializers, anonymous classes, list, variable argument functions, and likely some other things I didn't think of.

If this meets your design goals, pick one mechanism and ask more about it.

Answer 2

You could create "stub" input parameters in your stored proc, for example, and

CREATE PROCEDURE [dbo].[ThisTakesMultipleOptionalParameters

@SerialNumber int = null, 
@ProductName nvarchar(100) = null 

AS

Then you can EXEC it with one or more parameters specified, or not.

EXEC dbo.ThisTakesMultipleOptionalParameters @ProductName='Doritos' 

EXEC dbo.ThisTakesMultipleOptionalParameters @SerialNumber=666

EXEC dbo.ThisTakesMultipleOptionalParameters @SerialNumber=696, @ProductName='Juicy Bits'