Menu
Below is a big query which includes temporary tables. I have been trying to figure out the syntax to turn this into a stored procedure - I can't seem to figure it out. I meed to be able to select a date range using the variables @Report_Start_DT and @Report_End_DT.
CREATE PROCEDURE [gw_ppp].[dbo].[Prv_Child_Not_Seen] (@Report_Start_DT, @Report_End_DT)
as
BEGIN
DECLARE @Report_Start_DT datetime
DECLARE @Report_End_DT datetime
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
912
asked Oct 27 '25 11:10
I won't tell you how this is going to work but i will give you a general rule of thumb in turning queries into stored procedures.
EDIT
In order to help you i will give you examples on my steps. First of all begin with an empty script.
DECLARE in separate lines the arguments you need to have as inputs of your SP
DECLARE @Report_Start_DT datetime
DECLARE @Report_End_DT datetime
In the next two lines put a BEGIN and an END This way you will be sure that your code can run as a whole.
DECLARE @Report_Start_DT datetime
DECLARE @Report_End_DT datetime
BEGIN
END
Inside the BEGIN and END write your query as if it was just a query ...
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
After the declaration and before the BEGIN, SET values to your 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
Run the query and see if you are ok with the results. If not, make the necessary changes, until you are ok with it.
Comment out the SET lines.
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
On the top of the script write 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
Remove the word DECLARE from the variables of your SP and separate the lines with comma
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
Put the word AS after the 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
You are ready
189
Get rid of the GO statements. GO is a batch terminator, not a T-SQL command, so you're cutting off your procedure code prematurely by using it.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
