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]

On Thursday, April 11, 2013 6:31:02 PM UTC-4, mcnewsxp wrote:

> but not how to do nested loops.

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

[Erland Sommarskog]

mcnewsxp (mcou...@mindspring.com) writes:
> 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?



--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[mcnewsxp]

On Friday, April 12, 2013 3:42:11 AM UTC-4, Erland Sommarskog wrote:
> mcnewsxp (mcou...@mindspring.com) writes:
>
> > 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?

[mcnewsxp]

that's good. thanks.

[Erland Sommarskog]

mcnewsxp (mcou...@mindspring.com) writes:
> 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]

> > 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]

> > 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?

[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]

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]

mcnewsxp (mcou...@mindspring.com) writes:
> changed to FETCH NEXT FROM OwnerCursor INTO @OwnerID, @AccountID,
@AccountingPeriodID
> print @OwnerID
> print @AccountID
> print @AccountingPeriodID
>
> 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.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Bob Barrows]

mcnewsxp wrote:
> changed to FETCH NEXT FROM OwnerCursor INTO @OwnerID, @AccountID,
> @AccountingPeriodID print @OwnerID
> print @AccountID
> print @AccountingPeriodID
>
> how do you print on same line?

Via concatenation of course.