Cursors and While Loops - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursors and While Loops

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-11 : 08:17:40

Ink writes "I want my query to pull data like this:

1 123A 1
1 123A 2
1 123A 3
1 123A 4
1 123A 5
1 123A 6
1 123A 7
1 123A 8
1 123A 9
1 123A 10
1 123A 11
1 123A 12
2 123A 1
2 123A 2
2 123A 3
2 123A 4
2 123A 5
2 123A 6
2 123A 7
2 123A 8
2 123A 9
2 123A 10
2 123A 11
2 123A 12

...and so forth...but it's not doing it. It's only pulling the first record like below.

1 123A 1
1 123A 2
1 123A 3
1 123A 4
1 123A 5
1 123A 6
1 123A 7
1 123A 8
1 123A 9
1 123A 10
1 123A 11
1 123A 12

What must I add to pull data like my example above? Here's my t-sql.

create table #tmpTbl (AcctID int, AcctNum varchar(20))

insert into #tmpTbl values (1,'123A');
insert into #tmpTbl values (2,'123B');
insert into #tmpTbl values (3,'123C');
insert into #tmpTbl values (4,'123D');
insert into #tmpTbl values (5,'123E');

DECLARE @acctid int
DECLARE @acctnum varchar(10)
DECLARE @counter int

SET @counter=0

DECLARE abc CURSOR FOR
SELECT * FROM #tmpTbl

OPEN abc

FETCH NEXT FROM abc into @acctid, @acctnum
WHILE (@@FETCH_STATUS = 0)
BEGIN
WHILE @counter < 12
BEGIN
SET @Counter=@Counter+1
print cast(@acctid as varchar(2))+ ' ' + @acctnum + ' ' + cast(@counter as varchar(2))
END
FETCH NEXT FROM abc INTO @acctid, @acctnum

END

CLOSE abc
DEALLOCATE abc
GO

drop table #tmpTbl

Thanks,"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-11 : 08:39:39

place SET @counter = 0 before the WHILE @counter < 12.

Why are you using cursor ? This can be achieve without using cursor.

-----------------
'KH'
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-01-11 : 09:02:04

You've shown us your expected output and your actual output...BUT NOT your input data!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-11 : 20:18:41

Non cursor and while loop solution

create table #tmpTbl (AcctID int, AcctNum varchar(20))

insert into #tmpTbl values (1,'123A');
insert into #tmpTbl values (2,'123B');
insert into #tmpTbl values (3,'123C');
insert into #tmpTbl values (4,'123D');
insert into #tmpTbl values (5,'123E');

select 	* 
from	#tmpTbl
cross join
(
	select 1 as Num union all select 2 union all select 3 union all
	select 4 union all select 5 union all select 6 union all
	select 7 union all select 8 union all select 9 union all
	select 10 union all select 11 union all select 12
) as n
order by AcctID, AcctNum, Num

-----------------
'KH'
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources