Need help using While

Question

Post reply

Need help using While

Sharul Nizam

Ten Centuries

Points: 1302
More actions
January 21, 2010 at 8:04 am

#140725

My table and row as follow,
declare @tBusType TABLE
(
TrnxID int identity(1,1),
[BusTypeCode] [nvarchar](50) NOT NULL,
[BusTypeDesc] [nvarchar](50) NULL,
[nColLeft] [smallint] NULL,
[nColRight] [smallint] NULL,
[nRow] [smallint] NULL
)
insert into @tBusType(BusTypeCode,BusTypeDesc,nColLeft,nColRight,nRow)
values('STD26','STD26',1,2,9);
insert into @tBusType(BusTypeCode,BusTypeDesc,nColLeft,nColRight,nRow)
values('STD48','STD48',2,2,12);
insert into @tBusType(BusTypeCode,BusTypeDesc,nColLeft,nColRight,nRow)
values('STD30','STD30',1,2,11);
/*BusTypeCode is a unique*/
declare @tBusSeatInXY table
(
TrnxID int identity(1,1),
BusCd char(10) not null,
col_X tinyint null,
row_Y tinyint null,
seatDesc char(4) null
)
declare @colLeft as tinyint
declare @colRight as tinyint
declare @col_X as tinyint
declare @row_Y as tinyint
declare @gBusTypCd as varchar(50)
set @gBusTypCd='STD26'
select @col_X=nColLeft+nColRight,@row_Y=nRow from @tBusType where BusTypeCode=@gBusTypCd
declare @i tinyint;
declare @j-2 tinyint;
set @i = 1;
while @i <= @row_Y
begin
set @j-2 = 1;
while @j-2 <=@col_X
begin
insert into @tBusSeatInXY(BusCd,col_X,row_Y) values(@gBusTypCd,@j,@i)
set @j-2 = @j-2 + 1;
end
set @i = @i + 1;
end
My SQL result as follow,
select * from @tBusType
1STD26STD26129
2STD48STD482212
3STD30STD301211
select * from @tBusSeatInXY
1STD26 11NULL
2STD26 21NULL
3STD26 31NULL
4STD26 12NULL
5STD26 22NULL
6STD26 32NULL
7STD26 13NULL
8STD26 23NULL
9STD26 33NULL
10STD26 14NULL
11STD26 24NULL
12STD26 34NULL
13STD26 15NULL
14STD26 25NULL
15STD26 35NULL
16STD26 16NULL
17STD26 26NULL
18STD26 36NULL
19STD26 17NULL
20STD26 27NULL
21STD26 37NULL
22STD26 18NULL
23STD26 28NULL
24STD26 38NULL
25STD26 19NULL
26STD26 29NULL
27STD26 39NULL
How to make it my below SQL dynamic
declare @gBusTypCd as varchar(50)
set @gBusTypCd='STD26'
select @col_X=nColLeft+nColRight,@row_Y=nRow from @tBusType where BusTypeCode=@gBusTypCd
declare @i tinyint;
declare @j-2 tinyint;
set @i = 1;
while @i <= @row_Y
begin
set @j-2 = 1;
while @j-2 <=@col_X
begin
insert into @tBusSeatInXY(BusCd,col_X,row_Y) values(@gBusTypCd,@j,@i)
set @j-2 = @j-2 + 1;
end
set @i = @i + 1;
end
So, my While Loop can write the rest row of STD48 and STD30?
I'm stuck

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Why not using a solution that doesn't use any while loops at all and do it all in one path?

;with cte

AS

(

SELECT

BusTypeCode,

n1 % (ncolLeft+nColRight) +1 AS Col_X,

row_number() over(partition BY BusTypeCode,n1 % (ncolLeft+nColRight) ORDER BY n1 % nRow ) AS row_Y

FROM @tBusType

CROSS apply

( SELECT n n1

FROM tally

WHERE n<=(nRow * (ncolLeft+nColRight))

) row

)

SELECT

row_Number() over(partition BY BusTypeCode ORDER BY row_Y,Col_X ) AS TrnxID,

* FROM cte

ORDER BY BusTypeCode, row_Y,Col_X

If you don't have a Tally table yet you should get it by following the related link in my signature. It's on sale this week!!!! (Usually you can purchase it for 0.00USD but this week it's FOR FREE!! :w00t:

Note: have a look at the article and you'll see that I'm just kidding regarding the price... 😉

Edit: code replaced with a better performing solution... (ashamed of keeping the old version available...) :blush:

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Sharul Nizam Ten Centuries Points: 1302 More actions · Answer 2

Sharul Nizam

Ten Centuries

Points: 1302

January 21, 2010 at 4:17 pm

#1107004

Tally table really great ...

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

lmu92 (1/21/2010)
Why not using a solution that doesn't use any while loops at all and do it all in one path?

Well done, Lutz!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

Sharul Nizam (1/21/2010)
My table and row as follow,

My hat's off to you, Sharul... you really made it easy for folks to help you with the way you posted. Well done!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)