Want to use WHILE loop in Stored procedure

Question

Post reply

Want to use WHILE loop in Stored procedure

amitabhssinha

SSC-Addicted

Points: 481
More actions
December 29, 2009 at 9:42 pm

#217924

I have a stored procedure that will take date period as one of the input, say, from 20th Dec to 27th Dec. Now I want to take one by one date into stored procedure as input and return all the values coming of the date period.
How can I proceed, Please help me.
December 30, 2009 at 12:38 am

This was removed by the editor as SPAM

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

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

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

Chandra Sekhara Vyas Dhara

SSChampion

Points: 10207

December 29, 2009 at 10:41 pm

#1097316

Is the both the inputs are standard or varies?

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

ChiragNS

One Orange Chip

Points: 26137

December 29, 2009 at 11:25 pm

#1097322

Please give more details.

"Keep Trying"

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 3

amitabhssinha

SSC-Addicted

Points: 481

December 29, 2009 at 11:40 pm

#1097325

Date period is variable, it depends on user

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 4

Thanks Stewart for your valuable suggestion.

I had thought the same go earlier.

ramvbabu Old Hand Points: 372 More actions · Answer 5

You can use Datediff function also. Like the Datediff('d',@FromDate,tableData) > =0 and Datediff('d',@ToDate,tableData) <=0.

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 6

DECLARE @Date datetime

SELECT @Date = @StartDate

WHILE (@Date <= @EndDate)

BEGIN

-- Process for @Date value

SELECT @Date = dateadd(d,1,@Date)

END

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

amitabhssinha (12/29/2009)
I have a stored procedure that will take date period as one of the input, say, from 20th Dec to 27th Dec. Now I want to take one by one date into stored procedure as input and return all the values coming of the date period.
How can I proceed, Please help me.

Don't do it like this. Write a query which works, then encapsulate it into a stored procedure. A stored procedure is a query (or set of statements) which has been written, debugged, optimised and documented, ready for reuse.

As others have stated, there are many ways to return data from between two dates.

Do you want to return all of the data in a range of 7 days in one call, or call the stored procedure 7 times and handle 7 separate result sets?

Show what you have written so far.

Read the link in the sig below and spend a little time creating sample data scripts for people to use to help you.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 8

lobbymuncher (12/31/2009)
Don't do it like this. Write a query which works, then encapsulate it into a stored procedure. A stored procedure is a query (or set of statements) which has been written, debugged, optimised and documented, ready for reuse.
As others have stated, there are many ways to return data from between two dates.
Do you want to return all of the data in a range of 7 days in one call, or call the stored procedure 7 times and handle 7 separate result sets?
Show what you have written so far.
Read the link in the sig below and spend a little time creating sample data scripts for people to use to help you.

I totally agree with your advice, Chris!

But it seems like your signature is missing, so the link cannot be found... 😉

@amitabhssinha

I guess the first link in my signature is the one Chris (aka lobbymuncher) was pointing you at...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 9

lmu92 (12/31/2009)
lobbymuncher (12/31/2009)
Don't do it like this. Write a query which works, then encapsulate it into a stored procedure. A stored procedure is a query (or set of statements) which has been written, debugged, optimised and documented, ready for reuse.
As others have stated, there are many ways to return data from between two dates.
Do you want to return all of the data in a range of 7 days in one call, or call the stored procedure 7 times and handle 7 separate result sets?
Show what you have written so far.
Read the link in the sig below and spend a little time creating sample data scripts for people to use to help you.
I totally agree with your advice, Chris!
But it seems like your signature is missing, so the link cannot be found... 😉
@amitabhssinha
I guess the first link in my signature is the one Chris (aka lobbymuncher) was pointing you at...

Dammit you're absolutely right, Lutz! Will rectify shortly. You're on European time? I'll try not to clash with you this morning. I'm bored and it's too early for beer!

@amitabhssinha Hey I'm sorry, but if you follow the link Lutz provided - and adhere the advice given there, you will help yourself a great deal.

Cheers

ChrisM@home

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 10

lobbymuncher (12/31/2009)
...
You're on European time? I'll try not to clash with you this morning. I'm bored and it's too early for beer!
Cheers
ChrisM@home

Yep, I live in Germany. Right now it's around noon over here. Getting ready for some lunch.

Whatever reason reason you have to be bored: leave it with the old year!!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 11

lmu92 (12/31/2009)
lobbymuncher (12/31/2009)
...
You're on European time? I'll try not to clash with you this morning. I'm bored and it's too early for beer!
Cheers
ChrisM@home
Yep, I live in Germany. Right now it's around noon over here. Getting ready for some lunch.
Whatever reason reason you have to be bored: leave it with the old year!!

Sage advice Lutz! Have a few litres of that delicious German beer for me will ya mate? 😎

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

lobbymuncher (12/31/2009)
...and it's too early for beer!

NEVER! 😀

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 13

Jeff Moden (12/31/2009)
lobbymuncher (12/31/2009)
...and it's too early for beer!
NEVER! 😀

LOL! Happy New Year Jeff!

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]