Welcome to Sign in | Help
in Search

CTE cu variabile

Last post 06-20-2007, 12:28 PM by ssandu. 5 replies.
Sort Posts: Previous Next
  •  06-20-2007, 10:28 AM 2122

    CTE cu variabile

    Poate CTE lucra cu variabile ?

    Am o procedura care contine cateva CTE

    Procedura primeste 2 parametri pe care vreau sa-i folosesc in CTE

    Primesc eroarea in CTE "Must declare the scalar variable @data1"

    multumesc 


    Sorin Sandu
  •  06-20-2007, 10:52 AM 2123 in reply to 2122

    Re: CTE cu variabile

    Da, se poate. Uite un exemplu cât de cât interesant:

    USE AdventureWorks
    GO
    CREATE FUNCTION MonthlySalesForAProduct(@ProductID int)
    RETURNS TABLE AS RETURN
    WITH TotalSalesForAProduct AS (
        SELECT YEAR(OrderDate) as Y, MONTH(OrderDate) as M, SUM(OrderQty) as Qty
        FROM Sales.SalesOrderHeader h
        INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID=d.SalesOrderID
        WHERE d.ProductID=@ProductID
        GROUP BY YEAR(OrderDate), MONTH(OrderDate)
    )
    SELECT a.Y, a.M, a.Qty, b.Qty as PrevQty,
        CONVERT(numeric(10,2),100.*(a.Qty-b.Qty)/b.Qty) as PercentChange
    FROM TotalSalesForAProduct a LEFT JOIN TotalSalesForAProduct b
    ON a.Y*12+a.M=b.Y*12+b.M+1
    --ORDER BY Y, M

    GO
    SELECT * FROM MonthlySalesForAProduct(761) ORDER BY Y, M

    Am preferat să fac o funcţie, ca să pot să refolosesc rezultatul în alte query-uri, dar se putea utiliza un parametru într-un CTE şi dacă era procedură stocată.

    Dacă ai probleme, postează codul pe care l-ai scris (cu structura tabelelor, câteva date de test şi rezultatele dorite).

    Răzvan

  •  06-20-2007, 10:58 AM 2124 in reply to 2123

    Re: CTE cu variabile

    Probabil ai un "typo" - vezi de exemplu daca numele parametrului procedurii este altul decat numele parametrului din WHERE.

    Mie mi-a "mers" asa:

    CREATE PROCEDURE SelectProduct

       @ProductID int


    AS
    BEGIN
        WITH prods AS (SELECT * FROM dbo.Product WHERE ProductID = @ProductID)
        SELECT * FROM prods

    END

  •  06-20-2007, 12:00 PM 2125 in reply to 2124

    Re: CTE cu variabile

    Asta e procedura.

    Daca inlocuiesc variabilele cu valori merge perfect.

    Cred ca-mi scapa ceva acum.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:  <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    CREATE PROCEDURE dbo.Nota_Inchidere
     @data1 smalldatetime, @data2 smalldatetime, @nr int, @explicatii varchar(50)
    AS
    BEGIN
     SET NOCOUNT ON;
     DELETE FROM notainchidere;
     INSERT INTO notaInchidere (credit, sumacredit)
      SELECT credit, sum(suma) AS sumacredit FROM note_contabile
      WHERE (data BETWEEN @data1 AND @data2) AND (LEFT(credit,1) = '6' OR LEFT(credit,1)='7')
       GROUP BY credit;
     GO 
     WITH rulajD (debit, suma) AS
     (
      SELECT debit, sum(suma) AS suma FROM note_contabile
      WHERE (data BETWEEN @data1 AND @data2) AND (LEFT(debit,1) = '6' OR LEFT(debit,1)='7')
      GROUP BY debit
     )

     UPDATE notainchidere SET sumadebit = rulajd.suma FROM notainchidere LEFT JOIN rulajD
      ON notainchidere.credit = rulajd.debit;
     GO
     WITH rulajD (debit, suma) AS
     (
      SELECT debit, sum(suma) AS suma FROM note_contabile
      WHERE (data BETWEEN @data1 AND @data2) AND (LEFT(debit,1) = '6' OR LEFT(debit,1)='7')
      GROUP BY debit
     )  
     INSERT INTO notainchidere (debit, sumadebit)
     SELECT debit, suma FROM rulajD WHERE rulajD.debit NOT IN (SELECT credit FROM notainchidere);
     GO
     UPDATE notainchidere SET suma = sumadebit - ISNULL(sumacredit,0), credit =
     CASE
      WHEN Substring(debit,5,2)='01' THEN '121001'
      WHEN Substring(debit,5,2)='02' THEN '121010'
      WHEN Substring(debit,5,2)='03' THEN '121011'
      END
     WHERE LEFT(debit,1) = '6';
     GO
     UPDATE notainchidere SET suma = sumacredit - ISNULL(sumadebit,0), debit =
     CASE
      WHEN Substring(credit,1,4)='7701' THEN '121001'
      WHEN Substring(credit,1,4)='7650' THEN '121001'
      WHEN Substring(credit,1,4)='7511' THEN '121010'
      WHEN Substring(credit,1,4)='7790' THEN '121011'
      END
     WHERE LEFT(debit,1) <> '6';
     GO
     UPDATE NotaInchidere SET data = @data2
     INSERT INTO note_contabile
      (
       nr,
       document,
       data,
       explicatii,
       debit,
       credit,
       suma
      )
       SELECT @nr AS nr, 'Nota inchidere' AS document, data, @explic AS explicatii,
        debit AS credit, credit AS debit, suma FROM notainchidere
    END
    GO


    Sorin Sandu
  •  06-20-2007, 12:07 PM 2126 in reply to 2125

    Re: CTE cu variabile

    Incearca sa comentezi statementurile "GO". Variabilele sunt "valabile" doar in batch-ul curent.

    De asemenea, in penultimul rand, incearca "@explicatii AS explic," 

     

  •  06-20-2007, 12:28 PM 2127 in reply to 2126

    Re: CTE cu variabile

    Exact asta era

    In timp ce tot testam pe bucati am pus GO din loc in loc dar la final am uitat sa le scot.

    Multumesc ptr raspunsuri.


    Sorin Sandu
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems