sql stored procedure loop

I need help building a loop in my stored procedure, basically i want it too loop from 2005 till the current year.

 ALTER PROCEDURE [dbo].[testt1]
 as
DECLARE @YearToGet int;
SET @YearToGet = 2005;

WITH Years AS (
    SELECT DATEP开发者_开发知识库ART(year, GETDATE()) [Year]
    UNION ALL
    SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet
)

SELECT     TOP (100) PERCENT DIVISION, DYYYY, SUM(APRICE) AS Sales, SUM(PARTY) AS PAX, SUM(NetAmount) AS NetSales, SUM(InsAmount) 
                      AS InsSales, SUM(CancelRevenue) AS CXSales, SUM(OtherAmount) AS OtherSales, SUM(CXVALUE) AS CXValue
FROM         dbo.B101BookingsDetails AS B101BookingsDetails 


WHERE    Booked <= CONVERT(int,DateAdd(year, (SELECT * FROM Years) - Year(getdate()), DateAdd(day, DateDiff(day, 2, getdate()), 0)))
  and DYYYY = @YearToGet
GROUP BY  DYYYY, DIVISION

ORDER BY DIVISION,  DYYYY




OPTION (MAXRECURSION 0) -- this avoids hitting the recursion limit in the CTE

and i could really used a stored procedure website that goes into more detail then just the simple select statements

This is not directly an answer, but the code cannot be posted in a readible fashion in a comment, so I think this should be okay here:

Don't loop in SPs, rather use a CTE to generate the numbers you need.

DECLARE @YearToGet int;
SET @YearToGet = 2005;

WITH Years AS (
    SELECT DATEPART(year, GETDATE()) [Year]
    UNION ALL
    SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet
)
SELECT * FROM Years -- join here with your query
OPTION (MAXRECURSION 0) -- this avoids hitting the recursion limit in the CTE

Edit: Try this

WITH  Years
          AS (
              SELECT DATEPART(year, GETDATE()) [Year]
              UNION ALL
              SELECT [Year]-1
                FROM Years
                WHERE [Year] > @YearToGet
             )
    SELECT DIVISION, DYYYY, SUM(APRICE) AS Sales, SUM(PARTY) AS PAX, SUM(NetAmount) AS NetSales, SUM(InsAmount) AS InsSales, SUM(CancelRevenue) AS CXSales, SUM(OtherAmount) AS OtherSales, SUM(CXVALUE) AS CXValue
      FROM dbo.B101BookingsDetails 
      JOIN Years yr ON DYYYY = yr.[Year]
      WHERE Booked <= CONVERT(int, DATEADD(year, DYYYY-YEAR(GETDATE()), DATEADD(day, DATEDIFF(day, 2, GETDATE()), 0)))
      GROUP BY DYYYY, DIVISION
      ORDER BY DIVISION, DYYYY
    OPTION (MAXRECURSION 0);

Ok written to not use recursive CTE :) Since you are already storing the year in the B101BookingsDetails table, just get the list of years that is greater than the year you are looking for from there.

CREATE PROCEDURE dbo.testt1
(
    @YearToGet int = 2005
)
AS

    WITH Years AS (
        SELECT DISTINCT DYYYY as year
        FROM    dbo.B101BookingsDetails
        WHERE DYYYY >= @YearToGet
    )   
    SELECT  TOP (100) PERCENT DIVISION, DYYYY, SUM(APRICE) AS Sales, SUM(PARTY) AS PAX, SUM(NetAmount) AS NetSales, 
                SUM(InsAmount) AS InsSales, SUM(CancelRevenue) AS CXSales, SUM(OtherAmount) AS OtherSales, SUM(CXVALUE) AS CXValue
        FROM    dbo.B101BookingsDetails 
                INNER JOIN Years
                    ON B101BookingsDetails.DYYYY = Years.Year
        WHERE   Booked  <= CONVERT(int,DateAdd(year, Years.Year - Year(getdate()), DateAdd(day, DateDiff(day, 2, getdate()), 0)))
        GROUP BY DYYYY, DIVISION

You need to increment @YearToGet at the end of the loop.