473,703 Members | 3,018 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help Cursor Based Stored Procedure Is Getting Slower and Slower!

I am begginner at best so I hope someone that is better can help.
I have a stored procedure that updates a view that I wrote using 2
cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
do it using reqular transact SQL.

The problem is that this procedure is taking longer and longer to run.
Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records every month.

The procedure works like this.
The first Cursor stores a unique account and duedate combination from
the view.
It then finds all the accts in the view that have that account duedate
combo and loads them into Cursor 2 this groups them together for data
manipulation. The accounts have to be grouped this way because a
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups.

Here is my procedure I hope someone can shead some light on this. My
boss is giving me heck about it. (I think he thinks Girls cant code!)
I got this far I hope someone can help me optimize it further.


CREATE PROCEDURE dbo.sp_PromiseS tatus
AS

BEGIN
SET NOCOUNT ON

/* Global variables */
DECLARE @tot_pay money
DECLARE @rec_upd VARCHAR(1)
DECLARE @todays_date varchar(12)
DECLARE @mActivityDate2 _temp datetime
DECLARE @tot_paydate datetime
/* variables for cursor ACT_CUR1*/
DECLARE @mAcct_Num1 BIGINT
DECLARE @mDueDate1 datetime

/* variables for ACT_CUR2 */
DECLARE @mAcct_Num2 BIGINT
DECLARE @mActivity_Date 2 datetime
DECLARE @mPromise_Amt_1 money
DECLARE @mPromise_Statu s varchar(3)
DECLARE @mCurrent_Due_A mt money
DECLARE @mDPD int
DECLARE @mPromise_Date datetime

SELECT @todays_date =''+CAST(DATEPA RT(mm,getdate() ) AS varchar(2))
+'/'+CAST(DATEPART (dd,getdate()) AS varchar(2))
+'/'+CAST(DATEPART (yyyy,getdate() ) AS varchar(4))+''

DECLARE ACT_CUR1 CURSOR FOR
SELECT DISTINCT
A.ACCT_NUM,
A.DUE_DATE
FROM VWAPPLICABLEPRO MISEACTIVITYREC ORDS A
OPEN ACT_CUR1
FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1

WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @rec_upd = 'N '

DECLARE ACT_CUR2 CURSOR FOR
SELECT
B.ACCT_NUM,
B.ACTIVITY_DATE ,
B.PROMISE_AMT_1 ,
B.PROMISE_STATU S,
B.CURRENT_DUE_A MT,
B.DAYS_DELINQUE NT_NUM,
B.PROMISE_DATE_ 1
FROM VWAPPLICABLEPRO MISEACTIVITYREC ORDS B (UPDLOCK)
WHERE B.ACCT_NUM = @mAcct_Num1
AND B.DUE_DATE = @mDueDate1
ORDER BY B.ACCT_NUM,B.DU E_DATE,B.ACTIVI TY_DATE,CASE
B.Time_Obtained
WHEN 0 THEN 0
ELSE 1
END Desc, B.Time_Obtained

OPEN ACT_CUR2
FETCH NEXT FROM ACT_CUR2
INTO @mAcct_Num2 ,@mActivity_Dat e2,@mPromise_Am t_1,@mPromise_S tatus,@mCurrent _Due_Amt,@mDPD, @mPromise_Date
WHILE (@@FETCH_STATUS = 0)
BEGIN

----CHECK----------------------------------------------------------------------
--DECLARE @PrintVariable2 VARCHAR (8000)
--SELECT @PrintVariable2 = CAST(@MACCT_NUM 2 AS VARCHAR)+'
'+CAST(@MACTIVI TY_DATE2 AS VARCHAR)+' '+CAST(@MPROMIS E_AMT_1 AS
VARCHAR)+' '+CAST(@MPROMIS E_STATUS AS VARCHAR)+'
'+CAST(@mCurren t_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+'
'+CAST(@mPromis e_Date AS VARCHAR)
--PRINT @PrintVariable2
----END
CHECK------------------------------------------------------------

IF @mDPD >= 30
BEGIN

SELECT @tot_pay = SUM(CONVERT(FLO AT, C.PAY_AMT))
FROM vwAplicablePaym ents C
WHERE C.ACCT_NUM = @mAcct_Num2
AND C.ACTIVITY_DATE >= @mActivity_Date 2
AND C.ACTIVITY_DATE < @mActivity_Date 2 + 15
----CHECK----------------------------------------------------------------------
--DECLARE @PrintVariable3 VARCHAR (8000)
--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'
--PRINT @PrintVariable3
----END
CHECK------------------------------------------------------------
END
ELSE IF @mDPD < 30
BEGIN

SELECT @tot_pay = SUM(CONVERT(FLO AT, C.PAY_AMT))
FROM vwAplicablePaym ents C
WHERE C.ACCT_NUM = @mAcct_Num2
AND C.ACTIVITY_DATE >= @mActivity_Date 2
AND C.ACTIVITY_DATE BETWEEN @mActivity_Date 2 AND
@mPromise_Date + 5
----CHECK--------------------------------------------------------------------
--DECLARE @PrintVariable4 VARCHAR (8000)
--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'
--PRINT @PrintVariable4
----END CHECK------------------------------------------------------------
END
----------------------------------------MY REVISED
LOGIC-------------------------------------------------------
IF @rec_upd = 'N'
BEGIN

IF @mDPD >= 30
BEGIN

SELECT @mActivityDate2 _temp = @mActivity_Date 2 + 15

--DECLARE @PrintVariable5 VARCHAR (8000)
--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15 '
--PRINT @PrintVariable5

END
ELSE IF @mDPD < 30
BEGIN
SELECT @mActivityDate2 _temp = @mPromise_Date + 5

--DECLARE @PrintVariable6 VARCHAR (8000)
--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'
--PRINT @PrintVariable6

END
IF @tot_pay >= 0.9 * @mCurrent_Due_A mt--used to be promise amt
BEGIN
UPDATE VWAPPLICABLEPRO MISEACTIVITYREC ORDS
SET PROMISE_STATUS = 'PK',
TOTAL_DOLLARS_C OLL = @tot_pay
WHERE CURRENT OF ACT_CUR2
--This statement updates the time that the status was placed
into PK.
IF @mPromise_Statu s IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPRO MISEACTIVITYREC ORDS
SET Status_Date = @todays_date
WHERE CURRENT OF ACT_CUR2
END

SELECT @rec_upd = 'Y '
END
IF ((@tot_pay < 0.9 * @mCurrent_Due_A mt) OR @tot_pay IS NULL)
AND( @mActivityDate2 _temp > @todays_date )--need to put 1day
of month here for snapshot9/01/2004
BEGIN
UPDATE VWAPPLICABLEPRO MISEACTIVITYREC ORDS
SET PROMISE_STATUS = 'OP'
WHERE CURRENT OF ACT_CUR2

--This statement updates the time that the status was placed
into OP which is the original Activity Date.
--The record will hold this date until it goes into PK,PB,or
IP.
IF @mPromise_Statu s IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPRO MISEACTIVITYREC ORDS
SET Status_Date = @mActivity_Date 2
WHERE CURRENT OF ACT_CUR2
END

END
ELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_A mt) OR @tot_pay IS
NULL)
AND( @mActivityDate2 _temp <= @todays_date )--need to put 1day
of month here for snapshot 9/01/2004
BEGIN
UPDATE VWAPPLICABLEPRO MISEACTIVITYREC ORDS
SET PROMISE_STATUS = 'PB',
TOTAL_DOLLARS_C OLL = case when @tot_pay is null
then 0 else @tot_pay end
WHERE CURRENT OF ACT_CUR2
--This statement updates the time that the status was placed
into PB.
IF @mPromise_Statu s IN ('PTP','OP')
BEGIN
UPDATE VWAPPLICABLEPRO MISEACTIVITYREC ORDS
SET Status_Date = @todays_date
WHERE CURRENT OF ACT_CUR2
END
END
END
ELSE IF @rec_upd = 'Y'
BEGIN
UPDATE VWAPPLICABLEPRO MISEACTIVITYREC ORDS
SET PROMISE_STATUS = 'IP',
TOTAL_DOLLARS_C OLL = 0
WHERE CURRENT OF ACT_CUR2
--This statement updates the time that the status was placed
into IP.
IF @mPromise_Statu s NOT IN ('IP')
BEGIN
UPDATE VWAPPLICABLEPRO MISEACTIVITYREC ORDS
SET Status_Date = @todays_date
WHERE CURRENT OF ACT_CUR2
END

END
FETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2
,@mActivity_Dat e2,@mPromise_Am t_1,@mPromise_S tatus,@mCurrent _Due_Amt,@mDPD, @mPromise_Date
END
CLOSE ACT_CUR2
DEALLOCATE ACT_CUR2

FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1
END
CLOSE ACT_CUR1
DEALLOCATE ACT_CUR1

SET NOCOUNT OFF

END
GO
Jul 20 '05 #1
15 3821
Without getting into code, I think you should consider using faster type
of cursor if possible. FORWARD ONLY for example
Jul 20 '05 #2
If you are a beginner then avoid cursors. They are rarely a necessary or
good solution to a problem in SQL. You will learn bad practices if you mess
with cursors before you can write good set-based SQL.

It doesn't look like you are doing anything in your code for which a cursor
makes sense so I recommend you start again from scratch. If you need help,
try to pare the problem down to it's essential features and then post again
with more information (DDL, some sample data, required results). This
article explains how best to ask for help:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--
Jul 20 '05 #3
>> I am beginner at best so I hope someone that is better can help. <<

Virutally everything you have done is wrong.

1) The data element names violate ISO-11179 standards. What does a
VARCHAR(1) mean?

2) You should never write a cursor in an applications. Thye are for
utility programs.

3) You are using FLOAT and MONEY for currency amounts. And you got
the CAST() syntax wrong.

4) Unlike the 3GL languages, SQL has temporal data types. Why did you
avoid them in favor of COBOL-style string processing? You even load
the current timestamp into a string!
The problem is that this procedure is taking longer and longer to run. Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records [sic] every month. <<

No. The basic algorithm you have written is a COBOL tape file merge.
30K rows is tiny.
The first Cursor [magnetic tape file] stores a unique account and duedate combination from the view. <<

The view name has a silly "vw_" prefix in violation of ISO-11179. And
a table has no records!!! Rows are not records! Until you understand
that you will never write good SQL. A table is a set, and in SQL we
process data in whole sets, not single records.
It then finds all the accts in the view that have that account duedate combo .. <<

No, you write a query that finds the set of (account_nbr, duedate)
.. and loads them into Cursor 2 this groups them together for data

manipulation. The accounts have to be grouped this way because an
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups. <<

Think about the phrase "single group" for a minute :) A GROUP BY is
used to build grouped tables.

What you seem to be trying to do is update a table of promises. That
should be one UPDATE statement. The skeleton for the statement would
be something like this:

CREATE PROCEDURE PromiseStatus (..)
AS
UPDATE ApplicablePromi ses
SET status_date = CURRENT_TIMESTA MP,
promise_status = CASE WHEN ..END,
total_dollars_c oll
= (SELECT SUM(P.pay_amt),
FROM ApplicablePromi ses AS A, Applicablepayme nts AS P
WHERE P.acct_num = A.acct_num
AND ApplicablePromi ses.acct_num = A.account_num
AND ApplicablePromi ses.due_date = A.due_date
AND P.activity_date BETWEEN ...
AND GROUP BY A.acct_num, A.due_date)
WHERE ... ;

You put all the IF_THEN_ELSE logic into a CASE expression. You put
the total into a scalar subquery. You use temporal functions to get
the current timestamp.

Quit thinking about one column at a time. That is how you process a
field in a record. A table is made of rows, and you think of the row
as the unit of work.
Jul 20 '05 #4
Philip Mette (ph******@msn.c om) writes:
I am begginner at best so I hope someone that is better can help.
I have a stored procedure that updates a view that I wrote using 2
cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
do it using reqular transact SQL.

The problem is that this procedure is taking longer and longer to run.
Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records every month.


While cursor-based solutions sometimes may be defendible and even be
the only solution, the cost for a cursor can be pretty severe. The
corresponding set-based solution is often several magnitudes faster.
Processing 30000 rows with a cursor is not going to be anywhere near
fast.

Just like David I would recommend you to start with a clean paper. I
started to look at your code, but, frankly, all the uppercase and the
poor formatting is quite deterring. And not knowing the tables and not
having sample data is not making things any easier.

In the short run, you may make some improvements by making the cursors
INSENSITIVE by adding this keyword before CURSOR, and by replacing
WHERE CURRENT OF with the actual key values.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
Ok well I have been yelled at worse;) No I see what you are saying. I
should have used Char variable not Varchar(1).That was a adjustment
later on that frankly wasnt changed properly. I used Cursors based on
a recommendation by a "SQL Guru" Obviously this is not a good
description of him. I am going to try this using the skeleton you gave
me.It is pointing me in a right direction I think.
Again please understand I am a beginner Query writer when it comes to
SQL and I am very good with VB ,.Net and Java(Used to do front end
work and analysis not DB.)I quess I was thinking of this like code
language,ie the use of arrays etc. Thanks for your 'direct' honesty
and help.


jc*******@earth link.net (--CELKO--) wrote in message news:<18******* *************** ****@posting.go ogle.com>...
I am beginner at best so I hope someone that is better can help. <<
Virutally everything you have done is wrong.

1) The data element names violate ISO-11179 standards. What does a
VARCHAR(1) mean?

2) You should never write a cursor in an applications. Thye are for
utility programs.

3) You are using FLOAT and MONEY for currency amounts. And you got
the CAST() syntax wrong.

4) Unlike the 3GL languages, SQL has temporal data types. Why did you
avoid them in favor of COBOL-style string processing? You even load
the current timestamp into a string!
The problem is that this procedure is taking longer and longer to run. Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records [sic] every month. <<

No. The basic algorithm you have written is a COBOL tape file merge.
30K rows is tiny.
The first Cursor [magnetic tape file] stores a unique account and duedate combination from the view. <<

The view name has a silly "vw_" prefix in violation of ISO-11179. And
a table has no records!!! Rows are not records! Until you understand
that you will never write good SQL. A table is a set, and in SQL we
process data in whole sets, not single records.
It then finds all the accts in the view that have that account duedate combo .. <<

No, you write a query that finds the set of (account_nbr, duedate)
.. and loads them into Cursor 2 this groups them together for data

manipulation. The accounts have to be grouped this way because an
account can have different due dates and multiple records within each
account due date combo and they need to be looked at this way as
little singular groups. <<

Think about the phrase "single group" for a minute :) A GROUP BY is
used to build grouped tables.

What you seem to be trying to do is update a table of promises. That
should be one UPDATE statement. The skeleton for the statement would
be something like this:

CREATE PROCEDURE PromiseStatus (..)
AS
UPDATE ApplicablePromi ses
SET status_date = CURRENT_TIMESTA MP,
promise_status = CASE WHEN ..END,
total_dollars_c oll
= (SELECT SUM(P.pay_amt),
FROM ApplicablePromi ses AS A, Applicablepayme nts AS P
WHERE P.acct_num = A.acct_num
AND ApplicablePromi ses.acct_num = A.account_num
AND ApplicablePromi ses.due_date = A.due_date
AND P.activity_date BETWEEN ...
AND GROUP BY A.acct_num, A.due_date)
WHERE ... ;

You put all the IF_THEN_ELSE logic into a CASE expression. You put
the total into a scalar subquery. You use temporal functions to get
the current timestamp.

Quit thinking about one column at a time. That is how you process a
field in a record. A table is made of rows, and you think of the row
as the unit of work.

Jul 20 '05 #6
Thanks Erland.
A few Questions:
1.What does insensitive do?
2.Instead of Where Current Of
If I used Activity_Record _Num(The Primary Key)
which is distinctive would that work?
3.I use a statement called (UPDLOCK) in this part of the code:
DECLARE ACT_CUR2 CURSOR FOR
SELECT
B.ACCT_NUM,
B.ACTIVITY_DATE ,
B.PROMISE_AMT_1 ,
B.PROMISE_STATU S,
B.CURRENT_DUE_A MT,
B.DAYS_DELINQUE NT_NUM,
B.PROMISE_DATE_ 1
FROM VWPROMISEACTIVI TYRECORDS B (UPDLOCK)
Does that effect performance speed as well? This is the only process
that runs against this table so I am wondering if it is needed.

Just so you know I am working on a rewrite like you suggested. In the
short, I would like to add these suggestions that you made to the
cursor based SP.


Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...
Philip Mette (ph******@msn.c om) writes:
I am begginner at best so I hope someone that is better can help.
I have a stored procedure that updates a view that I wrote using 2
cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn't
do it using reqular transact SQL.

The problem is that this procedure is taking longer and longer to run.
Up to 5 hours now! It is anaylizing about 30,000 records. I think
partly because we add new records every month.


While cursor-based solutions sometimes may be defendible and even be
the only solution, the cost for a cursor can be pretty severe. The
corresponding set-based solution is often several magnitudes faster.
Processing 30000 rows with a cursor is not going to be anywhere near
fast.

Just like David I would recommend you to start with a clean paper. I
started to look at your code, but, frankly, all the uppercase and the
poor formatting is quite deterring. And not knowing the tables and not
having sample data is not making things any easier.

In the short run, you may make some improvements by making the cursors
INSENSITIVE by adding this keyword before CURSOR, and by replacing
WHERE CURRENT OF with the actual key values.

Jul 20 '05 #7
Philip Mette (ph******@msn.c om) writes:
Thanks Erland.
A few Questions:
1.What does insensitive do?
The meaning of INSENSITIVE is that the dataset does not change
once the cursor has been created. SQL Server copies the data to a
worktable. This may sound like some overhead, and it probably is, but
it's a small overhead in comparison with the iterative processing.

The default cursor type is keyset. I think it means that the keys are
stable, but updates in non-key columns will be reflected. But I have
never fully grasped keyset cursors so I could be wrong. What I have
seen is some really horrible query plans to set up a keyset-driven
cursor. Sure, it was in 6.5, but I see no reason to try it again.
2.Instead of Where Current Of
If I used Activity_Record _Num(The Primary Key)
which is distinctive would that work?
I didn't look to close on your code, but you should pick data from
the cursor which identifies the current row(s) being processed. (Note
that you could run a cursor over an aggregate, and thus update several
rows at same time. There are situations when this is a possible solution.
3.I use a statement called (UPDLOCK) in this part of the code:
...
Does that effect performance speed as well?


Not very likely. Since you don't seem to have an active transaction, the
effect of the UPDLOCK is none anyway.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #8
Erland,

I got the process run time to 2:00 minutes! The solution was to create
a composite index on Account and Due_date in the tables, which is what
is in my where clauses in my select statements. That and changing the
cursors to Insensitive, made this query fast. This amount of time is
more than acceptable. Thanks for pointing me in the right direction. I
am going to continue to look into a transact SQL solution in the
future.
Thanks Again,

Jill
(Using Phil's Login Name. Dont have a google acct.)


Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...
Philip Mette (ph******@msn.c om) writes:
Thanks Erland.
A few Questions:
1.What does insensitive do?


The meaning of INSENSITIVE is that the dataset does not change
once the cursor has been created. SQL Server copies the data to a
worktable. This may sound like some overhead, and it probably is, but
it's a small overhead in comparison with the iterative processing.

The default cursor type is keyset. I think it means that the keys are
stable, but updates in non-key columns will be reflected. But I have
never fully grasped keyset cursors so I could be wrong. What I have
seen is some really horrible query plans to set up a keyset-driven
cursor. Sure, it was in 6.5, but I see no reason to try it again.
2.Instead of Where Current Of
If I used Activity_Record _Num(The Primary Key)
which is distinctive would that work?


I didn't look to close on your code, but you should pick data from
the cursor which identifies the current row(s) being processed. (Note
that you could run a cursor over an aggregate, and thus update several
rows at same time. There are situations when this is a possible solution.
3.I use a statement called (UPDLOCK) in this part of the code:
...
Does that effect performance speed as well?


Not very likely. Since you don't seem to have an active transaction, the
effect of the UPDLOCK is none anyway.

Jul 20 '05 #9
Hello guys,

I have may be similar problem.
I need to recalculate a progressive balance based on a table with the
following structure:

EcrID varchar(15)
AccountNumber varchar(15)
EcrDate DateTime
EcrAmount float
ProgBalance float

The program has to calculate the progbalance which is for each row :
progbalance=pro gbalance + EcrAmount
Rows are sorted by AccountNumber and EcrDate.

Is it possible to that by a single update statement or do I have to
use cursors?

(Sorry for my english)

Many thanks for your help.
Jul 20 '05 #10

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

Similar topics

2
4702
by: berthelot samuel | last post by:
Hi everyone, I am currently trying to write a report based on a View of SQL Server. Basically, I have 3 tables : Hardware, SoftwareInstalled and Software with SoftwareInstalled that keeps track of all the software installed on each piece of hardware by referencing the primary keys of each table. So now, I have a request that retrieve information from those 3 tables giving a list of all the hardware with their details + the software...
1
6112
by: sam | last post by:
I'm trying to develop a stored procedure for a Crystal reports. Here's the stored procedure. The SP uses the following package for the REF CURSOR. CREATE OR REPLACE PACKAGE J1R_TYPES AS TYPE ReturnCursorType IS REF CURSOR; END J1R_TYPES; CREATE OR REPLACE PROCEDURE custom_report(
6
19020
by: Matthew Houseman | last post by:
All, I've created a synonym that points to a package over a database link like so: CREATE SYNONYM API_MYLINK FOR USER.CSAPI_V2@INSTANCE.DOMAIN.COM I've granted execute like so: grant execute on CSAPI_V2 to scott; When I attach to the database in C# using ODP.NET and attempt to
7
2225
by: Philip Mette | last post by:
Does anyone have any good references they could recommend on Cursor based SQL writing? I have to create SQL that can loop though records simular to VB loops and I have been told that this is the way to go. Any recommendations would be helpful.
10
1695
by: Neil | last post by:
I need to get two values from a complex SQL statement which returns a single record and use those two values to update a single record in a table. In order to assign those two values to variables and then use those variables in the UPDATE statement, I created a cursor and used Fetch Next.... Into. This way, I only have to call the complex SQL once instead of twice. This seems like the best way to go. However, I've always used cursors for...
13
5021
by: JayCallas | last post by:
I know this question has been asked. And the usual answer is don't use cursors or any other looping method. Instead, try to find a solution that uses set-based queries. But this brings up several questions / senarios: * I created several stored procedures that take parameters and inserts the data into the appropriate tables. This was done for easy access/use from client side apps (i.e. web-based).
1
7523
by: peaceburn | last post by:
Hi, I'm gonna pull my hair in the coming days with these DB2 stored procedures. So the issue, let's assume a simple stored procedure like this : CREATE PROCEDURE MYSCHEMA.PROCEDURE1 ( ) DYNAMIC RESULT SETS 1 ------------------------------------------------------------------------
7
6144
by: sri | last post by:
My environment is DB2v9.1, Windows/AIX and I am trying to compile the stored proc below and I am getting an error, "ERROR SQL0104N An unexpected token "WITH" was found following "User_ID = UsrID; ". Expected tokens may include: "SELECT". LINE NUMBER=11. SQLSTATE=42601" CREATE PROCEDURE TestProc(IN UsrID INT) LANGUAGE SQL SPECIFIC TestProc DYNAMIC RESULT SETS 1
3
2849
by: jenipriya | last post by:
Tables ------------ Employee (EmpID, EmpName,DeptID DateOfJoin, Sal, Addr) Finance (EmpID, Sal) Club (Clubname, EmpID, Fee, DateOfJoin) Leave (EmpID, Date) Department (DeptID, DeptName, NoOfEmployees)
0
8669
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
9122
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
9017
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
8963
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...
1
6588
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3125
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2069
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.