T-SQL

Re: CTE cu variabile

ssandu — Wed, 20 Jun 2007 09:28:24 GMT

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.

Re: CTE cu variabile

Diana — Wed, 20 Jun 2007 09:07:27 GMT

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

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

Re: CTE cu variabile

ssandu — Wed, 20 Jun 2007 09:00:05 GMT

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

Re: CTE cu variabile

Diana — Wed, 20 Jun 2007 07:58:03 GMT

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

Re: CTE cu variabile

rsocol — Wed, 20 Jun 2007 07:52:54 GMT

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

CTE cu variabile

ssandu — Wed, 20 Jun 2007 07:28:23 GMT

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