将查询转换为存储过程 [英] Turning a query into a stored procedure
本文介绍了将查询转换为存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
下面是一个包含临时表的大查询.我一直在试图找出将其转换为存储过程的语法 - 我似乎无法弄清楚.我可以使用变量 @Report_Start_DT 和 @Report_End_DT 选择日期范围.
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
推荐答案
我不会告诉你这是如何工作的，但我会给你一个将查询转换为存储过程的一般经验法则.
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.
编辑
为了帮助您，我将举例说明我的步骤.首先从一个空脚本开始.
In order to help you i will give you examples on my steps. First of all begin with an empty script.
DECLARE 在单独的行中列出您需要作为 SP 输入的参数
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
在接下来的两行中放置一个 BEGIN 和一个 END 这样您就可以确保您的代码可以作为一个整体运行.
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
在 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
在声明之后和 BEGIN 之前，SET 值到您的变量中.
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
运行查询，看看结果是否正常.如果没有，请进行必要的更改，直到您满意为止.
注释 SET 行.
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
在脚本顶部写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
从 SP 的变量中删除单词 DECLARE 并用逗号分隔各行
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
在变量后面加上AS
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
你准备好了
这篇关于将查询转换为存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
