Is a multi-valued stored procedure parameter just bad practice?

views:

answers:

Is a multi-valued stored procedure parameter just bad practice?

I have the strange aversion to passing in multiple ID parameters to a single stored procedure. For example, this feels just wrong:

GetMyObject(ListofIDs, OtherParam1, OtherParam2, ...)

I understand HOW to do it (correctly if I must).. but I don't feel like I should do it. I feel like it defeats the purpose of a "get item" stored procedure/sub routine. I feel like I should build my SPs to support appropriate filter parameters. If my caller has a list of IDs, shouldn't they call the sp that many times?

Help?

A "get item by ID" routine should never return more than one object, because that makes absolutely no linguistic sense.

A "get items by IDs" routine? Sure, if you have a decent use-case for it and it'll be used often enough.

But most of the time, yes, instead of a routine returning multiple items by ID, you want a routine that returns items based on application-appropriate filtering parameters (e.g. "give me all transactions from January 8th for more than $10").

By the way, sometimes a range of IDs (e.g. everything between 5 and 10) is a perfectly valid set of filters!

Incidentally, this isn't necessarily just a MySQL or SQL-in-general issue. Almost any sort of dataset querying API in any language will present these same design questions, and their answers will usually be highly similar.

Nicholas Knight 2010-07-20 02:16:42

I guess that is an interesting way of thinking about it. Why can't a range of IDs be a set of filters? The problem that I am seeing time and time again is that an application will want all of the items (for a list of IDs) in a single call for the sake of "performance". So, instead of making 16 calls, they will want to make one call with 16 IDs.

deLux_247 2010-07-20 02:20:48

@deLux_247: If the application _needs_ such a call, give it to them. It's not an unreasonable choice for some use cases. But I'd be looking carefully at _why_ the application needs it -- where is it getting the IDs from? If they're coming from the database in the first place, why didn't it just request the data it needed then? Are there really going to be that many IDs? If the use case is only two or three at a time, for example, I don't see why it can't call it once for each ID unless your infrastructure is severely overloaded.

Nicholas Knight 2010-07-20 02:23:29

ansaurus

tags:

views:

answers:

Is a multi-valued stored procedure parameter just bad practice?

related questions