How to loop through records

On 16 Feb, 21:13, "Plamen Ratchev" wrote:
> If your start and end weeks are in the same year, then the posted solutions
> with utility table with numbers will help. But if the weeks cross years
> (that is start week is in one year, and end week is next year) then you can
> have the case where start week = 40 and end week = 3. In that case it will
> be better to use a calendar table with pre-calculated weeks:http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using...
>
> Then you have to add filters for start and end date to restrict the calendar
> table.
>
> Of course, all those notes are valid only if the meaning of week in your
> case relates to calendar years/dates.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Thank you all very much for the help!

I already have tblWeek which will work as the numbers table. The
problem is solved!

// S
 >> Stay informed about: How to loop through records 

Create a numbers table - see
http://www.aspfaq.com/show.asp?id=2516
for how to do this and why you want one.

Then you can insert each value without a loop, just one insert statement
which will be much faster, e.g.,

INSERT INTO tblSchedule (WeekID)
SELECT Number
FROM Numbers
WHERE Number BETWEEN @WeekIDFrom AND @WeekIDTo.

Tom

wrote in message

>I have @WeekIDFrom and @WeekIDTo as input parameters in a stored
> procedure.
>
> I need to loop through each value between @WeekIDFrom and @WeekIDTo
> and insert the value into a table, like this:
>
> INSERT INTO tblSchedule (WeekID)
> VALUES (@WeekID)
>
> Can someone please help me with the loop?
>
> // S
 >> Stay informed about: How to loop through records 

If your start and end weeks are in the same year, then the posted solutions
with utility table with numbers will help. But if the weeks cross years
(that is start week is in one year, and end week is next year) then you can
have the case where start week = 40 and end week = 3. In that case it will
be better to use a calendar table with pre-calculated weeks:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-...auxilia

Then you have to add filters for start and end date to restrict the calendar
table.

Of course, all those notes are valid only if the meaning of week in your
case relates to calendar years/dates.

HTH,

Plamen Ratchev
http://www.SQLStudio.com
 >> Stay informed about: How to loop through records 

Plamen Ratchev,

Good catch!!!

Then I would suggest to use startdate and enddate instead, using the date of
the first date in the week.

DECLARE @WeekIdFrom datetime
DECLARE @WeekIdTo datetime
DECLARE @t TABLE (WeekId INT NOT NULL PRIMARY KEY)

SET @WeekIdFrom = '20080217'
SET @WeekIdTo = '20080323'

;with
L0 as (select 1 as n union all select 1),
L1 as (select 1 as n from L0 as a, L0 as b),
L2 as (select 1 as n from L1 as a, L1 as b),
L3 as (select 1 as n from L2 as a, L2 as b),
L4 as (select 1 as n from L3 as a, L3 as b),
Numbers as (select row_number() over(order by (select 1)) as n FROM L4)
INSERT INTO @t(WeekId)
SELECT
datepart(week, dateadd(week, n - 1, @WeekIdFrom))
FROM
Numbers
WHERE
n <= datediff(week, @WeekIdFrom, @WeekIdTo) + 1

SELECT * FROM @t ORDER BY WeekId
GO


AMB

"Plamen Ratchev" wrote:

> If your start and end weeks are in the same year, then the posted solutions
> with utility table with numbers will help. But if the weeks cross years
> (that is start week is in one year, and end week is next year) then you can
> have the case where start week = 40 and end week = 3. In that case it will
> be better to use a calendar table with pre-calculated weeks:
> http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-...auxilia
>
> Then you have to add filters for start and end date to restrict the calendar
> table.
>
> Of course, all those notes are valid only if the meaning of week in your
> case relates to calendar years/dates.
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com
>
 >> Stay informed about: How to loop through records