Cursor Or not. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor Or not.

Author

Topic

dtong004
Constraint Violating Yak Guru

281 Posts

Posted - 2001-07-12 : 10:37:42

It appears I got flamed by a group of people here. However, I am not intimated here.

First. I declare my position here. Cursor solution does provide flexible solution for sql job and maybe robust (see my example as following). However, it does trade of performance. It may runs 2 times slower to hundreds times slower than other "set" solution.

Second. I do accept their terminology from developers other than Microsoft side such as Oracle. Their defination of cursor, such as "select xxxx into yyy from ... where xxx"
It is their way of defining technology. Microsoft solution is not the only RDBM vendor. I do respect other developers terms of "cursor".

Third. If we look at the RDBMS with a magnifier, set solution vs. cursor solution.
At physical lever, they do the same work. Writing the data row by row, line by line. From disk I/O point of view, there is no difference. Then Why set solution faster?, I think I said earlier: RDBMS play a big role on such read-ahead, caching, etc.

Fourth: I admit I am an MCDBA. However, unlike some of your folks said, Microsoft Certs only thinks as Microsoft, I am quite open to other technology. It appears that some of folks here who stick with what Microsoft says. Again, I would say, Certification does broaden your knowledge, does not mean makes you an expert. In other side however, a person without a Certification can NOT gurantee themself as an expert. If 2 person with 2 years experience, one with certification, one is not. Who is better, it is obvious.

Fifth. I do have a cursor solution based procedure. I do want to see someone come up with a solution without using cusror. Put on challange! It is pretty much like merge two tables problem (see previous post)

Here is the problem. A company with 200 employee, I put them in EMP_V. Each may have some actions (or may not), each action captured with an action code, they might have multiple action code, with each code, they repeat or no repeat. Also, each of them may have some results (or may not). Same as action. The report will show emp, actions, number of actions, results, number of the results.

Here is my solution. The key is in second procedure.

/***********************************************************************************************
THIS PROCEDURE IS TO CREATE A REPORT FORMATED TO PUT
TWO INFORMATION TOGETHER. DIALS
AND CONTACTS WITH ACTION & RESULT CODE.

AUTHOR: DANIEL TONG
***********************************************************************************************/

CREATE PROCEDURE DIALS_CONTACTS_P
AS

SET NOCOUNT ON

DECLARE LOGIN_ID_CUR CURSOR
FOR
SELECT DISTINCT (LOGIN_ID) FROM EMP_V
ORDER BY LOGIN_ID

DECLARE @LOGIN_ID VARCHAR (10)

--RPT_DIALS_CONTACTS HOLDS DIALS AND CONTACTS INFO.

TRUNCATE TABLE RPT_DIALS_CONTACTS

OPEN LOGIN_ID_CUR

FETCH LOGIN_ID_CUR INTO @LOGIN_ID
WHILE @@FETCH_STATUS=0
BEGIN
EXEC RPT_DNC_P @LOGIN_ID --CALL THIS PROCEDURE TO POPULATE THE RECORDS.
FETCH NEXT FROM LOGIN_ID_CUR INTO @LOGIN_ID
END
CLOSE LOGIN_ID_CUR
DEALLOCATE LOGIN_ID_CUR

RETURN
GO

CREATE PROCEDURE RPT_DNC_P(@CLOGIN_ID VARCHAR(10))
AS

SET NOCOUNT ON

DECLARE @DIAL_CUR CURSOR
DECLARE @CONTACT_CUR CURSOR

SET @DIAL_CUR= CURSOR FOR
SELECT [ACTION CODE], [COUNT] FROM DAILY_DIALS_V
WHERE [LOGIN]=@CLOGIN_ID

SET @CONTACT_CUR = CURSOR
FOR
SELECT [RESULT CODE],[COUNT] FROM DAILY_CONTACTS_V
WHERE [LOGIN]=@CLOGIN_ID

DECLARE @CACTIONCODE AS VARCHAR(2)
DECLARE @CRESULTCODE AS VARCHAR(3)
DECLARE @D_COUNT AS INT
DECLARE @C_COUNT AS INT

OPEN @DIAL_CUR
OPEN @CONTACT_CUR

FETCH @DIAL_CUR INTO @CACTIONCODE, @D_COUNT
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @CONTACT_CUR INTO @CRESULTCODE, @C_COUNT
IF @@FETCH_STATUS = 0
INSERT RPT_DIALS_CONTACTS (CLOGIN_ID, CACTIONCODE, AC_USAGE, CRESULTCODE, RC_USAGE)
VALUES (@CLOGIN_ID, @CACTIONCODE, @D_COUNT, @CRESULTCODE, @C_COUNT)
ELSE
INSERT RPT_DIALS_CONTACTS (CLOGIN_ID, CACTIONCODE, AC_USAGE)
VALUES (@CLOGIN_ID, @CACTIONCODE, @D_COUNT)
FETCH NEXT FROM @DIAL_CUR INTO @CACTIONCODE, @D_COUNT
END

IF @@FETCH_STATUS= -1
BEGIN
FETCH NEXT FROM @CONTACT_CUR INTO @CRESULTCODE, @C_COUNT
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT RPT_DIALS_CONTACTS (CLOGIN_ID, CRESULTCODE, RC_USAGE)
VALUES (@CLOGIN_ID, @CRESULTCODE, @C_COUNT)
FETCH NEXT FROM @CONTACT_CUR INTO @CRESULTCODE, @C_COUNT
END
END

CLOSE @CONTACT_CUR
CLOSE @DIAL_CUR
DEALLOCATE @CONTACT_CUR
DEALLOCATE @DIAL_CUR

RETURN

I would be very glad if anyone come up with clean solution without using a query. The total about serveral thousands of rows.

Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources