Post reply

SQL LOOP - with variables for date inserts to a table

  • February 22, 2010 at 9:35 pm

    #137315

    I am trying to insert multiple rows of dynamic data into an SQL table for future invoicing but i'm not sure how to write an SQL LOOP statement to do this?

    For example i have a Booking table inserted with a single row of data that contains the following items:

    - UserID

    - Start Date

    - End Date

    - Payment Frequency (weekly or monthly)

    - Amount (always the same amount)

    I want to write a stored procedure (or maybe a trigger) to INSERT the future payments into my Invoices table for multiple rows of future dates based on the period and frequency from the first table.

    For example the multiple rows of data might look like these simple columns:

    UserID, PaymentDate, PaymentAmount

    1234,1/1/2010,10

    1234,8/1/2010,10

    1234,15/1/2010,10

    1234,221/2010,10

    The above would represent a weekly payment of $10 starting on the 1/1/2010.

    I will also be using a TRY/CATCH block and a BEGIN/COMMIT TRANSACTION block where the SQL loop will reside.

    I will also need to use some variables in the loop so i can set the date forward on each iteration based on the payment frequency.

    I just need a little guidance on LOOPING in SQL.

  • February 23, 2010 at 2:03 am

    #1123476

    Loops, by their very nature, process things Row-By-Agonizing-Row. SQL is a set-based language, and thus performs best with set-based code.

    If you provide the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

    If you provide this information, I think you will find many people here willing to help you learn the proper way to do this.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 2 posts - 1 through 1 (of 1 total)

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