What's wrong with this loop? 1

[briangriffin]

Trying to populate a variable table with 24 records for each client (for now I've hardcoded the outer loop to execute 4 times). The inner loop executes 24 times as it should, but the outer loop only executes once,and I'm not sure why:

declare 
@v_hourcount int,
@v_loccount int

set @v_hourcount = 1
set @v_loccount = 1

while @v_loccount <= 4 
BEGIN

    while @v_hourcount <= 24
	BEGIN
	insert into @v_results (location, drawnhour)

	select b.location, @v_hourcount
	from @v_locations b
	where b.locid = @v_loccount

	set @v_hourcount = @v_hourcount + 1
	END

set @v_loccount = @v_loccount + 1
END



select * from @v_results

Thanks in advance for your help.

 

[SkipVought]

hi.

while @v_loccount <= 4 
BEGIN

    while @v_hourcount <= 24
    BEGIN
    insert into @v_results (location, drawnhour)

    select b.location, @v_hourcount
    from @v_locations b
    where b.locid = @v_loccount

    set @v_hourcount = @v_hourcount + 1
    END
[b]
set @v_hourcount = 1
[/b]
set @v_loccount = @v_loccount + 1
END

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[briangriffin]

Of course... thanks, Skip.

 

[gmmastros]

All those loops are making my head spin. There is a better way to do this. Let me know if you are interested.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Hmmm...interesting George, are you thinking along the lines of

WITH CTE_Table4
AS
(
  SELECT 1 AS T4Field
  UNION ALL
  SELECT T4Field + 1
  FROM CTE_Table4
  WHERE T4Field + 1 <= 4
),
CTE_Table24
AS
(
  SELECT 1 AS T24Field
  UNION ALL
  SELECT T24Field + 1
  FROM CTE_Table24
  WHERE (T24Field + 1) <= 24
)
SELECT T4Field, T24Field INTO #Temp4By24
FROM CTE_Table4, CTE_Table24 
ORDER BY T4Field

?
Adapting it to Brian' s query gets a little tricky but will try tomorrow when I have more time...

I am interested in your solution.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[markros]

Sure, it's a simple recursive CTE thing. Check a quick sample
here (unrelated, but the same idea)

Populate table with 24 records per loop

PluralSight Learning Library

 

[briangriffin]

Always interested in a better way, and I always learn something from your posts.

 

[TheBugSlayer]

Oh OK...I guess

...
INSERT INTO @v_results (location, drawnhour)
SELECT T24Field, T24Field 
FROM CTE_Table4, CTE_Table24

Should do the trick. Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

TheBugSlayer - This is very close to what I would suggest. My slight modification would be to change the old comma join to a cross join, like this...

INSERT INTO @v_results (location, drawnhour)
SELECT T24Field, T24Field
FROM CTE_Table4 [!]Cross Join[/!] CTE_Table24

Another difference would be how the table that contains 24 rows would be built. In every database I deal with, I always have a numbers table (some people call it a tally table). My Numbers table has just one column (int identity primary key) and has 1,000,000 rows. That may seem like a lot, but it only takes 4 megs to store it, and it comes in handy a lot. If you can't add this table to your database, there is a "poor man's" number table built in for you.

The code would look like this:

insert into @v_results (location, drawnhour)
select b.location, A.Number
from   @v_locations b
       Cross Join (
         Select Number
         From   master..spt_values 
         Where  Type = 'P'
                And Number Between 1 And 24
         ) As A

Of course, you could make the poor man's number table be a cte if you wanted. I'm also not going to claim that a numbers table approach would give better performance than a recursive cte (although I suspect it would). What I will say about performance, is that this method should be many times faster than the nested loop approach.

briangriffin - This approach may not be exactly like yours based on the ordering of data. As such, you may need to play around with order by's to get code that is equivalent to your nested loop approach.

I strongly urge you to consider adding a numbers table to your database. It will help with this query and you're likely to find many other uses for it too.

If you don't have a numbers table and would like to add one:

/* ****** Creation of a Numbers tables ****** */
CREATE TABLE Numbers(Num INT IDENTITY(1,1) PRIMARY KEY)
go
 
SET NOCOUNT ON
BEGIN TRAN
    INSERT INTO Numbers DEFAULT VALUES
    INSERT INTO Numbers DEFAULT VALUES
    INSERT INTO Numbers DEFAULT VALUES
    INSERT INTO Numbers DEFAULT VALUES
    INSERT INTO Numbers DEFAULT VALUES
    INSERT INTO Numbers DEFAULT VALUES
    INSERT INTO Numbers DEFAULT VALUES
    INSERT INTO Numbers DEFAULT VALUES
    INSERT INTO Numbers DEFAULT VALUES
    INSERT INTO Numbers DEFAULT VALUES
COMMIT TRAN
go 100000

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom