Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can you please help me?? Expand / Collapse
Author
Message
Posted Monday, August 26, 2013 10:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
I have some doubts in using of loops in sql server 2005
can you please anyone help me?

Thanks in advance
Post #1488547
Posted Tuesday, August 27, 2013 12:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 35,263, Visits: 31,749
techmarimuthu (8/26/2013)
I have some doubts in using of loops in sql server 2005
can you please anyone help me?

Thanks in advance


Probably but you need to ask the actual question you have. You should also take a peek at the first link in my signature line below.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488576
Posted Tuesday, August 27, 2013 8:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:41 PM
Points: 31,080, Visits: 15,525
Don't use loops if you can avoid it. If you have a specific issue, as Jeff mentioned, we can help you find a way to do it without a loop.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1488793
Posted Wednesday, August 28, 2013 5:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 914, Visits: 2,876
techmarimuthu (8/26/2013)
I have some doubts in using of loops in sql server 2005
can you please anyone help me?

Thanks in advance

My general rule of thumb is:
It's OK to use loops for manipulating database objects
It's NOT OK to use loops for manipulating data




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1489144
Posted Wednesday, August 28, 2013 7:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 10:20 AM
Points: 271, Visits: 819
I respect the general wisdom about not using cursors and loops and have usually found a way to avoid them.

But I have one data upload from a vendor in a CSV file. I pull it into a temp table in SS with an SSIS process, which then runs a stored procedure. The SP creates a cursor and loops through the temp file, evaluating the vendor's returned data. Depending on the content of each data point, I may write one thing to one table or another value to a different table.

I don't know how I could possible build all that updating logic into any simpler form and it works well enough and quickly enough for me.

So it seems to me there are times that a cursor and a loop are the only possible solutions. But I'm always ready to learn.

Can anyone point me toward a discussion of why and how all cursors and loops can be done away with?


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1489212
Posted Wednesday, August 28, 2013 8:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:41 PM
Points: 31,080, Visits: 15,525
I don't know you can do away with all of them. Most you can, and there isn't a summary that I know of. There are examples of where a cursor is done away with, but why don't you present details on the way you handle the import? Perhaps there is a better way, perhaps not.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1489276
Posted Wednesday, August 28, 2013 4:02 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 11:34 AM
Points: 522, Visits: 235
Following links might be of help to you:
http://technet.microsoft.com/en-us/library/ms178642(v=sql.90).aspx
http://technet.microsoft.com/en-us/library/ms141724.aspx

There are lots of good articles on technet that you can refer to.

If you can specify your actual doubts, someone can help you out
Post #1489449
Posted Wednesday, August 28, 2013 5:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 35,263, Visits: 31,749
Sigerson (8/28/2013)
I respect the general wisdom about not using cursors and loops and have usually found a way to avoid them.

But I have one data upload from a vendor in a CSV file. I pull it into a temp table in SS with an SSIS process, which then runs a stored procedure. The SP creates a cursor and loops through the temp file, evaluating the vendor's returned data. Depending on the content of each data point, I may write one thing to one table or another value to a different table.

I don't know how I could possible build all that updating logic into any simpler form and it works well enough and quickly enough for me.

So it seems to me there are times that a cursor and a loop are the only possible solutions. But I'm always ready to learn.

Can anyone point me toward a discussion of why and how all cursors and loops can be done away with?


I know very little of SSIS. If I were doing this in T-SQL, I'd simply add a column that identified which table I'd want the row to go to. Then I'd do a single pass "INSERT" for each table. I don't know how you are evaluating which rows go to what table but even 5 passes because you have 5 tables is going to be an awful lot faster than using a loop against a file.

Think "columns", not "rows".


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1489477
Posted Wednesday, August 28, 2013 5:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 35,263, Visits: 31,749
techmarimuthu (8/26/2013)
I have some doubts in using of loops in sql server 2005
can you please anyone help me?

Thanks in advance


You still haven't even identified what your doubts are. Are you not interested?


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1489479
Posted Thursday, August 29, 2013 6:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 10:20 AM
Points: 271, Visits: 819
Thanks to everyone who answered me.

I didn't mean to imply that I have doubts about avoiding cursors and loops. I do avoid them whenever possible because I respect the advice of the people who say it's good practice. I will certainly read the recommended articles.

But I'm still learning where to draw the line. Here's my current requirement, where I found that only a cursor provided the control I needed, given my skill set:

I have a CSV of invoice information coming in from a vendor, and it gets moved into a temporary table in the d/b for the following steps:.

1. I have to create and save to disk an RTF exception report which I create using the FSO.
2. I have to examine every field in every row and print the row number and field name of any data that's invalid or missing on the exception report.
3. If the file passes the validation phase, then I rewind the cursor and begin the update phase.
4. If the account has been put on hold or closed out, print a message on the exception report.
5. I write the invoice information to one table and four other values to two other tables.
6. The names and addresses on the invoices may have changed from the information we sent the vendor, so changes must be identified and updated to the tables, and printed on the exception report.
7. In one case, a decision on what to update is based on inspection of multiple interrelated fields. For all of these 'evaluated' updates, I have to print an explanatory message to the exception report.

Doing all of that within one SQL statement or a With block is beyond me, so I used a cursor. But like I said, I'm always willing to learn.


Thanks again,




Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1489685
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse