473,836 Members | 2,171 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DB2 cursors in stored procedures with parameters?

Hi all,

In Oracle you can pass parameters to cursors i.e.
cursor myCursor(name_c ity varchar(50), population integer) is
select * from cities
where name = name_city and
num_people = population;
Can you do the same thing in DB2?

All I can see is:

DECLARE myCursor CURSOR WITH RETURN FOR

and this syntax doesn't allow for parameters...
Thanks!

Dec 8 '06 #1
7 23565
<pa************ *@gmail.comwrot e in message
news:11******** **************@ n67g2000cwd.goo glegroups.com.. .
Hi all,

In Oracle you can pass parameters to cursors i.e.

cursor myCursor(name_c ity varchar(50), population integer) is
select * from cities
where name = name_city and
num_people = population;

Can you do the same thing in DB2?

All I can see is:

DECLARE myCursor CURSOR WITH RETURN FOR

and this syntax doesn't allow for parameters...

Thanks!
I am not sure what you are trying to do. Here is an example SP in DB2 that
returns a cursor based on input variable.

CREATE PROCEDURE DB2INST1.DEPT_E MP
(IN IN_DEPT CHAR(3))

RESULT SETS 1
MODIFIES SQL DATA
LANGUAGE SQL

BEGIN

DECLARE C1 CURSOR WITH RETURN FOR
SELECT EMPNO
FROM DB2INST1.EMPLOY EE
WHERE WORKDEPT = IN_DEPT;

OPEN C1;

END
-----------------------------------------------------

CALL DB2INST1.DEPT_E MP ('A00')

Result set 1
--------------

EMPNO
------
000010
000110
000120

3 record(s) selected.

Return Status = 0


Dec 8 '06 #2
Thanks for your quick reply.

Actually what I'm trying to do is get the cursor declaration to take
the parameter...

i.e. in your example the parameter is in the CREATE PROCEDURE
declaration

CREATE PROCEDURE DB2INST1.DEPT_E MP (IN IN_DEPT CHAR(3))

I want to put it in the DECLARE stmt like so:

DECLARE C1 CURSOR(param1, param2) WITH RETURN FOR

Then call it like so:

OPEN c1(param1, param2...);


Mark A wrote:
<pa************ *@gmail.comwrot e in message
news:11******** **************@ n67g2000cwd.goo glegroups.com.. .
Hi all,

In Oracle you can pass parameters to cursors i.e.

cursor myCursor(name_c ity varchar(50), population integer) is
select * from cities
where name = name_city and
num_people = population;

Can you do the same thing in DB2?

All I can see is:

DECLARE myCursor CURSOR WITH RETURN FOR

and this syntax doesn't allow for parameters...

Thanks!

I am not sure what you are trying to do. Here is an example SP in DB2 that
returns a cursor based on input variable.

CREATE PROCEDURE DB2INST1.DEPT_E MP
(IN IN_DEPT CHAR(3))

RESULT SETS 1
MODIFIES SQL DATA
LANGUAGE SQL

BEGIN

DECLARE C1 CURSOR WITH RETURN FOR
SELECT EMPNO
FROM DB2INST1.EMPLOY EE
WHERE WORKDEPT = IN_DEPT;

OPEN C1;

END
-----------------------------------------------------

CALL DB2INST1.DEPT_E MP ('A00')

Result set 1
--------------

EMPNO
------
000010
000110
000120

3 record(s) selected.

Return Status = 0
Dec 8 '06 #3
<pa************ *@gmail.comwrot e in message
news:11******** **************@ 16g2000cwy.goog legroups.com...
Thanks for your quick reply.

Actually what I'm trying to do is get the cursor declaration to take
the parameter...

i.e. in your example the parameter is in the CREATE PROCEDURE
declaration

CREATE PROCEDURE DB2INST1.DEPT_E MP (IN IN_DEPT CHAR(3))

I want to put it in the DECLARE stmt like so:

DECLARE C1 CURSOR(param1, param2) WITH RETURN FOR

Then call it like so:

OPEN c1(param1, param2...);
Why?

You can use a variable in the SQL statement of the cursor, and in situations
where you cannot use a variable for the SQL, then you can use a dynamically
prepared cursor with the entire definition as a parm or variable.
Dec 8 '06 #4
What you described is actually what I'm doing now (had to find a
workaround quickly =)). However it just seems that the Oracle
implementation is cleaner, and was wondering if there was an equivalent
to doing something similar.
Mark A wrote:
<pa************ *@gmail.comwrot e in message
news:11******** **************@ 16g2000cwy.goog legroups.com...
Thanks for your quick reply.

Actually what I'm trying to do is get the cursor declaration to take
the parameter...

i.e. in your example the parameter is in the CREATE PROCEDURE
declaration

CREATE PROCEDURE DB2INST1.DEPT_E MP (IN IN_DEPT CHAR(3))

I want to put it in the DECLARE stmt like so:

DECLARE C1 CURSOR(param1, param2) WITH RETURN FOR

Then call it like so:

OPEN c1(param1, param2...);

Why?

You can use a variable in the SQL statement of the cursor, and in situations
where you cannot use a variable for the SQL, then you can use a dynamically
prepared cursor with the entire definition as a parm or variable.
Dec 8 '06 #5
pa************* @gmail.com wrote:
What you described is actually what I'm doing now (had to find a
workaround quickly =)). However it just seems that the Oracle
implementation is cleaner, and was wondering if there was an equivalent
to doing something similar.
Correct - and I agree that parameterizing cursors is a nice-to-have.
It does, as Mark shows not add any function however.
You just use the variable directly within the SELECT.
If you don't know yet which variable to use when you DECLARE you can use
a temporary variable in the SELECT and assign to it before the OPEN.
All the Oracle syntax does is to father up the parameters in one place
(between the braces) and move the assignment into the OPEN syntax.

The closest I can think of that would give you at least the
encapsulation would be a table function (aka a parameterized view). But
these need to be statically created.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 8 '06 #6
Thanks again. As a developer it's always nice to have these functions.
They make life just a little easier day by day. But for now the
workaround is just as good.
Serge Rielau wrote:
pa************* @gmail.com wrote:
What you described is actually what I'm doing now (had to find a
workaround quickly =)). However it just seems that the Oracle
implementation is cleaner, and was wondering if there was an equivalent
to doing something similar.
Correct - and I agree that parameterizing cursors is a nice-to-have.
It does, as Mark shows not add any function however.
You just use the variable directly within the SELECT.
If you don't know yet which variable to use when you DECLARE you can use
a temporary variable in the SELECT and assign to it before the OPEN.
All the Oracle syntax does is to father up the parameters in one place
(between the braces) and move the assignment into the OPEN syntax.

The closest I can think of that would give you at least the
encapsulation would be a table function (aka a parameterized view). But
these need to be statically created.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 11 '06 #7
Patrick wrote:
Thanks again. As a developer it's always nice to have these functions.
They make life just a little easier day by day. But for now the
workaround is just as good.
I agree. Rest assured that I recognize a good thing when I see it. Even
if it's coming from the competition :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Dec 11 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2092
by: zlatko | last post by:
I use a stored procedure that is calling several other stored procedure which update or append values in several tables. All of them are stored procedures with input parameters by which they filter rows to be updated or inserted into other tables. Filtration is based on certain actual values on forms (form with several subforms). My question is following: How to pass parameters to those stored procedures that are triggered by a button?...
8
7352
by: Adam Greifer | last post by:
Hi! I'm a newbie at DB2 but have 13 years of SQL Server. I need to convert over 100 SQL Server procs to DB2. I haven't had much luck with the IBM Integration Toolkit and want to avoid ER/win templates. Any suggestions. Thanks. Adam
5
4538
by: Jeff | last post by:
I have question about differences in fenced sql procedures and fenced stored procedures. Do fenced sql procedures take up an extra memory segment when executed? Reason I ask is we have several fenced sql procedures that have been excuting o.k. We implemented a fenced stored procedure on a C program and when trying to execute it, we get the "DIA3833C The system memory limit was reached."
3
2149
by: Zlatko | last post by:
A question concerning Access Project with SQL Server: I use a stored procedure that is calling several other stored procedure which update or append values in several tables. All of them are stored procedures with input parameters by which they filter rows to be updated or inserted into other tables. Filtration is based on certain actual values on forms (form with several subforms). My question is following: How to pass parameters to...
5
2257
by: Rob Wire | last post by:
For the code below, how could I add an item in the drop down lists for both company and location to be an "All" selection that would send to the stored proc. spRptAttachments a value of "%" so that it would bring back all attachments at all companies or all locations at a company? Thank you, Rob. Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not IsPostBack Then
3
1306
by: .Net Sports | last post by:
I'm able to find info for using input parameters for stored procedures to do inserts and update type SQL functions in asp.net (vb.net), but I need to find something that will use input parameters with Select sql statements to do search functions in an sql dbase and sql tables. TIA ..NetSports
45
3426
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
28
72596
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test Environments. What is the purpose of a view if I can just copy the vode from a view and put it into a stored procedure? Should I be accessing views from stored procedures?
1
3350
by: jobs | last post by:
I have a growning list of stored procedures that accept a single string as a parameter. The list and parameters are maintained in a table with two columns. Some of the Stored procedures take hours to complete. procedures and the page come right back and recording infomation about the stored procedure (like a job number) that can later be used to report what stored procedures are still running.
9
8996
by: jyothi1105 | last post by:
Hi all, Here is some information which could help people who want to create stored procedures and execute them in their program. You can create stored procedures in two ways: Through front end or writing procedure from backend SQLServer. Back End First we'll cover how to write a stored procedure in back end. The following example is how to create a store procedure for MSSql Server:
0
9810
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9656
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10821
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10527
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10571
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10241
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5642
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4001
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.