• 0

[mySql] stored proc input as delimited values


Question

I have a page that populates a text box with a number if integer values - each representing an ID of a vlue in a database, so after selecting options, the box may read:

""

"6"

"5,7"

"6,9,12"

etc

eg. The number of integers listed has no specific length. It could be none, it could be 1, it could be 20 (though a real world likely maximum would be about 5).

OK so far...

I am sending this entire string over to my stored procedure as an input param as a varchar. Because there is no known maximum number of values represented, I cannot realistically map each to an input of its own.

So within my stored proc, this string is known as inputIDs.

I want to select items from a table where these values are ALL matched.

The query is selecting products against a range of paramters for a product - so:

ProductID 123MR1 may match against a product parameters table of paramID = 4, paramID=5 and paramID=10. If my input params were to be '4,5,10' I would want it to match...

Any ideas?

Link to comment
Share on other sites

13 answers to this question

Recommended Posts

  • 0

Forgive my since my MYSQL knowledge isn't that great, but would it not be easier to just use a dynamic select statement, as opposed to a Stored Procedures, given how variable the input will be?

That way you could just use an "IN" statement and assemble the query manually :) (e.g. WHERE inputID IN (1, 2, 3, 4, 5))

Link to comment
Share on other sites

  • 0
Forgive my since my MYSQL knowledge isn't that great, but would it not be easier to just use a dynamic select statement, as opposed to a Stored Procedures, given how variable the input will be?

That way you could just use an "IN" statement and assemble the query manually :) (e.g. WHERE inputID IN (1, 2, 3, 4, 5))

True, but stored procedures are better in many ways. I, for one, never pass a SQL line across a network. It's much safer to simply pass variables.

Link to comment
Share on other sites

  • 0
True, but stored procedures are better in many ways. I, for one, never pass a SQL line across a network. It's much safer to simply pass variables.

Thats fair, but like y_notm said, you could set up a stored procedure that creates the sql query, and just pass a csv string in and do some string appends. Its scruffy, and there's the risk of injection vulnerability if adequate checks aren't added to the code, but the hack required to parse a CSV string and perform SQL based on that would result in even uglier code... in my opinion of course :)

Link to comment
Share on other sites

  • 0

I am going down the path or splitting my string into a temp table then selecting from it, as said above. Is this not likely to have a major performance hit?

Link to comment
Share on other sites

  • 0

I've got no evidence either way, but my gut feeling would be that yes, there would be a fair performance hit compared to dynamically creating a query. You have to create a table, perform a string search, insert into the temporary table, then finally select from that table into a where clause.

How significant the hit is would depend on how efficiently MySQL creates temporary tables (which I assume is very efficiently), but also how many values you're passing as a parameter, since the performance hit will increase with bigger lists of values.

Link to comment
Share on other sites

  • 0
However, to counter that - a dynamic query has no execution plan.

Not sure about MySQL, but when talking about SQL Server, that's incorrect.

Link to comment
Share on other sites

  • 0
Really? How can dynamic SQL have a cached execution plan? I'm intrigued...

Each sql statement has its own cached execution plan. Some people believe that sprocs in SQL Server are precompiled, but that's not correct. The SQL Server docs state that they're not precompiled.

Here's a pretty good article. http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx It was quite the religious war back in 2003 (still is, I guess).

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.