Passing Parameters to stored procedure

[balachandar]

Hi All,
I have developed a new stored procedure. Wherein I have the following where clause

Where emp_id in (iparameter)
Actually there can be multiple values here
i.e.
Where emp_id in(‘100’,’121’’133’)

Moreover, the number of such values is not known. Hence, I am passing a big string
‘100’,’110’,’121’ to the stored procedure as a parameter iparameter and use it in the where clause.
However, it is not working in the way as I expected. If I hard code those values, the procedure works correctly. This is a temporary fix and not a permanent solution, Hence I would like to know from you the solution for this problem. Why If I pass a parameter to an “IN” clause, it is not working correctly.
Else what is the way I can build a dynamic “IN” clause. Give me the best solution.

Thanks and Regards
Balachandar Ganesan.

 

[MarcLodge]

It sounds as if what you expect to pass is not what the stored procedure receives. Try putting some sort of diagnostics into the stored procedure so that you can see what is actually getting through. This may lead you to the probelm. Obviously if the procedure runs ok with hard coded values, then you do not need to look at this area, but the sending procedure and transport process.

Marc

 

[balachandar]

Hi
Basically this is how the Query looks
select
*
from
Anytable
where Anycolumn in('1','2','3'....).
One never knows the number of values in the "IN" Clause.
Hence When I created the stored procedure I declared a
parameter inputparm varchar(4000) and declared a cursor for the following query
select
*
from
Anytable
where Anycolumn in(inputparm)

If pass a value like '1' The Stored procedure returns correct result set. However it does not return anything if
I pass a set of values like '1','2','3' .Since number of such values are not known I am not able to define multiple
values as well.

Please guide me in finding out what was wrong with My idea .How this can be solved ?. Is there any other way of doing this ?.
Balachandar Ganesan

 

[wgfranzen]

This is the same class of problem as when using the IN predicate and not knowing how many host variables to place within the parentheses. There are two options:

1. Build the SQL as IN 1, :2, :3, :4, :5 ... :N) where N is the reasonable maximum. When the number of values is less than N, just duplicate the value. For example, assume N=6 in the program. Assume a passing of 3 parameters A, B, C. Move parameter A to :1, B to :2, C to :3, A to :4, B to :5 and C to :6). DB2 will work just fine processing this. This may take some time to optimize, but you'll end up with "static bind" on the query.

This choice doesn't process a number of parameters > N very well at all - so the choice of the reasonable maximum may be the sticking point.

2. Build the SQL Statement Dynamically, and execute the prepare, etc. If the SELECT portion of the statement remains the same, it is very easy to perform this process. However, the cost of the prepare may be expensive to your immediate application. The draw back to this is the buffer for use in the dynamic query is 32K - (This is from memory so it should be researched). Again, this approach arrives a practical and reasonable maximum set of values.

As always, mileage depends on use. Both of these solve the problem; however, the neither may provide the optimal solution.

 

[balachandar]

Hi All,
Thanks for the suggestions that were given. Finally we have got a solution. It may not be the best, But it is little
tricky.
Instead of declaring a cursor for
select
*
from
Anytable
where Anycolumn in(inputparm)

We declared it like this
select
*
from
Anytable
where
REPLACE(inputparam,Anycolumn,'X')<>inputparam
i.e The idea is if the value of the column is a part of the list then replace output and inputparam will not match. Otherwise they will match.
Instead of finding if Anycolumn exists in inputparam we are trying to find if inputparam consists of Anycolumn.

Thanks and Regards
Balachandar Ganesan