Transformer une requête dans une procédure stockée

Je ne vais pas vous dire comment cela va fonctionner, mais je vais vous donner une règle générale dans le tournant des requêtes dans des procédures stockées.

MODIFIER

Afin de vous aider je vous donne des exemples sur mes pas. Tout d'abord commencer avec un vide script.

DECLARE dans des lignes séparées les arguments que vous devez avoir, comme les entrées de votre SP

DECLARE @Report_Start_DT  datetime
DECLARE @Report_End_DT datetime

Dans les deux lignes a mis un BEGIN et un END de Cette façon, vous serez sûr que votre code peut fonctionner comme un tout.

DECLARE @Report_Start_DT  datetime
DECLARE @Report_End_DT datetime
BEGIN
END

À l'intérieur de la BEGIN et END écrire votre requête, comme si c'était juste une requête ...

DECLARE @Report_Start_DT  datetime
DECLARE @Report_End_DT datetime
BEGIN
CREATE TABLE #Temp_Contact 
(
Person_ID INT,
Contact_Date DATETIME,
Success INT
)
INSERT INTO #Temp_Contact 
(
Person_ID, 
Contact_Date, 
Success
)
SELECT  Person_ID, 
Contact_Date,
gw_ppp.dbo.fnWasContacted(Contact_Method, Contact_Result, Participant)
FROM    gw_dw.dbo.DimContacts_Child
where   Contact_Date between DATEADD(month, -5, @Report_Start_DT) and  DATEADD(day, -1, @Report_Start_DT)
CREATE TABLE #Temp_Months  
(
Month VARCHAR(30),
Year int
)
INSERT INTO #Temp_Months   
VALUES 
('January', 2010),
('January', 2011),
('January', 2012),
('January', 2013),
('January', 2014),
('January', 2015),
('January', 2016),
('January', 2017),
('February',2010),  
('February',2011),  
('February',2012),  
('February',2013),  
('February',2014),  
('February',2015),  
('February',2016),
('February',2017),
('March',2010), 
('March',2011), 
('March',2012), 
('March',2013), 
('March',2014), 
('March',2015), 
('March',2016),
('March',2017),
('April',2010), 
('April',2011), 
('April',2012), 
('April',2013), 
('April',2014), 
('April',2015), 
('April',2016),
('April',2017),
('May',2010),   
('May',2011),   
('May',2012),   
('May',2013),   
('May',2014),   
('May',2015),   
('May',2016),
('May',2017),
('June',2010),  
('June',2011),  
('June',2012),  
('June',2013),  
('June',2014),  
('June',2015),  
('June',2016),
('June',2017),
('July',2010),  
('July',2011),  
('July',2012),  
('July',2013),  
('July',2014),  
('July',2015),  
('July',2016),
('July',2017),
('August',2010),    
('August',2011),    
('August',2012),    
('August',2013),    
('August',2014),    
('August',2015),    
('August',2016),
('August',2017),
('September',2010), 
('September',2011), 
('September',2012), 
('September',2013), 
('September',2014), 
('September',2015), 
('September',2016),
('September',2017),
('October',2010),   
('October',2011),   
('October',2012),   
('October',2013),   
('October',2014),   
('October',2015),   
('October',2016),
('October',2017),
('November',2010),  
('November',2011),  
('November',2012),  
('November',2013),  
('November',2014),  
('November',2015),  
('November',2016),
('November',2017),
('December',2010),  
('December',2011),  
('December',2012),  
('December',2013),  
('December',2014),  
('December',2015),  
('December',2016),
('December',2017)
select  distinct
a.Person_ID,
a.Child_Name,
a.Case_ID,
a.Stage_ID, 
a.Site,
a.Unit, 
a.Worker_Name,
src2.month, 
src2.year, 
src2.result
from    (
SELECT  distinct
s.POSITION_NBR, 
a.Person_ID,
a.Child_Name,
b.Case_ID,
b.Stage_ID, 
b.Entry_Date,
b.Site,
b.Unit, 
b.Worker_Name,
b.Worker_Role,
b.Worker_ID
from    (
select  distinct 
Person_ID,
Child_Name
FROM    gw_dw.dbo.DimContacts_Child  
where   Unit like 'P%' 
and (Contact_Date >=  @Report_Start_DT AND Contact_Date <=@Report_End_DT)
group by Person_ID,Child_Name
having  sum(case when (Contact_Method='Face To Face') AND (Contact_Result <> 'Attempted') AND (Participant='Yes') then 1 else 0 end) = 0 
) as A 
inner join
(
Select  distinct 
Person_ID,
Case_ID,
Stage_ID, 
Entry_Date,
Unit, 
Site,
Worker_Name,
Worker_Role, 
Worker_ID, 
Owner_Full_Name
from    gw_dw.dbo.DimContacts_Child b
where   Unit like 'P%' 
and (Contact_Date >= @Report_Start_DT  AND Contact_Date <=@Report_End_DT )
group by Worker_Name,
Worker_Role, 
Worker_ID,
Unit,
Person_ID,
Case_ID,
Stage_ID,
Entry_Date, 
Site, 
Owner_Full_Name 
having  sum(case when (Contact_Method='Face To Face') AND (Contact_Result <> 'Attempted') AND (Participant='Yes') then 1 else 0 end) = 0  
) as B 
on  A.Person_ID = B.Person_ID   
left join ECMS_BACKUP.dbo.STAFF s
on s.CONNX_WORKER_ID=b.Worker_ID
) as A
left join
(
select  lft.Person_ID,  
m.Month,
m.Year, 
gw_PPP.dbo.fnFmtContact(src.cnt) result
FROM    gw_dw.dbo.DimContacts_Child lft
JOIN  #Temp_Months m
on m.Month=DATENAME(month, Contact_Date)
and m.Year=DATENAME(YEAR, Contact_Date)
and Contact_Date between DATEADD(month, -5, @Report_Start_DT) and  DATEADD(day, -1, @Report_Start_DT)
LEFT OUTER JOIN
(
SELECT  Person_ID, 
DATENAME(month, Contact_Date) as Month, 
DATENAME(YEAR, Contact_Date) as Year, 
sum(Success) as cnt
FROM    #Temp_Contact
GROUP BY Person_ID, 
DATENAME(month, Contact_Date), 
DATENAME(YEAR, Contact_Date)) AS src
ON (lft.Person_ID = src.Person_ID AND DATENAME(month, Contact_Date) = src.month)
) AS src2
on src2.Person_ID=a.Person_ID
END

Après la déclaration et avant le BEGIN, SET les valeurs de vos variables.

DECLARE @Report_Start_DT  datetime
DECLARE @Report_End_DT datetime
SET @Report_Start_DT = '20130101'
SET @Report_End_DT = '20130601'
BEGIN
--Code as put in the prev step. I excluded it just to not make the post long in length
END

Exécuter la requête et voir si vous êtes ok avec les résultats. Si pas, de faire les modifications nécessaires, jusqu'à ce que vous êtes ok avec elle.

---

Comment out la SET lignes.

DECLARE @Report_Start_DT  datetime
DECLARE @Report_End_DT datetime
--SET @Report_Start_DT = '20130101'
--SET @Report_End_DT = '20130601'
BEGIN
--Code as put in the prev step. I excluded it just to not make the post long in length
END

Sur le haut du script écrire CREATE PROCEDURE [PROCEDURE_NAME]

CREATE PROCEDURE [gw_ppp].[dbo].[Prv_Child_Not_Seen]  
DECLARE @Report_Start_DT  datetime
DECLARE @Report_End_DT datetime
--SET @Report_Start_DT = '20130101'
--SET @Report_End_DT = '20130601'
BEGIN
--Code as put in the prev step. I excluded it just to not make the post long in length
END

Supprimer le mot DECLARE à partir des variables de votre SP et séparer les lignes avec virgule

CREATE PROCEDURE [gw_ppp].[dbo].[Prv_Child_Not_Seen]  
@Report_Start_DT  datetime,
@Report_End_DT datetime
--SET @Report_Start_DT = '20130101'
--SET @Report_End_DT = '20130601'
BEGIN
--Code as put in the prev step. I excluded it just to not make the post long in length
END

Mettre le mot AS après les variables

CREATE PROCEDURE [gw_ppp].[dbo].[Prv_Child_Not_Seen]  
@Report_Start_DT AS datetime,
@Report_End_DT AS datetime
AS
--SET @Report_Start_DT = '20130101'
--SET @Report_End_DT = '20130601'
BEGIN
--Code as put in the prev step. I excluded it just to not make the post long in length
END

Vous êtes prêt

Le mot-clé "GO" termine la déclaration, la troncation de la définition de la procédure stockée là. Les instructions suivantes ne font pas partie de la procédure et exécuté immédiatement. Supprimer tous les "GO" mots-clés, même si vous pouvez éventuellement mettre un ALLER après la dernière instruction dans la définition de la procédure stockée.