Newsgroup: it.comp.appl.access
Titolo: [lungo] usare una query memorizzata sul server SQL (stored procedure)

buongiorno
 
oggi qui si lavora...
 
ho una procedura access che accede tramite odbc a un database sql server
 
dati i limiti del mio access 2010 (p.e. max 254 campi per tabella) uso 
delle VIEW per selezionare i (pochi) campi che mi servono da tabelle molto 
corpose (tipo le anagrafiche o i documenti), e fin qui tutto bene, sono 
semplici select, con qualche UNION 
 
il problema è che le view non permettono l'utilizzo di *tabelle temporanee* 
per cui, volendo far fare tutti i conti a SQL e avere bello pronto il 
risultato in access (invece di farlo da questa parte), il giochino non 
funziona, come ampiamente documentato sul web
 
la scappatoia dovrebbe essere una 'Stored procedure': nell'apposito menu di 
MSSMS il comando "create a new stored procedure" genera un modello base che 
riporto qui sotto:
 
 
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = 
<Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = 
<Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
 
 
ora la *DOMANDONA* : come e dove infilo tutto il codice qui *sotto* nel 
modello qui *sopra* ? 
 
 
nota bene: scusate se è poco leggibile, ma ho rimosso tutte le indentazioni 
per evitare gli 'a capo' automatici dopo la 72a colonna che lo rendevano 
impossibile da leggere; se avete la pazienza di rispondere, nel bene e nel 
male, ricordatevi comunque di 'quotar bene' (
https://is.gd/PwXdFJ
 e 
https://is.gd/wwbxMc
)
 
 
/*
Reporting BOS
*/
 
DECLARE @Year AS VARCHAR(4)
DECLARE @Month AS VARCHAR(2) -- used for exchange rate
 
-- values manually forced for testing 
SET @Year = format(CURRENT_TIMESTAMP, 'yyyy') 
SET @Month = format(CURRENT_TIMESTAMP, 'MM') 
 
-- step 1: collect data from DLN1 
SELECT *
INTO #tmp
FROM (SELECT b.docentry,
 d.u_shortname AS Distr,
 g.u_shortname AS Manuf
 --, isnull(d.U_Responsible, '0000') as SalesmanCode
 ,
 Isnull(f.u_u_shortname, 'NULL') AS Salesman,
 d.country AS Country,
 h.itmsgrpnam AS ProdType,
 Isnull(a.totalfrgn * xc.rate / xe.rate, 0) AS Delivered -- in EUR
 ,
 Format(b.u_man_date_inv, 'yyyy') AS DelivYear,
 Format(b.u_man_date_inv, 'MM') AS DelivMonth,
 CASE
 WHEN Year(b.u_man_date_inv) = @Year - 3 THEN 'P3'
 WHEN Year(b.u_man_date_inv) = @Year - 2 THEN 'P2'
 WHEN Year(b.u_man_date_inv) = @Year - 1 THEN 'P1'
 WHEN Year(b.u_man_date_inv) = @Year THEN 'C'
 WHEN Year(b.u_man_date_inv) > @Year THEN 'N'
 ELSE '-'
 END AS PCN
 FROM dln1 a
 INNER JOIN odln b ON b.docentry = a.docentry
 LEFT JOIN [@mti_ditta_relations] c ON c.code = b.u_relation
 LEFT JOIN ocrd d ON d.cardcode = c.u_dist_cardcode
 LEFT JOIN oitm e ON e.itemcode = a.itemcode
 LEFT JOIN oitb h ON h.itmsgrpcod = e.itmsgrpcod
 LEFT JOIN [@a_ditta_resp_salesm] f ON f.code = d.u_responsible
 INNER JOIN ortt xc ON xc.ratedate = @Year + @Month 
 + '01' AND xc.currency = b.doccur
 INNER JOIN ortt xe ON xe.ratedate = @Year + @Month 
 + '01' AND xe.currency = 'EUR'
 LEFT JOIN ocrd g ON g.cardcode = c.u_man_cardcode
 WHERE b.canceled = 'N'
 AND g.u_shortname <> 'HTC'
 AND Isnull(e.u_dittacode, 'NULL') NOT LIKE 'MI[0123456789]%'
 AND a.itemcode NOT LIKE 'EXTRA%'
 AND a.itemcode NOT LIKE 'COMMISSION%') tmp
 
-- step 2: group data per Distr, Manuf, etc and year
SELECT *
INTO #tmp1
FROM (SELECT distr,
 manuf,
 salesman,
 country,
 prodtype,
 Isnull(Sum(CASE
 WHEN pcn = 'P3' THEN delivered
 END), 0) AS P3,
 Isnull(Sum(CASE
 WHEN pcn = 'P2' THEN delivered
 END), 0) AS P2,
 Isnull(Sum(CASE
 WHEN pcn = 'P1' THEN delivered
 END), 0) AS P1,
 Isnull(Sum(CASE
 WHEN pcn = 'C' THEN delivered
 END), 0) AS C0
 FROM #tmp
 GROUP BY distr,
 manuf,
 salesman,
 country,
 prodtype) tmp1
 
-- step 3: not necessary
 
-- step 4: calc budget 
SELECT *
INTO #tmp3
FROM (SELECT DISTINCT dis.u_shortname AS Distr,
 man.u_shortname AS Manuf,
 Isnull(f.u_u_shortname, 'NULL') AS Salesman,
 dis.country AS Country,
 s.itmsgrpnam AS ProdType,
 r.u_item AS ProdCode,
 r.u_item_catnr AS ModelNo,
 r.u_fin_qty AS QtyFin,
 r.u_fin_price AS PriceFin,
 r.u_ong_qty AS QtyOnG,
 p.price AS PriceOnG,
 h.u_rel AS Relation,
 h.u_relname AS RelName,
 h.u_b_year AS BudgetYear,
 h.u_rel_cur AS Currency,
 h.NAME AS VPref,
 xc.rate AS CHFrate,
 xe.rate AS EURrate,
 r.u_fin_qty * r.u_fin_price * xc.rate / xe.rate AS FinBudgetTurnover,
 r.u_ong_qty * p.price * xc.rate / xe.rate AS OnGBudgetTurnover
 FROM [@ditta_bud_row] r
 INNER JOIN [@ditta_bud_h] h ON h.code = r.code
 INNER JOIN [@mti_ditta_relations] l ON l.code = h.u_rel
 INNER JOIN ocrd dis ON dis.cardcode = l.u_dist_cardcode
 LEFT JOIN [@a_ditta_resp_salesm] f ON f.code = dis.u_responsible
 INNER JOIN ortt xc ON xc.ratedate = @Year + @Month 
 + '01' AND xc.currency = h.u_rel_cur
 INNER JOIN ortt xe ON xe.ratedate = @Year + @Month 
 + '01' AND xe.currency = 'EUR'
 INNER JOIN ocrd man ON man.cardcode = l.u_man_cardcode
 INNER JOIN ospp p ON p.u_relation = h.u_rel AND p.itemcode = r.u_item
 LEFT JOIN oitm q ON q.itemcode = r.u_item
 LEFT JOIN oitb s ON s.itmsgrpcod = q.itmsgrpcod
 WHERE h.u_rel IS NOT NULL
 AND r.u_fin_qty IS NOT NULL
 AND r.u_fin_qty > 0
 AND h.canceled = 'N'
 AND h.u_b_year = @Year
 AND h.u_rel_cur IS NOT NULL) tmp3
 
-- select * from #tmp3
SELECT *
INTO #tmp4
FROM (SELECT distr,
 manuf,
 salesman,
 country,
 prodtype,
 Sum(finbudgetturnover) AS FinBudget,
 Sum(ongbudgetturnover) AS OnGBudget
 FROM #tmp3
 GROUP BY distr,
 manuf,
 salesman,
 country,
 prodtype) tmp4
 
-- select * from #tmp4 -- testing...
 
-- step 5: collect data from RDR1 
SELECT *
INTO #tmp5
FROM (SELECT f.u_shortname AS Distr,
 d.u_shortname AS Manuf,
 Isnull(h.u_u_shortname, 'NULL') AS Salesman,
 f.country AS Country,
 g.itmsgrpnam AS ProdType,
 a.openqty * a.price * xc.rate / xe.rate AS ValResidual,
 Format(b.docduedate, 'yyyy') AS DelivYear,
 Format(b.docduedate, 'MM') AS DelivMonth,
 CASE
 WHEN Year(b.docduedate) < @Year THEN 'P'
 WHEN Year(b.docduedate) = @Year THEN 'C'
 WHEN Year(b.docduedate) > @Year THEN 'N'
 ELSE '-'
 END AS PCN
 --, * 
 FROM rdr1 a
 INNER JOIN ordr b ON b.docentry = a.docentry
 INNER JOIN [@mti_ditta_relations] c ON c.code = b.u_relation
 INNER JOIN ocrd d ON d.cardcode = c.u_man_cardcode
 LEFT JOIN oitm e ON e.itemcode = a.itemcode
 LEFT JOIN oitb g ON g.itmsgrpcod = e.itmsgrpcod
 INNER JOIN ocrd f ON f.cardcode = c.u_dist_cardcode
 LEFT JOIN [@a_ditta_resp_salesm] h ON h.code = f.u_responsible
 INNER JOIN ortt xc ON xc.ratedate = @Year + @Month 
 + '01' AND xc.currency = b.doccur
 INNER JOIN ortt xe ON xe.ratedate = @Year + @Month 
 + '01' AND xe.currency = 'EUR'
 WHERE b.canceled = 'N'
 AND d.u_shortname <> 'HTC'
 AND Isnull(e.u_dittacode, 'NULL') NOT LIKE 'MI[0123456789]%') tmp5
 
-- select * from #tmp5 -- testing...
 
-- step 6: group data per distributor, manufacturer and year
SELECT *
INTO #tmp6
FROM (SELECT distr,
 manuf,
 salesman,
 country,
 prodtype,
 Isnull(Sum(CASE
 WHEN pcn = 'C'
 OR pcn = 'P' THEN valresidual
 END), 0) AS TBD,
 Isnull(Sum(CASE
 WHEN pcn = 'N' THEN valresidual
 END), 0) AS N1
 FROM #tmp5
 GROUP BY distr,
 manuf,
 salesman,
 country,
 prodtype) tmp6
 
-- select * from #tmp6 -- testing...
 
-- step 7b: print data method 2
SELECT *
INTO #tmp7
FROM (SELECT '1' AS Total,
 a.distr,
 a.manuf,
 a.salesman,
 a.country,
 a.prodtype,
 Isnull(a.p3, 0) AS P3,
 Isnull(a.p2, 0) AS P2,
 Isnull(a.p1, 0) AS P1,
 0 AS SB,
 0 AS OB,
 Isnull(a.c0, 0) AS C0,
 0 AS TBD,
 Isnull(a.c0, 0) AS T0,
 0 AS N1
 FROM #tmp1 a
 UNION ALL
 SELECT '1' AS Total,
 b.distr,
 b.manuf,
 b.salesman,
 b.country,
 b.prodtype,
 0 AS P3,
 0 AS P2,
 0 AS P1,
 Isnull(b.finbudget, 0) AS SB,
 Isnull(b.ongbudget, 0) AS OB,
 0 AS C0,
 0 AS TBD,
 0 AS T0,
 0 AS N1
 FROM #tmp4 b
 UNION ALL
 SELECT '1' AS Total,
 c.distr,
 c.manuf,
 c.salesman,
 c.country,
 c.prodtype,
 0 AS P3,
 0 AS P2,
 0 AS P1,
 0 AS SB,
 0 AS OB,
 0 AS C0,
 Isnull(c.tbd, 0) AS TBD,
 Isnull(c.tbd, 0) AS T0,
 Isnull(c.n1, 0) AS N1
 FROM #tmp6 c) tmp7
 
-- final query 5 : M
SELECT manuf, distr,
 Sum(p3) AS P3 -- delivered 3 years ago
 ,
 Sum(p2) AS P2 -- delivered 2 years ago
 ,
 Sum(p1) AS P1 -- delivered 1 year ago
 ,
 Sum(sb) AS SB -- sales budget
 ,
 Sum(ob) AS OB -- ongoing budget
 ,
 Sum(c0) AS C0 -- delivered current year
 ,
 Sum(tbd) AS TBD -- to be delivered current year
 ,
 Sum(t0) AS T0 -- sum: selivered + to be delivered
 ,
 Sum(t0 - ob) AS DB -- difference with ongoing budget
 ,
 Sum(n1) AS N1 -- to be delivered next year
FROM #tmp7
GROUP BY manuf, Distr
ORDER BY manuf, distr
 
-- step 8: release temp tables 
DROP TABLE #tmp -- DLN1 detail
 
DROP TABLE #tmp1 -- DLN1 grouped per D/M/S/C/T
 
DROP TABLE #tmp3 -- budget detail
 
DROP TABLE #tmp4 -- budget grouped per D/M/S/C/T
 
DROP TABLE #tmp5 -- RDR1 detail
 
DROP TABLE #tmp6 -- RDR1 grouped per D/M/S/C/T
 
DROP TABLE #tmp7 -- final query method 2
 
 
 
 
 
-- 
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

 ........... [ al lavoro ] ...........
 
Ciao,
visto che non usi parametri il modello si semplifica parecchio:
 
CREATE PROCEDURE NomeDellaProcedura
AS
BEGIN
 
SET NOCOUNT ON;
 
-- Inserisci il tuo codice SQL qui
-- poi premi F5 ed esegui per creare la procedura
 
END
GO
 
Ciao
Mat.

 
 
grazie Potone, nel pomeriggio mi ci dedico



Io ho dato per scontato che tu usi Sql Server Management Studio per generare la procedura, ecco  da dove arriva "F5 per eseguire" se usi qualche altro tool dopo aver preparato il codice sql devi eseguirlo/lanciarlo per creare la procedura.
 
Ciao,
Mat.