A callable statement is not really applicable in this case, as the number of parameters is not fixed. Construct your SQL directly like in the example above (and of course take care of SQL injection vulnerabilities).
You can implement your stored procedure with 5 input params (assuming you have max of 5 checkboxes), and pass null or 0, when the corresponding checkbox is not checked In the SP, you would tweak the query as Paul has illustrated, appending condition to query if input param is not null. You need to be careful with the 'where' and 'and' clauses
If you need variable number of params, a standard way would be to use delimited input string, and parse the string in SP to retrieve individual values, unless your database provides support for arrays or collections
Joined: Jun 30, 2005
since I am using a stored procedure. I wrote a function that returns a variable that is needed to append to the where clause.
say the variable 'qry' returns ' where txtbx1 = I_bx1 and txtbx2 = I_bx2' where I_bx1 and I_bx2 are variables that will be passed to the stored procedure.
I am not able to append it to my sql query . How should i do it.
I don't think any of us understand why you are using a Stored Procedure to do what you are trying to do. Typically, Stored Procedures will represent a repeatable bit of query logic where only the input parameters are allowed to change. You seem to be trying to write a Stored Procedure which itself changes, if that is the case, a Statement makes more sense. Is there a specific reason you are using a procedure?
Thanks for the reply! I figured out a solution - I am taking the count() of the StringBuffer value and then using the deleteCharAt(countValue) and removing the last character, which happens to be the comma value.
I will definitely look into your post however since I will probably need to do this function more often.