Hey All,

First time poster, looking for some ideas or guidance with a small project I currently have.

I have slowly been ramping up on my db2 skills using online sites and now i hope this site can help..

Basically what I need is a stored procedure that is sent multiple parameters. I need to query tables based on these parameters. It would be pretty simple except I cannot expect all parameters to be populated on every call.

For example lets say the screen has first name, last name, employee number and department. I need to pull records off a table based on this. If they pass all 4 parms at all times it becomes a simple select and I'm done..

BUT the issue is I can be passed just a first name, or a last name and nothing else. Any combination really, the less parameters the more rows I would return. If someone passes first name last name and employee number I would grab 1 record, if they pass just last name I can grab all "smiths"...

So, I am not sure if there is such a concept of dynamic clauses, where if I want the AND statement executed I can put it in an if statement of some sort... I can ask for "-1" if a parameter is not chosem..

So..

(Psuedo code/english?)

select ****
where 1=1
(if vfname != -1) AND fname = vfname
(if vlname!= -1) AND lname = vlname
(if vemp!= -1) AND emp = vemp
(if vdep!= -1) AND dep = vdep

If all parms are -1 I would just return the entire table...

I've been working with DB2 for just a little bit so I doubt something like this is out there so this is why I come here for ideas....

I hope all this made sense, I'll provide more detail is needed

SQL is by design quite close to how you would describe that in a human language; you just need to express what you want in a human language:

select stuff from table where parameter is null or parameter equals something etc.

Right...

More like:
select stuff from table where parameter equals something IF parameter was provided..


But you might be on to something, what is I do the following...I ask for a -1 when no department was chosen... I can do the following:

select * from table
where (-1 = vDepNum OR depNum = vDepNum);


If I recall, it will check the first statement and if -1 = -1 it will not continue with the second part of the or, am I correct? If it's not -1 it means I was passed a parameter and I can use it in my clause....

hmm if what I said above is correct then this might be what I need.

jcoder, if you don't want to build a dynamic statement, you can look into the CASE expression - IBM DB2 9.7 for Linux, UNIX, and Windows

Stealth_DBA - Thanks. I was under the impression a CASE statement could not be used within a where or and clause, but looking at the documentation it seems that is not the case(no pun intended).

I have not done anything with dynamic SQL, but I will look into both.

Thank you for your help

Keep in mind writing your SQL that way it will be tablespace scans. If you want good performance, look at dynamic sql that Stealth mentioned or write all the variations of statements and execute the appropriate one based on your inputs.
Dave Nance

Could you explain why?

Let's see, what access path would you choose with this sql statement?

although now that I think of it with dynamic rules at run time DB2 may be able to get the right path.
VS:
or:
Dave