Can you please help me??

Question

Can you please help me??

techmarimuthu

SSC Enthusiast

Points: 137
More actions
August 26, 2013 at 10:41 pm

#395375

I have some doubts in using of loops in sql server 2005
can you please anyone help me?
Thanks in advance

Viewing 14 posts - 1 through 13 (of 13 total)

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

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

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.

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)

Steve Jones - SSC Editor SSC Guru Points: 736468 More actions · Answer 2

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.

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

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[/url]

@SeanPearceSQL

About Me[/url]

Sigerson Hall of Fame Points: 3697 More actions · Answer 4

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 - SSC Editor SSC Guru Points: 736468 More actions · Answer 5

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.

Snigdha Vartak Default port Points: 1486 More actions · Answer 6

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 SSC Guru Points: 1004405 More actions · Answer 7

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.

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)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

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.

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)

Sigerson Hall of Fame Points: 3697 More actions · Answer 9

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 10

I am certanly not a SSIS expert so I can't give you more details. Others on this forum might provide better help on that. But as far as I know you can create good conditional logic inside a SSIS package.

So instead of importing the CSV to a temp table and then analyse and process the lines you could try to analyse and proces the lines inside SSIS and then writing the results to the specific tables.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

RandomEvent SSCrazy Points: 2963 More actions · Answer 11

Why not add an extra column as specified earlier and then apply your logic to a case statement to populate the column.

You can then split the data out as required.

Or use SSIS and a conditional Split or milti-cast.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

Sigerson (8/29/2013)
Doing all of that within one SQL statement or a With block is beyond me,...

It's beyond me, too. But then again, I wouldn't even try to do it with a single statement. Avoiding the loop or doing something "set based" does [font="Arial Black"]NOT [/font]mean doing it "within one SQL statement or a Withblock". Multiple set-based passes will be much faster than using a loop for this.

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

Sigerson Hall of Fame Points: 3697 More actions · Answer 13

Jeff Moden (8/29/2013)
Sigerson (8/29/2013)
Doing all of that within one SQL statement or a With block is beyond me,...
It's beyond me, too. But then again, I wouldn't even try to do it with a single statement. Avoiding the loop or doing something "set based" does [font="Arial Black"]NOT [/font]mean doing it "within one SQL statement or a Withblock". Multiple set-based passes will be much faster than using a loop for this.

I honestly don't understand what you mean by that. What would make it set-based? Is there an article I can chew on to get a grasp of this?

Thanks,

Sigerson

"No pressure, no diamonds." - Thomas Carlyle