cursor question

Question

cursor question

FDS_

SSC Enthusiast

Points: 183
More actions
September 16, 2013 at 1:14 pm

#279866

Hi guys,
Can i load a cursor from a procedure?
Like
DECLARE cursor_importedPatients CURSOR FOR
EXEC procedure
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

fernando.desena (9/16/2013)
Hi guys,
Can i load a cursor from a procedure?
Like
DECLARE cursor_importedPatients CURSOR FOR
EXEC procedure
Thanks!

No that won't work quite like that. You could create a temp table first and insert into the temp table and then define your cursor to use the temp table. Why do you need a cursor? They are generally very slow and should be used for a few administrative tasks. With more details I bet we could find a way to eliminate the cursor entirely. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

FDS_ SSC Enthusiast Points: 183 More actions · Answer 2

Emmm its like this:

Some process imports the information from a file into a DB table. Here is the thing, i have to take, one by one, the rows of this table and persist them into other 4 tables.

For example:

A process imports clients from a file into a "importedClients" table. Later, you see the importation details (like "50 new clientes, 2 duplicated, etc.") and want, efectively, to do the importation. So, you will execute a procedure which opens a cursor for the records of the "importedClients" table and insert them in "clientContact", "clients", "clientsSocialWork", "etc".

The process will import 200.000 registers :S

And i made that question because i have to import only the records that doesnt exist in other table. Yes, i can make that in a SELECT but i already have other SP for that thing.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

FDS_ (9/16/2013)
Emmm its like this:
Some process imports the information from a file into a DB table. Here is the thing, i have to take, one by one, the rows of this table and persist them into other 4 tables.
For example:
A process imports clients from a file into a "importedClients" table. Later, you see the importation details (like "50 new clientes, 2 duplicated, etc.") and want, efectively, to do the importation. So, you will execute a procedure which opens a cursor for the records of the "importedClients" table and insert them in "clientContact", "clients", "clientsSocialWork", "etc".
The process will import 200.000 registers :S
And i made that question because i have to import only the records that doesnt exist in other table. Yes, i can make that in a SELECT but i already have other SP for that thing.

A cursor over 200,000 rows with a stored proc call for each line is going to be a performance issue for certain.

As I said previously if you want to use a cursor for the output of a stored proc you will have first create the temp table and then insert into it with your proc.

Create table #MyTable

(

Column definition to match the output of your proc.

)

insert #MyTable

exec MyProc

**cough**

cursor goes here

**cough**

i have to take, one by one, the rows of this table and persist them into other 4 tables.

I am 99.99999% certain this can be done without RBAR processing. It would take your 200,000 row insert from 30 minutes to a few seconds.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

FDS_ SSC Enthusiast Points: 183 More actions · Answer 4

what does "RBAR processing" means? sry about my ignorance...

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

RBAR = "Row-By-Agonizing-Row".

It's something we try to avoid in databases. Rather we try to do all of the rows at once.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

FDS_ (9/16/2013)
what does "RBAR processing" means? sry about my ignorance...

Sorry. That is an abbreviation around here (coined by Jeff Moden) that means Row By Agonizing Row. It is used to defined looping which is just horribly inefficient in sql server.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

siggemannen SSCrazy Points: 2436 More actions · Answer 7

FDS_ (9/16/2013)
Hi guys,
Can i load a cursor from a procedure?
Like
DECLARE cursor_importedPatients CURSOR FOR
EXEC procedure
Thanks!

Something like this can be done, but as other mentioned, it's usually not a good idea!

IF OBJECT_ID('SP_TEST') IS NOT NULL

DROP PROCEDURE SP_TEST

GO

CREATE PROCEDURE SP_TEST

@pParam_1 INT

,@pCursor CURSOR VARYING OUTPUT

AS

SET@pCursor = CURSOR READ_ONLY FORWARD_ONLY FOR

SELECTc.cust_no

FROMcustomer c

WHEREcust_id = @pParam_1

OPEN @pCursor

GO

DECLARE @cr_X CURSOR

,@cust_no VARCHAR(30)

EXECSP_TEST 2, @pCursor = @cr_x OUTPUT

FETCH NEXT FROM @cr_x INTO @cust_no

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @cust_no

FETCH NEXT FROM @cr_x INTO @cust_no

END

CLOSE @CR_X

DEALLOCATE @CR_X

GO

FDS_ SSC Enthusiast Points: 183 More actions · Answer 8

Thanks all! i did it that way and its working ok.

Now i will take your advise and i will try to do a masive inserts. Like:

INSERT dbo.Clients

SELECT id, name

FROM dbo.ClientsToImport.

I think it will be faster than my actual way. I will let you know about this !!

Again,ty all.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

FDS_ (9/17/2013)
Thanks all! i did it that way and its working ok.
Now i will take your advise and i will try to do a masive inserts. Like:
INSERT dbo.Clients
SELECT id, name
FROM dbo.ClientsToImport.
I think it will be faster than my actual way. I will let you know about this !!
Again,ty all.

"Massive Inserts"... you need to be aware that each system has a tipping point and that each "massive insert" can also blow the log file out to incredible sizes. With that thought in mind, be a bit careful as to how big your "massive inserts" are. Sometimes it's a very good thing to split such inserts up into smaller batches. It's still "set based"... just smaller, iterative sets.

--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)

FDS_ SSC Enthusiast Points: 183 More actions · Answer 10

Well i did it and its working fine! Thanks all for the answers.

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

FDS_ (9/25/2013)
Well i did it and its working fine! Thanks all for the answers.

It would be great if you could post what you did. It may very well help someone else out that comes along later and views this thread.

Glad you were able to solve your issue.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/