Stored Procedure Problem Help!

Question

Stored Procedure Problem Help!

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

My feedback would be that you shouldn't learn how to use a Cursor until you've really learned and understand Set-Based progamming.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 2

Just for giggles:

-- Source table

CREATE TABLE #Table1

(

row_id INTEGER NOT NULL PRIMARY KEY,

data CHAR(1) NOT NULL

);

-- Target table

CREATE TABLE #Table2

(

row_id INTEGER NOT NULL PRIMARY KEY,

data CHAR(1) NOT NULL

);

-- Sample data

INSERT #Table1 (row_id, data) VALUES (1, 'A');

INSERT #Table1 (row_id, data) VALUES (2, 'B');

INSERT #Table1 (row_id, data) VALUES (3, 'C');

INSERT #Table1 (row_id, data) VALUES (4, 'D');

INSERT #Table1 (row_id, data) VALUES (5, 'E');

INSERT #Table1 (row_id, data) VALUES (6, 'F');

INSERT #Table1 (row_id, data) VALUES (7, 'G');

INSERT #Table1 (row_id, data) VALUES (8, 'H');

INSERT #Table1 (row_id, data) VALUES (9, 'I');

-- Show the data in #Table1

SELECT row_id,

data

FROM #Table1

ORDER BY

row_id ASC;

GO

-- Cursor definition (reads #Table1)

-- see http://msdn.microsoft.com/en-us/library/ms180169.aspx

-- for a description and further examples

DECLARE curRead

SCROLL CURSOR

FOR

SELECT row_id,

data

FROM #Table1

ORDER BY

row_id ASC

FOR READ ONLY;

-- Populate the cursor from the SELECT statement

-- in its definition

OPEN curRead;

-- Loop to read each row from the cursor

WHILE (1 = 1)

BEGIN

-- Variables to hold column values from

-- each row in the cursor

DECLARE @row_id INTEGER,

@data CHAR(1);

-- Fetch the next row from the cursor

FETCH NEXT

FROM curRead

INTO @row_id,

@data;

-- If the row has been deleted, try the next row

IF @@FETCH_STATUS = -2 CONTINUE;

-- If there are no more rows, exit the loop

IF @@FETCH_STATUS = -1 BREAK;

-- Add the current column data as a new record

-- in #Table2

INSERT #Table2

(row_id, data)

VALUES (@row_id, @data);

END;

-- Close the cursor, and release the reference to it

CLOSE curRead; DEALLOCATE curRead;

-- Show the data in #Table2

SELECT row_id,

data

FROM #Table1

ORDER BY

row_id ASC;

-- Drop the tempoary tables

DROP TABLE #Table1;

DROP TABLE #Table2;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Heh... you must really be bored, Paul. 🙂 It does show, however, what a PITA a Cursor is compared to the simple Set-Based methods.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 4

Jeff Moden (3/15/2010)
Heh... you must really be bored, Paul. 🙂

Actually, I had to think quite hard to get that cursor right. I am not proud of it though 😎

Jeff Moden (3/15/2010)
It does show, however, what a PITA a Cursor is compared to the simple Set-Based methods.

That was an objective, yes. Perfect set-up to post the one-line super-fast set-based equivalent.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 5

...and I did learn something. The keyset cursor I ended up creating is initialized using a plan that features a Sequence Project operator using a special internal-only version of the ROW_NUMBER ranking function: [Expr1005] = Scalar Operator(i4_row_number). This returns an INTEGER instead of the normal BIGINT. I find that sort of thing interesting!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Paul White (3/15/2010)
Jeff Moden (3/15/2010)
Heh... you must really be bored, Paul. 🙂
Actually, I had to think quite hard to get that cursor right. I am not proud of it though 😎
Jeff Moden (3/15/2010)
It does show, however, what a PITA a Cursor is compared to the simple Set-Based methods.
That was an objective, yes. Perfect set-up to post the one-line super-fast set-based equivalent.
Paul

It is good to know that you're not proud of it - other than creating a setup for the set-based method.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events