[SQL srv 2000] SP met cursors en while

[Y3PP3R]

Ik heb een table met verschillende magazijns en modellen. Telkens als er wat in of uit een magazijn gaat wordt dat in de tabel als een record toegevoegd. als er iets binnenkomt, dan is binnen een datum en is uit NULL en als er iets uit gaat andersom. Alle modellen hebben ook nog een kleurnummer en een combinatienummer.

Nu is het mogelijk dat het zelfde model verschillende keren is binnen gekomen op 1 plaats en ook wel er uit is gegaan.

Het is de bedoeling dat ik weergeef hoeveel van de verschillende modellen op dit moment aanwezig zijn in welk magazijn.

Daarvoor heb ik een SP gemaakt, die een tablevariable maakt met van alle verschillende modellen per magazijn het totaal. Hij werkt met verschillende cursors die ik genest heb (misschien dat hier t probleem zit?).

Het probleem is namelijk dat mn tablevariable leeg blijft. In t executionplan heb ik gezien, dat alleen de eerste cursor rows returned, dus, alle andere cursors worden doordat ik WHILE @@FETCH_STATUS = 0 gebruik, niet geopend en mn INSERT statement wordt niet bereikt.

Ik heb er eens over nagedacht, alleen ik zou geen alternatief SET-Based solution weten.

Kan iemand mij vertellen waar de fout in mijn code zit? Ik heb hem nagelopen, uitgevoerd en dingen nagezocht in de SQLsvr 2k bible en gezocht in t forum.

code:

DECLARE @magazijn NVARCHAR(50), @inkoopnr INT, @combi NCHAR, @kleurnr INT, @bekledingnr INT, @totaal INT

DECLARE cMagazijn CURSOR FOR SELECT DISTINCT magazijn FROM voorraad
DECLARE cModel CURSOR FOR SELECT DISTINCT inkoopnr FROM voorraad WHERE magazijn='@magazijn'
DECLARE cCombi CURSOR FOR SELECT DISTINCT modelnr FROM voorraad WHERE magazijn='@magazijn' AND inkoopnr=@inkoopnr
DECLARE cKleur CURSOR FOR SELECT DISTINCT kleurnr FROM voorraad WHERE magazijn='@magazijn' AND inkoopnr=@inkoopnr AND modelnr='@combi'
DECLARE cBekleding CURSOR FOR SELECT DISTINCT bekledingnr FROM voorraad WHERE magazijn='@magazijn' AND inkoopnr=@inkoopnr AND modelnr='@combi' AND kleurnr=@kleurnr

DECLARE @voorraad TABLE (
[ID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),  
Magazijn NVARCHAR(50),
Inkoopnr INT,
Model NCHAR,
Bekledingnr INT,
Kleurnr INT,
Aantal INT)

OPEN cMagazijn

FETCH cMagazijn INTO @magazijn
WHILE @@Fetch_Status = 0
    BEGIN
        OPEN cModel
        FETCH cModel INTO @inkoopnr
        WHILE @@Fetch_Status = 0
        BEGIN
            OPEN cCombi
            FETCH cCombi INTO @combi
            WHILE @@Fetch_Status = 0
            BEGIN
                OPEN cKleur
                FETCH cKleur INTO @kleurnr
                WHILE @@Fetch_Status = 0
                BEGIN
                    OPEN cBekleding
                    FETCH cBekleding INTO @bekledingnr
                    WHILE @@Fetch_Status = 0
                    BEGIN
                        SELECT @totaal = SUM(aantal) FROM voorraad WHERE magazijn=@magazijn AND inkoopnr=@inkoopnr AND modelnr=@combi AND kleurnr=@kleurnr AND bekledingnr=@bekledingnr AND binnen IS NOT NULL
                        SELECT @totaal = @totaal - SUM(aantal) FROM voorraad WHERE magazijn=@magazijn AND inkoopnr=@inkoopnr AND modelnr=@combi AND kleurnr=@kleurnr AND bekledingnr=@bekledingnr AND uit IS NOT NULL
                        INSERT INTO @voorraad(magazijn, inkoopnr, Model, bekledingnr, kleurnr, aantal) VALUES(@magazijn, @inkoopnr, @combi, @bekledingnr, @kleurnr, @totaal)
                        FETCH cBekleding INTO @bekledingnr
                    END
                    CLOSE cBekleding
                    FETCH cKleur INTO @kleurnr
                END
                CLOSE cKleur
                FETCH cCombi INTO @combi
            END
            CLOSE cCombi
            FETCH cModel INTO @inkoopnr
        END
        CLOSE cModel
        FETCH cMagazijn INTO @magazijn
    END
CLOSE cMagazijn

DEALLOCATE cMagazijn
DEALLOCATE cKleur
DEALLOCATE cCombi
DEALLOCATE cBekleding
DEALLOCATE cModel

SELECT * FROM @voorraad

[Annie]

Bij het doorgeven van een string variabele, "...magazijn='@magazijn'... ", in de query is het niet de bedoeling dat je deze omsluit met enkele quotes. Maar daar zit het probleem niet, het eerst declareren van de cursors zal afaik sowieso niet werken. Deze zal je telkens weer opnieuw moeten opbouwen binnen je lus(sen).

Maar ik raad je aan om nog eens goed te kijken naar je gekozen oplossingsroute. Het gebruik van 1 cursor is voor de meeste database professionals al een reden om zich even achter de oren te krabben en te kijken of dit wel de enige en juiste methode is, laat staan wanneer je er 5 hebt.

[Y3PP3R]

Dank je die enkele quotes waren een probeersel, ik had m oorspronkelijk ook zonder.
Ik zal eerst proberen of jouw oplossing verder werkt, moet ik dan ook steeds deallocaten?

Ik heb ook eens nagedacht over een While lus met @@rowcount, alleen dan zou ik niet weten hoe ik ditzelfde gedaan moet krijgen. Ik wil niet makkelijk zijn, ik modder hier al de hele dag mee namelijk, maar kan iemand mij even op t juiste pad helpen zonder cursors?

[EfBe]

IF @bNestedCursors THEN
BEGIN
USE GroupBy
END

m.a.w.: je moet naar een groupby query kijken, groeperen op de fields in je @voorraad table. Ik ga niet je gehele query langs maar nested cursors zijn altijd middels group by op te lossen.

[Y3PP3R]

*voelt zich stom*
O o o wat is dat makkelijk met group by ...

Dank dank!!

[jvdmeer]

Heb je 't nu opgelost met of zonder cursors?
Ik heb nog niet helemaal door wat je wilt (lukt me niet om die 5 lussen in m'n hoofd te leggen) , maar volgens mij kan het zelfs zonder cursors.

Dus als je nog cursors gebruikt, kan je dan wat specifieker aangeven wat je wilt bereiken. Evt. met voorbeelddata.

[EfBe]

Ik heb nog niet helemaal door wat je wilt (lukt me niet om die 5 lussen in m'n hoofd te leggen) , maar volgens mij kan het zelfs zonder cursors.

Hij heeft geprobeerd met cursors uit te leggen hoe GROUP BY werkt.

[ Voor 54% gewijzigd door EfBe op 28-12-2003 13:36 ]