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 12345»»»

For loop in sql server Expand / Collapse
Author
Message
Posted Sunday, June 22, 2008 10:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
Do we have for loop or for each statement in SQL Server?

I tried to use the for loop in my stored procedure but I haven't found it's working and it is throwing error.


Thanks a lot,
Venki
Post #521425
Posted Sunday, June 22, 2008 11:31 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
It's "While ...", but I would recommend that you figure out how to write set-based code instead of relying on loop-based techniques. The performance difference is about an order of magnitude.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #521436
Posted Sunday, June 22, 2008 4:27 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
venki (6/22/2008)
Do we have for loop or for each statement in SQL Server?

I tried to use the for loop in my stored procedure but I haven't found it's working and it is throwing error.


Venki,

Tell us what you're trying to do with the loop... lots of things can be done without one and will usually be much, much more performant.


--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 #521463
Posted Sunday, June 22, 2008 11:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
Venki,

In SSIS you get for each loops which do all kinds of things, and really assist with managing the loads into your environment.
BUT (and this is the big but), none of these are actually row based loops, and ultimately they will perform set based operations.

Agree with the above, post what you are trying to do, While and cursors are swearwords on the RDBMS

~PD
Post #521532
Posted Monday, June 23, 2008 10:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
I got a doubt when I am trying to create a Stored Procedure.

I am pulling the data i.e recordset by written a query from different tables.

I supposed to modify the data by taking each row and each column.

When I searched in Google, I haven't get any link for this. It is giving links to Oracle. That's why I have asked this question.

Now I used Cursors and while loop and completed the task.

Thanks a lot for your suggestions.


Thanks a lot,
Venki
Post #522008
Posted Monday, June 23, 2008 10:32 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
venki (6/23/2008)
I got a doubt when I am trying to create a Stored Procedure.

I am pulling the data i.e recordset by written a query from different tables.

I supposed to modify the data by taking each row and each column.

When I searched in Google, I haven't get any link for this. It is giving links to Oracle. That's why I have asked this question.

Now I used Cursors and while loop and completed the task.

Thanks a lot for your suggestions.


I don't know for sure because your description of what you're trying to do is very generic... but I believe you've made a mistake by using a Cursor and While loop instead of some good set based code. Good luck...



--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 #522021
Posted Monday, June 23, 2008 2:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
venki (6/23/2008)
...
Now I used Cursors and while loop and completed the task.

Thanks a lot for your suggestions.

...



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #522170
Posted Monday, June 23, 2008 11:44 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
Venki,

If your info is not sensitive, post your code and .dtsx file, and we will see what we can do.
ESPECIALLY, since you have SSIS, cursors should be forbidden

~PD
Post #522336
Posted Tuesday, June 24, 2008 9:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
Yes, but what can I do. Dead line has came up. I have to submit it yesterday that's why I have created the stored procedure and I executed it in executeSQL task.

Thanks a lot,
Venki
Post #522618
Posted Tuesday, June 24, 2008 9:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
Agreed, you do whatever it takes to get the job done.

HOWEVER, now that you have implemented, why not go back and have a look at fixing it the right way?
Post the dtsx if you can
Post #522620
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse