DECLARE @kHauptKategorie int
DECLARE @kKategorie int
DECLARE @cName varchar(255)
DECLARE @kKategoriefirst int
DECLARE @nAnzahlArtikel int
DECLARE @cPath varchar(max)
DECLARE @cPathReadable varchar(max)
CREATE TABLE #Temp
(
kKategorie int,
cReadable varchar(max),
cPath varchar(max),
nAnzahlArtikel int
)
DECLARE hkat_cursor CURSOR FOR
SELECT tKategorie.kKategorie, kOberkategorie, tKategorie.kKategorie, cName FROM tKategorie INNER JOIN tKategorieSprache ON tKategorie.kKategorie = tKategorieSprache.kKategorie WHERE kShop = 0 AND kPlattform = 1;
OPEN hkat_cursor;
-- Perform the first fetch.
FETCH NEXT FROM hkat_cursor INTO @kHauptKategorie, @kKategorie, @kKategoriefirst, @cName;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Temp (kKategorie) VALUES (@kHauptKategorie);
SET @cPath = CAST(@kHauptKategorie AS varchar(max)) + '/';
SET @cPathReadable = CAST(@cName AS varchar(max)) + '/';
WHILE(@kKategorie != 0)
BEGIN
SELECT @kHauptKategorie = tKategorie.kKategorie, @kKategorie = kOberKategorie, @cName = cName FROM tkategorie INNER JOIN tKategorieSprache ON tKategorie.kKategorie = tKategorieSprache.kKategorie WHERE kShop = 0 AND kPlattform = 1 AND tKategorie.kKategorie = @kKategorie
SET @cPath = CAST(@kHauptKategorie AS varchar(max)) + '/' + @cPath;
SET @cPathReadable = CAST(@cName AS varchar(max)) + '/' + @cPathReadable;
END
UPDATE #Temp SET cPath = @cPath, cReadable = @cPathReadable WHERE kKategorie = @kKategoriefirst;
FETCH NEXT FROM hkat_cursor INTO @kHauptKategorie, @kKategorie, @kKategoriefirst, @cName;
END
CLOSE hkat_cursor;
DEALLOCATE hkat_cursor;
DECLARE artikel_cursor CURSOR FOR
SELECT kKategorie, cPath FROM #Temp;
OPEN artikel_cursor;
FETCH NEXT FROM artikel_cursor INTO @kKategorie, @cPath;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @nAnzahlArtikel = COUNT(kArtikel) FROM tKategorieArtikel INNER JOIN #Temp ON #Temp.kKategorie = tKategorieArtikel.kKategorie WHERE cPath LIKE @cPath + '%';
UPDATE #Temp SET nAnzahlArtikel = @nAnzahlArtikel WHERE kKategorie = @kKategorie;
FETCH NEXT FROM artikel_cursor INTO @kKategorie, @cPath;
END
CLOSE artikel_cursor;
DEALLOCATE artikel_cursor;
SELECT * FROM #Temp;
DROP TABLE #Temp;
