Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Using a SP in a DB2 Function

Hi,

  I have a stored procedure  SP1 which returns the following result set.

ID1                  CL_ID                Link_ID1
123                   ABC                    234
 333                  DEF                       --
444                   HIJ                      567

Now, i want to create a UDF with V_ID as a parameter.
This will call this stored Procedure SP1 and pass the V_ID parameter against the result set, to filter for the Link_ID1.

So, if i pass the V_ID as 444 inside the UDF, then it should check for the Link_ID1 in the stored procedure. Since there is no match, it should return 444.
 IF i pass 234 in the UDF, then it should look for the match of 234 and return ID1, which is 123.

Is there a way i can do it? Please advise.
0
pvsbandi
Asked:
pvsbandi
3 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi pvsbandi,

In DB2, a stored procedure can call a UDF (or anything else), but a UDF cannot call a stored procedure.  

Can you create another stored procedure that calls the one that you have now?


Kent
0
 
pvsbandiAuthor Commented:
Sure.. how? Please advise.

  I want to be able to use the function in the select statements  to return the ID1 or the passed V_ID from the function.
0
 
momi_sabagCommented:
can you post your stored procedure code? (the one that generates that table)
maybe you can use only a function to get it all done, maybe a view will be enough
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
pvsbandiAuthor Commented:
Hi,

 Please find the code attached.
SP.sql
0
 
mustaccioCommented:
>  a UDF cannot call a stored procedure

This depends on the DB2 platform and version, I believe. It's possible in DB2 LUW since version 8.2 if I'm not mistaken.

There are certain limitations, of course. Particularly, you cannot readily process cursors in SQL UDFs.
0
 
pvsbandiAuthor Commented:
Thanks. Can this procedure be altered to facilitate the creation of a UDF?
0
 
mustaccioCommented:
Like I said, you won't be able to process a cursor in the UDF, so the only way to alter your SP is to remove the OPEN statement, which is probably not what you want.

Can you explain in simple terms what you're trying to achieve in your stored procedure?
0
 
tliottaCommented:
A call to a procedure should be okay in reasonably recent releases, though the procs shouldn't include statements such as CONNECT, COMMIT, etc. I'm not sure if DB2 9.7 provides a way to handle it. Cursors have been possible with DB2 for AS/400s for a number of years, but I've only seen them defined within the functions themselves.

If a cursor cannot be declared in a function for a stored proc result set, maybe a second proc can do it. The function might then be a wrapper for proc2. Maybe.

Tom
0
 
pvsbandiAuthor Commented:
Sorry for the delay..was out of town.
   and thanks for the advise! I have created a new procedure with the logic embedded in it.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now