For loop in sql server

Question

For loop in sql server

Viewing 11 posts - 31 through 40 (of 40 total)

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

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

Thanks for the feedback...

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

Jim Russell (6/27/2008)
Jeff -- I am continually amazed at the effort you put in to help us all out. Thanks very much!!

I am with Jim on this one, it is people like you Jeff that make us want to strive to achieve so much more.

Even though we can make it work in 500 different ways, you always have the right way

~PD

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 3

pduplessis (6/28/2008)
Jim Russell (6/27/2008)
Jeff -- I am continually amazed at the effort you put in to help us all out. Thanks very much!!
I am with Jim on this one, it is people like you Jeff that make us want to strive to achieve so much more.
Even though we can make it work in 500 different ways, you always have the right way
~PD

Now you guys are going to embarrass me :blush: Thanks guys...

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

khelelo SSC Journeyman Points: 83 More actions · Answer 4

Hi,

Regarding this subject, I am battling in creating a proc to bcp distinct text files into distinct multi - tables by using one table as a parameter. The parameter table has all the table and file names. I have used cursors but their fail.

eg:

declare @filename varchar(100)

declare @bcpCommand as nvarchar(4000)

declare @defaultdirs as nvarchar(4000)

declare @dd as varchar(2)

--SET @bcpCommand = (' bcp General.dbo.'+@filename + ' in '+ @DefaultDirs +'\' +@filename +'.' + @dd + ' -T -f'+@DefaultDirs +'\Format\'+@filename +'.fmt' + ' -k -e'+@DefaultDirs +'\Error\'+@filename +'.err' )

--set @filename = (select DataSource FROM HO_Control_Table)

--select DataSource FROM HO_Control_Table

declare Next_Table_Cursor CURSOR FOR

select Filename FROM Parameter_Control_Table;

OPEN Next_Table_Cursor;

FETCH NEXT FROM Next_Table_Cursor;

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM Next_Table_Cursor;

END;

CLOSE Next_Table_Cursor;

DEALLOCATE Next_Table_Cursor;

GO

Please help!!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

This should be posted as it's own topic, in the appropiate forum (which is probably NOT Integration Services). When you do, be sure to include the actual error message.

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

Kamal-970618 SSC Rookie Points: 43 More actions · Answer 6

Hi Jeff Moden,

You have specified that instead of using While loop we can use set based code... Can you give us a idea of wat that set based code means and how to achieve that.

🙂

Cheers & Regards,

Kamal

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 7

A good starting point is in Jeffs sig:

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."

There is deep wisdom in that...

pandeharsh Ten Centuries Points: 1106 More actions · Answer 8

Hi,

It would be kind, if you explain set base code with suitable example.

Why we should use set base code instead of loop and cursor.

Tanks

[font="Verdana"]Regards
Kumar Harsh[/font]

:unsure:

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 9

pandeharsh (9/21/2009)
Hi,
It would be kind, if you explain set base code with suitable example.
Why we should use set base code instead of loop and cursor.
Tanks

http://www.lmgtfy.com/?q=Why+we+should+use+set+base+code+instead+of+loop+and+cursor

example result: http://www.codeproject.com/KB/database/SetAndProceduralSQL.aspx?display=Print

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

jcrawf02 (9/23/2009)
pandeharsh (9/21/2009)
Hi,
It would be kind, if you explain set base code with suitable example.
Why we should use set base code instead of loop and cursor.
Tanks
http://www.lmgtfy.com/?q=Why+we+should+use+set+base+code+instead+of+loop+and+cursor
...

Jon, did you actually try this? You should, especially the first results, ... 🙂

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

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 11

RBarryYoung (9/23/2009)
jcrawf02 (9/23/2009)
pandeharsh (9/21/2009)
Hi,
It would be kind, if you explain set base code with suitable example.
Why we should use set base code instead of loop and cursor.
Tanks
http://www.lmgtfy.com/?q=Why+we+should+use+set+base+code+instead+of+loop+and+cursor
...
Jon, did you actually try this? You should, especially the first results, ... 🙂

I did, thought that was funny. Especially since the second one was the same question in a different thread.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."