Post reply

Want to use WHILE loop in Stored procedure

  • 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 29, 2009 at 10:41 pm

    #1097316

    Is the both the inputs are standard or varies?

  • December 29, 2009 at 11:25 pm

    #1097322

    Please give more details.

    "Keep Trying"

  • December 29, 2009 at 11:40 pm

    #1097325

    Date period is variable, it depends on user

  • December 30, 2009 at 12:38 am

    This was removed by the editor as SPAM

  • December 30, 2009 at 12:49 am

    #1097340

    Thanks Stewart for your valuable suggestion.

    I had thought the same go earlier.

  • December 31, 2009 at 2:12 am

    #1097781

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

  • December 31, 2009 at 2:42 am

    #1097791

    DECLARE @Date datetime

    SELECT @Date = @StartDate

    WHILE (@Date <= @EndDate)

    BEGIN

    -- Process for @Date value

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

    END

  • December 31, 2009 at 3:50 am

    #1097811

    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]

  • December 31, 2009 at 4:00 am

    #1097815

    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]

  • December 31, 2009 at 4:07 am

    #1097816

    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]

  • December 31, 2009 at 4:33 am

    #1097828

    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]

  • December 31, 2009 at 4:40 am

    #1097832

    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]

  • December 31, 2009 at 9:44 pm

    #1098054

    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)
    Intro to Tally Tables and Functions

  • January 1, 2010 at 5:52 am

    #1098079

    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]

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

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