gotcha, no-cursor belivers! - 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)

gotcha, no-cursor belivers!

Author

Topic

dtong004
Constraint Violating Yak Guru

281 Posts

Posted - 2001-07-13 : 15:02:07

I think it is better to put on a new thread and we can develop our discussion from there (as some of you requested).

For those of you who believes who is thinking cursor is just a waste and discard of using of cursor, this is a challange.
Utill I see your clean solution, I still hold my position that cursor is flexible and robust soution for some cases like this.
However, we should avoid to use it as it IS affect the performance.

Here is the example database as some of you requested.

CREATE TABLE EMP (EMP_ID INT, EMP_NAME VARChAR(30), TITLE VARCHAR(20), SUPERVISOR_ID INT)
GO
CREATE TABLE ACTION (EMP_ID INT, ACTION_CODE VARCHAR(2), NUM_ACTIONS INT, ACTION_DATE DATETIME DEFAULT CURRENT_TIMESTAMP)
GO
CREATE TABLE RESULT(EMP_ID INT, RESULT_CODE VARCHAR(3), NUM_RESULTS INT, RESULT_DATE DATETIME DEFAULT CURRENT_TIMESTAMP)
GO

-- CREATE ANOTHER TABLE TO HOLD THE RESULTS.
CREATE TABLE REPORT_ACTION_RESULT (EMP_ID INT, ACTION_CODE VARCHAR(2), NUM_ACTINOS INT, RESULT_CODE VARCHAR(3), NUM_RESULT INT)
GO

INSERT EMP (EMP_ID) VALUES (11)
INSERT EMP (EMP_ID) VALUES (12)
INSERT EMP (EMP_ID) VALUES (13)
GO

INSERT ACTION (EMP_ID, ACTION_CODE, NUM_ACTIONS) VALUES (11, 'XX', 3)
INSERT ACTION (EMP_ID, ACTION_CODE, NUM_ACTIONS) VALUES (11, 'XY', 4)
INSERT ACTION (EMP_ID, ACTION_CODE, NUM_ACTIONS) VALUES (11, 'YY', 2)
INSERT ACTION (EMP_ID, ACTION_CODE, NUM_ACTIONS) VALUES (12, 'XX', 1)
INSERT ACTION (EMP_ID, ACTION_CODE, NUM_ACTIONS) VALUES (12, 'YY', 5)
INSERT ACTION (EMP_ID, ACTION_CODE, NUM_ACTIONS) VALUES (13, 'XZ', 3)
INSERT ACTION (EMP_ID, ACTION_CODE, NUM_ACTIONS) VALUES (13, 'ZZ', 3)
INSERT ACTION (EMP_ID, ACTION_CODE, NUM_ACTIONS) VALUES (13, 'YZ', 3)
INSERT ACTION (EMP_ID, ACTION_CODE, NUM_ACTIONS) VALUES (11, 'QR', 3)

GO

INSERT RESULT (EMP_ID, RESULT_CODE, NUM_RESULTS) VALUES (11, 'AAA', 3)
INSERT RESULT (EMP_ID, RESULT_CODE, NUM_RESULTS) VALUES (11, 'AAB', 6)
INSERT RESULT (EMP_ID, RESULT_CODE, NUM_RESULTS) VALUES (12, 'AAA', 2)
INSERT RESULT (EMP_ID, RESULT_CODE, NUM_RESULTS) VALUES (12, 'ABB', 1)
INSERT RESULT (EMP_ID, RESULT_CODE, NUM_RESULTS) VALUES (12, 'ACC', 2)
INSERT RESULT (EMP_ID, RESULT_CODE, NUM_RESULTS) VALUES (12, 'DDA', 1)
INSERT RESULT (EMP_ID, RESULT_CODE, NUM_RESULTS) VALUES (13, 'CCC', 12)
GO

/* FOLLOWING DATA ENTRY IS TO PROVIDE AN EXPECTED EXAMPLE RESULT FROM ABOVE DATA

INSERT REPORT_ACTION_RESULT VALUES (11, 'XX', 3, 'AAA', 3)
INSERT REPORT_ACTION_RESULT VALUES (11, 'XY', 4, 'AAB', 6)
INSERT REPORT_ACTION_RESULT VALUES (11, 'YY', 2, NULL, NULL)
INSERT REPORT_ACTION_RESULT VALUES (12, 'XX', 1, 'AAA', 2)
INSERT REPORT_ACTION_RESULT VALUES (12, 'YY', 5, 'ABB', 1)
INSERT REPORT_ACTION_RESULT VALUES (12, NULL, NULL, 'ACC', 2)
INSERT REPORT_ACTION_RESULT VALUES (12, NULL, NULL, 'DDA', 1)
INSERT REPORT_ACTION_RESULT VALUES (13, 'XZ', 3, 'CCC', 12)
INSERT REPORT_ACTION_RESULT VALUES (13, 'ZZ', 3, NULL, NULL)
INSERT REPORT_ACTION_RESULT VALUES (13, 'YZ', 3, NULL, NULL)
INSERT REPORT_ACTION_RESULT VALUES (13, 'QR', 3, NULL, NULL)

*/

I solve this problem with cursor based solution. I would like to see your solution without using cursor.
I think in OLTP system, there would be more often of using cursor.

Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources