Turning a query into a stored procedure

Question

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

You cannot have a GO statement in the middle of your stored procedure, it ends the proc at that point. — Jerry Ritcey, Oct 16 '13 at 20:24
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Oct 16 '13 at 20:31

Giannis Paraskevopoulos · Answer 1 · 2015-09-15T09:18:18.110

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

Thank you. I updated the code. I am still having some trouble understanding this. At the end of Report_Start_DT, the comma has a red error underline that says 'Incorrect Syntax' and I don't understand 'SET' — user2270911, Oct 16 '13 at 21:47
@user2270911 You may check my steps with examples. Hope it helps. — Giannis Paraskevopoulos, Oct 17 '13 at 07:59

score 1 · Answer 2 · answered Oct 16 '13 at 20:23

1

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.

answered Oct 16 '13 at 20:23

Joe Stefanelli

132,803
19
237
235

score 1 · Answer 3 · answered Oct 16 '13 at 20:27

The keyword "GO" ends the statement, truncating the stored procedure definition there. The remaining statements are not part of the procedure and executed immediately. Remove all "GO" keywords, though you may optionally put one GO after the last statement in the stored procedure definition.

Turning a query into a stored procedure

3 Answers3

Linked