SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can you please help me??


Can you please help me??

Author
Message
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87886 Visits: 41124
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63971 Visits: 19117
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
My Blog: www.voiceofthedba.com
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1792 Visits: 3432
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
Sigerson
Sigerson
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 Visits: 1232
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63971 Visits: 19117
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
My Blog: www.voiceofthedba.com
Snigdha Vartak
Snigdha Vartak
Mr or Mrs. 500
Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)

Group: General Forum Members
Points: 570 Visits: 247
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 :-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87886 Visits: 41124
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87886 Visits: 41124
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sigerson
Sigerson
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 Visits: 1232
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search