sql server new users:
From a cursory look, it may be as easy as writing a single query and NOT =
any using nested loops (CURSOR or WHILE).
This is a WAG for directing your thinking only -since I don't have =
access to the table DDL, or data, it most likely won't work in its =
present form.
SELECT=20
t1.p4,
Total =3D sum ( t2.n1 )
FROM tbl_MyTable t1
JOIN ntbl_MyTable t2
ON t1.p4 =3D t2.n4
WHERE t1.p4 =3D 123
GROUP BY t1.p4
It would help us better assist you if you could include table DDL, query =
strategy used so far, sample data in the form of INSERT statements, and =
an illustration of the desired results. (For help with that refer to: =
http://www.aspfaq.com/5006 and to =
http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-g= ets-answered.html )
=20
The less 'set up' work we have to do, the more likely you are going to =
have folks tackle your problem and help you. Without this effort from =
you, we are just playing guessing games.=20
As an aside -the table and columns names you 'inherited' are really, =
really, bad and should be changed to reflect commonly accepted standards =
and practices as soon as is practical.
--=20
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.=20
Most experience comes from bad judgment.=20
- Anonymous
[quoted text, click to view] "venkat" <venk,kol@yahoo.com> wrote in message =
news:OPGoCFn4GHA.3556@TK2MSFTNGP02.phx.gbl...
> Hi,
>=20
> I am trying to write a t-sql with nested while loop and table variable =
> instead of cursors.
> Can some one pls help me if am doing correct with the below code.
>=20
> eg
> declare @myTable table
> (rowId int identity(1,1),
> p1 int,
> p2 int,
> p3 int)
>=20
> declare @nmyTable table
> (rowId int identity(1,1), p1 int)
>=20
> declare @rowId int, @maxRowId int, @p1 int
> declare @nrowId int, @nmaxRowId int, @n1 int
> declare @total int
>=20
>=20
> insert into @myTable
> select
> p1 from
> tbl_myTable
> where
> p4 =3D 123
>=20
> select
> @rowId =3D min(RowId),
> @MaxRowId =3D max(RowId)
> from @myTable
>=20
> -- here is the loop
> while @RowId <=3D @MaxRowId
> begin
>=20
> select @p1 =3D p1 from @myTable where RowId =3D @RowId
>=20
>=20
>=20
> insert into @nmyTable select n1 from ntbl_myTable where n4 =3D 123
> select @nrowId =3D min(RowId),@nMaxRowId =3D max(RowId) from =
@nmyTable
>=20
> while @nRowId <=3D @nMaxRowId
> begin
> select @n1 =3D n1 from @nmyTable where RowId =3D @nRowId
> set @total =3D @total + @n1
> set @nRowId =3D @nRowId+1
> end
>=20
>=20
> -- increment the row counter
> set @RowId =3D @RowId + 1
> end
>=20
> print @total
>=20
Hi,
I am trying to write a t-sql with nested while loop and table variable
instead of cursors.
Can some one pls help me if am doing correct with the below code.
eg
declare @myTable table
(rowId int identity(1,1),
p1 int,
p2 int,
p3 int)
declare @nmyTable table
(rowId int identity(1,1), p1 int)
declare @rowId int, @maxRowId int, @p1 int
declare @nrowId int, @nmaxRowId int, @n1 int
declare @total int
insert into @myTable
select
p1 from
tbl_myTable
where
p4 = 123
select
@rowId = min(RowId),
@MaxRowId = max(RowId)
from @myTable
-- here is the loop
while @RowId <= @MaxRowId
begin
select @p1 = p1 from @myTable where RowId = @RowId
insert into @nmyTable select n1 from ntbl_myTable where n4 = 123
select @nrowId = min(RowId),@nMaxRowId = max(RowId) from @nmyTable
while @nRowId <= @nMaxRowId
begin
select @n1 = n1 from @nmyTable where RowId = @nRowId
set @total = @total + @n1
set @nRowId = @nRowId+1
end
-- increment the row counter
set @RowId = @RowId + 1
end
print @total
