?
Solved

Using a SP in a DB2 Function

Posted on 2012-12-24
9
Medium Priority
?
316 Views
Last Modified: 2013-01-22
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
Comment
Question by:pvsbandi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 668 total points
ID: 38719138
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
 

Author Comment

by:pvsbandi
ID: 38719160
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 38719426
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:pvsbandi
ID: 38721307
Hi,

 Please find the code attached.
SP.sql
0
 
LVL 8

Assisted Solution

by:mustaccio
mustaccio earned 664 total points
ID: 38724066
>  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
 

Author Comment

by:pvsbandi
ID: 38724621
Thanks. Can this procedure be altered to facilitate the creation of a UDF?
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 38724780
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
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 668 total points
ID: 38739219
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
 

Author Closing Comment

by:pvsbandi
ID: 38805286
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question