pvsbandi
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. Can this procedure be altered to facilitate the creation of a UDF?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
and thanks for the advise! I have created a new procedure with the logic embedded in it.
ASKER
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.