sql - 将查询转换为存储过程-6ren

sql - 将查询转换为存储过程

转载作者：行者123 更新时间：2023-12-02 05:37:10

下面是一个包含临时表的大查询。我一直在试图找出将其转换为存储过程的语法——我似乎无法弄清楚。我需要能够使用变量 @Report_Start_DT 和 @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

最佳答案

我不会告诉您这是如何工作的，但我会为您提供将查询转换为存储过程的一般经验法则。

编辑

为了帮助您，我将举例说明我的步骤。首先从一个空脚本开始。

DECLARE 在单独的行中您需要作为 SP 输入的参数

DECLARE @Report_Start_DT  datetime
DECLARE @Report_End_DT datetime

在接下来的两行中放置一个BEGIN 和一个END 这样您就可以确保您的代码可以作为一个整体运行。

DECLARE @Report_Start_DT  datetime
DECLARE @Report_End_DT datetime
BEGIN
END

在 BEGIN 和 END 中编写您的查询，就好像它只是一个查询 ...

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 值。

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 行。

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 并用逗号分隔行

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

你准备好了

关于sql - 将查询转换为存储过程，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19413076/

文章推荐： r - 添加或保留前导零而不转换为字符格式

文章推荐： emacs - 排除 LaTeX-mode-hook 是个好主意吗？

文章推荐： java - 带子串的for循环

文章推荐： javascript - 使用javascript的百分比计算器

fortran - 在 PURE 过程 Fortran 中调用类型绑定(bind)过程
如果我声明了类似的类型 type test(NSIZE) integer, len :: NSIZE real :: dummy(NSIZE) contains procedure,
vba - 从 Excel 应用程序隐藏 VBA 过程，但不从其他项目隐藏 VBA 过程
我知道这是一个不太可能的事情，但是由于“选项私有(private)模块”的限制，甚至更糟糕的“私有(private)子/函数”的限制，有谁知道是否有一种方法可以从 Excel 应用程序隐藏 VBA 过
SQL 过程 - 根据其他表的计数更改表列的值
我有两个表，property 和 component。 component.id_property = property.id。我正在尝试创建一个过程，该过程对所选属性的组件进行计数，如果所选属性没
在一行中返回结果的 SQL 过程
我有一份报告，它是在 SSRS 2005 中开发的，我正在使用存储过程从数据库中获取结果。报告输出的结果非常简单，如下图所示。如果假设我正在寻找不同的成员例如:- MemberID c108 c
delphi - 衰落值常规功能/过程
我需要一个通用函数/过程，该函数/过程将根据提供的数据计算出我的淡入淡出时间和值，如下所示：我将字节值保存在字节数组中：这些是起始值。然后，我在其他数组中存储了一些值：这些将是新值。然后我有时间要提
java - 如何在java中为多个按钮设置相同的操作/过程
我想在界面的多个按钮上创建相同的操作。是否只能通过创建单独的操作监听器方法并调用执行操作的方法才可行，还是还有其他方法？是否可以将按钮放在一个组中并执行以下操作:- groupButton.setOn
delphi - 跳转到finally而不退出函数/过程
我有以下情况: procedure Test; begin repeat TryAgain := FALSE; try // Code // Code if this an
Oracle 向一个对象类型添加一个新的成员函数/过程
我正在尝试执行以下操作；假设我在 Oracle 中创建了一个对象类型 create type test as object( name varchar2(12), member procedure p
VHDL - 适用于任何类型数组的函数/过程
问题: 如果可能的话，如何声明一个用于任何类型参数的函数 T其中 T 的唯一约束是它被定义为 1D array如 type T is array ( integer range <> ) of a_r
包含一整年的天数和小时数的 Mysql 过程
我正在尝试创建这个 mysql 过程来制作一个包含今年所有日期和所有时间的表(以一小时为间隔。) CREATE TABLE FECHAS ( created_at datetime ); CREA
带有预选光标数据的 MySQL 过程
所以，我在这里面临一个问题，这让我发疯，我认为这是一个愚蠢的错误，所以我不是 MySQL 的新手，但它并不像我想象的那样工作。尝试将此语句部署到 MySQL 后，我收到此错误: ERROR 106
带参数语法的 MySQL 过程
我有一个架构，其中包含星球大战中的人物列表、他们出现的电影、他们访问的行星等。这是架构: CREATE DATABASE IF NOT EXISTS `starwarsFINAL` /*!40100
具有多个计数和分组依据的 MySql 过程
我一直在为一家慈善机构创建一款应用程序，允许家庭在节日期间注册接收礼物。数据库组织有多个表。下面列出了这些表(及其架构/创建语句): CREATE TABLE IF NOT EXISTS ValidD
mysql - 按日期删除表而不删除系统表的函数/过程
正如上面标题所解释的，我正在尝试编写一个sql函数来按日期删除表而不删除系统表。我在此消息下方放置了一张图片，以便直观地解释我的问题。任何帮助将不胜感激!感谢您的时间! 最佳答案您可以通过查询INF
使用游标的 mysql 过程
DELIMITER $$ CREATE PROCEDURE INSERT_NONE_HISTORY_CHECKBOX() BEGIN DECLARE note_id bigint(20); F
postgresql 过程/触发器
是否可以编写一个存储过程或触发器，在特定时间在数据库内部自动执行，而无需来自应用程序的任何调用？如果是，那么任何人都可以给我一个例子或链接到一些我可以阅读如何做到这一点的资源。最佳答案查看 pgA
mysql 过程 - 在第二个查询中使用结果并重新格式化日期
我需要创建一个过程:1)从表中的字段中选择一些文本并将其存储在变量中2) 更新相同的记录字段，仅添加 yyyymmdd 格式的日期以及过程中的附加文本输入...类似这样的... delimiter /
MySQL 过程 - 如果不存在则插入行
好的，这就是我想做的: 如果条目已存在(例如基于字段name)，则只需返回其id 如果没有，请添加这是我迄今为止所管理的(对于“如果不存在，则创建它”部分): INSERT INTO `object
MySQL 过程 - 开始工作
以下是我编写的程序，用于找出每位客户每天购买的前 10 件商品。这是我尝试过的第一个 PL/SQL 操作。它没有达到我预期的效果。我使用的逻辑是接受开始日期、结束日期以及我对每个客户感兴趣的前“x
mysql - 列不能为空 - 过程
我正在尝试在MySQL中创建一个过程那insert week s(当年)发送至我的 week table 。但存在一个问题，因为在为下一行添加第一行后，我收到错误: number column can

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 将查询转换为存储过程