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..
(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.
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.
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.