Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » For loop in sql server
Add to briefcase
41 posts, Page 1 of 5
12345»»»

For loop in sql server Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
VRT
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: Wednesday, July 27, 2011 6:55 AM
Points: 142, Visits: 448
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
RBarryYoung
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: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376
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
Jeff Moden
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 @ 11:54 PM
Points: 33,113, Visits: 27,041
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #521463
pduplessis-723389
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 03, 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
VRT
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: Wednesday, July 27, 2011 6:55 AM
Points: 142, Visits: 448
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
Jeff Moden
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 @ 11:54 PM
Points: 33,113, Visits: 27,041
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #522021
RBarryYoung
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: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376
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
pduplessis-723389
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 03, 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
VRT
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: Wednesday, July 27, 2011 6:55 AM
Points: 142, Visits: 448
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
pduplessis-723389
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 03, 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
41 posts, Page 1 of 5
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.