February 10, 2017 at 8:14 am
I vaguely recall having executed stored procedures in the past where there were a number of optional parameters, and I'm wondering if anyone knows if it would be practical to design a stored procedure that could handle a rather large number of parameters. I'm not really envisioning anything in the millions, but very possibly somewhere in the 2 to 3 thousand category. The reason I ask is because I'm working with a scenario in which a specified SQL query file is going to have parameters in it in the following format:
SELECT FIELD1, FIELD2
FROM dbo.MY_TABLE
WHERE FIELD3 IN (${parameter_name:-'optional colon and dash followed by what to use if the parameter value doesn't actually have a value (not necessarily NULL)'});
These parameters are processed by a Java program that substitutes the chosen values from a GUI into the query in place of the parameters. The grief is that the parameter values can be a series of comma-separated single-quoted strings... something perfect for an IN portion of a WHERE clause, but not really usable anywhere else in the query, and there's no way to know in advance how many elements there will be. I'm just wondering if there might be a way to code a stored procedure such that an EXEC statement could make use of such a parameter. Seems unlikely to me, or at best, perhaps rather impractical, but thought I'd get feedback and see what folks think... This would have to be functional with SQL 2014 or lower to be of any immediate value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 13, 2017 at 6:36 am
Well... no replies... I'm going to guess there's just no practical way to do it, which is what I suspected all along, but I had to at least ask the question...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply