For loop in sql server

Question

For loop in sql server

VRT

SSCertifiable

Points: 5776
More actions
June 22, 2008 at 10:27 am

#193086

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.
Thank You

Viewing 15 posts - 1 through 15 (of 40 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

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.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

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.

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)

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 3

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

VRT SSCertifiable Points: 5776 More actions · Answer 4

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.

Thank You

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

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.

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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

venki (6/23/2008)
...
Now I used Cursors and while loop and completed the task.
Thanks a lot for your suggestions.

...

:angry:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 7

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

VRT SSCertifiable Points: 5776 More actions · Answer 8

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.

Thank You

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 9

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

VRT SSCertifiable Points: 5776 More actions · Answer 10

pduplessis (6/23/2008)
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

OK I will send it. I will send the stored procedure in document. There is nothing in the dtsx file. only one ExecuteSQL task. In that I have given a query as EXEC sp_venkiSP.

If it is possible to delete then immediatly delete this.

Thank You

VRT SSCertifiable Points: 5776 More actions · Answer 11

Pduplessis,

Have you checked my Stored Procedure?

Thank You

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

That's what happens when you "send" it to just one person, Venki... why don't you post the procedure here so we can all lend a hand... 😉

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

VRT SSCertifiable Points: 5776 More actions · Answer 13

Hi,

Your suggestions are always welcome. No body responded for this one yesterday that's why I deleted it.

I have kept it for halfen hour and taken back. Now I am keeping again and will not delete it.

In the execute SQL task nothing is there. I have given only

EXEC sp_TransferIH

Thank You

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Wow... lots of RBAR there 😀

For starters, you can replace the following WHILE loop...

declare @cnt int

declare @UnitCount int

set @cnt=1

set @UnitCount=1

while @cnt<len(@s_Table_PkgNumber)

begin

if(substring(@s_Table_PkgNumber,@cnt,1)=', ')

begin

set @UnitCount=@UnitCount+1

end

set @cnt=@cnt+1

End

... with this...

SET @UnitCount = LEN(@s_Table_PkgNumber)-LEN(REPLACE(@s_Table_PkgNumber,',',''))+1

That one is really important because once you've fixed that, column can be produced just using a simple select which means you can convert the Cursor into a simple SELECT. That means that you can very easily do INSERT/SELECTS instead of using a cursor and things will be very much faster.

The following...

if @s_Quote_Type=' '

set @s_Quote_Type=Null

... can simply be replace by ...

SET @s_Quote_Type = NULLIF(@s_Quote_Type,' ')

I'm on my way to work and I'll try to take a look at this in more detail tonight. In the mean time... experiment with the code suggestions I've made so far...

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