So we built better training tools.
-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers
All at your fingertips. What are you waiting for?
Course Of The Month
4 days, 10 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Using a SP in a DB2 Function
Posted on 2012-12-24
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.
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.
[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
-
4
2
- +2
9 Comments
Active today
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
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
Active 1 day ago
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.
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.
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
maybe you can use only a function to get it all done, maybe a view will be enough
> 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.
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.
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?
Can you explain in simple terms what you're trying to achieve in your stored procedure?
Active 1 day ago
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
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
Featured Post
Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.
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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special.
It's completely agentless, but does let you create an agent, if you desire.
It offers powerful scalability …
Suggested Courses
Course of the Month4 days, 10 hours left to enroll
689 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.