while loop query

[mcnewsxp]

i have an AccountEntry table that contains OwnerID, AccountID, AccountPeriodID.

i need to get the distinct OwnerID, distinct AccountID and distinct AccountPeriodID and then execute 3 while loops to call a stored proc with the values ala

while ownerid loop has values
while accountid loop has values
while ownerid loop has values
exec storedproc ownerid, accountid, accountperiodid
read new accountperiodid value
end loop
read new accountid
end loop
read ownerid
end loop

i know how to use
BEGIN
EXEC myproc
FETCH NEXT FROM OwnerCursor INTO @OwnerID
ENDWHILE @@FETCH_STATUS = 0

but not how to do nested loops.

thanks in advance.

CREATE TABLE [dbo].[AccountEntry](
[AccountingEntryID] [int] IDENTITY(1,1) NOT NULL,
[AccountingPeriodID] [int] NULL,
[AccountID] [int] NOT NULL,
[OwnerID] [int] NULL,
CONSTRAINT [PK_AccountingEntry] PRIMARY KEY CLUSTERED


1, 1, 1
1, 2, 1
2, 23, 3
3, 4, 5

[rpresser]

Post by mcnewsxp
but not how to do nested loops.

read this page:
http://sqlserverpedia.com/wiki/WHILE_Loops

[mcnewsxp]

Post by rpresser

Post by mcnewsxp
but not how to do nested loops.

http://sqlserverpedia.com/wiki/WHILE_Loops

that's good. thanks.

[Erland Sommarskog]

Post by mcnewsxp
i have an AccountEntry table that contains OwnerID, AccountID,
AccountPeriodID.
i need to get the distinct OwnerID, distinct AccountID and distinct
AccountPeriodID and then execute 3 while loops to call a stored proc
with the values ala

Why not just have

SELECT DISTINCT ownerid, accountid, accountperiodid
FROM ...

in the cursor declaration?

[mcnewsxp]

Post by Erland Sommarskog

Post by mcnewsxp
i have an AccountEntry table that contains OwnerID, AccountID,
AccountPeriodID.
i need to get the distinct OwnerID, distinct AccountID and distinct
AccountPeriodID and then execute 3 while loops to call a stored proc
with the values ala

Why not just have
SELECT DISTINCT ownerid, accountid, accountperiodid
FROM ...
in the cursor declaration?

because I have to execute the proc for every possible combination.
i.e. owner ID every account ID and account period ID combo. so you distinct would get them all?

[Erland Sommarskog]

because I have to execute the proc for every possible combination. i.e.
owner ID every account ID and account period ID combo. so you distinct
would get them all?

And? There is no problem in writing a query that produce every combination.
Your mistake is that you are thinking in loops when you should think in
sets.

[Gert-Jan Strik]

Post by mcnewsxp

Post by Erland Sommarskog
Why not just have
SELECT DISTINCT ownerid, accountid, accountperiodid
FROM ...
in the cursor declaration?

because I have to execute the proc for every possible combination.
i.e. owner ID every account ID and account period ID combo. so you distinct would get them all?

The DISTINCT keyword works on the combination of all columns, so the statement above will give you
all unique combinations of ownerid, accountid and accountperiodid of your selection.
--
Gert-Jan

[Gert-Jan Strik]

Post by mcnewsxp

Post by Erland Sommarskog
Why not just have
SELECT DISTINCT ownerid, accountid, accountperiodid
FROM ...
in the cursor declaration?

because I have to execute the proc for every possible combination.
i.e. owner ID every account ID and account period ID combo. so you distinct would get them all?

The DISTINCT keyword works on the combination of all columns, so the statement above will give you
all unique combinations of ownerid, accountid and accountperiodid of your selection.
--
Gert-Jan

[mcnewsxp]

where am i going wrong?

DECLARE @ownerID int
DECLARE @AccountID int
DECLARE @AccountingPeriodID int

DECLARE OwnerCursor CURSOR FOR
select distinct ownerid, accountid, accountingperiodid FROM [123Landlord].[dbo].[AccountingEntry] order by ownerid
OPEN OwnerCursor
FETCH NEXT FROM OwnerCursor INTO @OwnerID, @AccountID, @AccountingPeriodID

WHILE @@FETCH_STATUS = 0
BEGIN
--UPDATE MyData SET dat_value = @Value where dat_id = @ID
print @OwnerID + @AccountID + @AccountingPeriodID
FETCH NEXT FROM OwnerCursor INTO @OwnerID, @AccountID, @AccountingPeriodID
END

CLOSE OwnerCursor
DEALLOCATE OwnerCursor
GO

[mcnewsxp]

where am i going wrong?

DECLARE @ownerID int
DECLARE @AccountID int
DECLARE @AccountingPeriodID int

DECLARE OwnerCursor CURSOR FOR
select distinct ownerid, accountid, accountingperiodid FROM [mydb].[dbo].[AccountingEntry] order by ownerid
OPEN OwnerCursor
FETCH NEXT FROM OwnerCursor INTO @OwnerID, @AccountID, @AccountingPeriodID

WHILE @@FETCH_STATUS = 0
BEGIN
--UPDATE MyData SET dat_value = @Value where dat_id = @ID
print @OwnerID + @AccountID + @AccountingPeriodID
FETCH NEXT FROM OwnerCursor INTO @OwnerID, @AccountID, @AccountingPeriodID
END

CLOSE OwnerCursor
DEALLOCATE OwnerCursor
GO

[mcnewsxp]

Post by mcnewsxp
where am i going wrong?
DECLARE OwnerCursor CURSOR FOR
select distinct ownerid, accountid, accountingperiodid FROM [mydb].[dbo].[AccountingEntry] order by ownerid
OPEN OwnerCursor
BEGIN
END
CLOSE OwnerCursor
DEALLOCATE OwnerCursor
GO

changed to FETCH NEXT FROM OwnerCursor INTO @OwnerID, @AccountID, @AccountingPeriodID
print @OwnerID
print @AccountID
print @AccountingPeriodID

how do you print on same line?

[Erland Sommarskog]

Post by mcnewsxp
how do you print on same line?

With a single PRINT statement.

It was not clear from your post what was going wrong for you, but I
leave as an exercise to the reader to figure out why:

DECLARE @ownerID int
DECLARE @AccountID int
DECLARE @AccountingPeriodID int
print @OwnerID + @AccountID + @AccountingPeriodID

will either print a single line or a single integer value.

[Bob Barrows]

@AccountingPeriodID print @OwnerID
how do you print on same line?

Via concatenation of course.