如何将此sql语句转换为storeproc [英] How to convert this sql statement to storeproc

查看：100
本文介绍了如何将此sql语句转换为storeproc的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

选择Dly_DC_LostSlsSnpsht_v。PlntCdPlnt_Id_Cd，Dly_DC_LostSlsSnpsht_v。DivCdDiv_Cd，

GlblCatSum。GlblCatSumCdGbl_Cat_Sum_Cd，（coalesce（glblCatSum） 。GlblCatSumDesc，

'未知'））Gbl_Cat_Sum_Desc，Dly_DC_LostSlsSnpsht_v。ProdtCdProd_Cd，

ProdtGPI。ProdtLglLongNm Matl_Nm，Dly_DC_LostSlsSnpsht_v。WhlslPrcAmt_USDWhlsl_Prc__USD_，

sum（Dly_DC_LostSlsSnpsht_v。EstdLostSlsQty）Estd_Lost_Sales_Qty，

sum（Dly_DC_LostSlsSnpsht_v。 EstdLostSlsAmt）Estd_Lost_Sales_Amt，

sum（Dly_DC_LostSlsSnpsht_v。EstdDmndQty）Tot_AO_Dmd_Qty，

（总和（Dly_DC_LostSlsSnpsht_v。EstdLostSlsQty）* 1.0e0）/（NULLIF（sum（Dly_DC_LostSlsSnpsht_v。EstdDmndQty），

0））Estd_Lost_Sales__
来自EIS的
。Dly_DC_LostSlsSnpsht_vDly_DC_LostSlsSnpsht_v ，

EIS。GlblCatSumGlblCatSum，EIS。ProdtGPIProdtGPI，

EIS。GlblCatC oreFcsGlblCatCoreFcs

其中Dly_DC_LostSlsSnpsht_v。日期之间的CalRunDt - 1

和日期

和Dly_DC_LostSlsSnpsht_v。ProdtCd =ProdtGPI。ProdtCd

和GlblCatCoreFcs。GlblCatCoreFcsCd=ProdtGPI。GlblCatCoreFcsCd

和GlblCatSum。GlblCatSumCd= GlblCatCoreFcs。GlblCatSumCd

group byDly_DC_LostSlsSnpsht_v。PlntCd，Dly_DC_LostSlsSnpsht_v。DivCd，GlblCatSum。GlblCatSumCd，（coalesce（GlblCatSum。GlblCatSumDesc ，'未知'）），Dly_DC_LostSlsSnpsht_v。ProdtCd，ProdtGPI。ProdtLglLongNm，Dly_DC_LostSlsSnpsht_v。WhlslPrcAmt_USD

解决方案





如果您提到的查询是您要使用的确切查询，

  CREATE   PROCEDURE  ProcedureName 
 
  AS  
  BEG IN  
   -   添加SET NOCOUNT ON以防止来自 $的额外结果集b $ b   -   干扰SELECT语句。 
  SET   NOCOUNT   ON ; 
 
   -   在此处插入程序语句 
  SELECT  
   Dly_DC_LostSlsSnpsht_v。  PlntCd   Plnt_Id_Cd 
，  Dly_DC_LostSlsSnpsht_v。  DivCd   Div_Cd 
，  GlblCatSum。  GlblCatSumCd   Gbl_Cat_Sum_Cd 
，（ coalesce （  GlblCatSum。  GlblCatSumDesc，'  Unknow'））  Gbl_Cat_Sum_Desc 
，  Dly_DC_LostSlsSnpsht_v。  ProdtCd   Prod_Cd 
，  ProdtGPI。  ProdtLglLongNm    Matl_Nm 
，  Dly_DC_LostSlsSnpsht_v。  WhlslPrcAmt_USD   Whlsl_Prc__USD _ 
，SUM（  Dly_DC_LostSlsSnpsht_v。  EstdLostSlsQty）   Estd_Lost_Sales_Qty 
，SUM（  Dly_DC_LostSlsSnpsht_v。  EstdLostSlsAmt）  Estd_Lost_Sales_Amt 
，SUM（   Dly_DC_LostSlsSnpsht_v。  EstdDmndQty）  Tot_AO_Dmd_Qty 
，（SUM（  Dly_DC_LostSlsSnpsht_v.  EstdLostSlsQty）*  1  .0e0）/（ NULLIF （SUM（  Dly_DC_LostSlsSnpsht_v。  EstdDmndQty）， 0 ））  Estd_Lost_Sales __ 
  FROM  
   EIS。  Dly_DC_LostSlsSnpsht_v   Dly_DC_LostSlsSnpsht_v  
，  EIS。  GlblCatSum   GlblCatSum 
，  EIS。  ProdtGPI   ProdtGPI 
，  EIS。  GlblCatCoreFcs   GlblCatCoreFcs 
 
  WHERE    Dly_DC_LostSlsSnpsht_v。  CalRunDt  BETWEEN   DATE   -   1  
  AND   DATE  
  AND     Dly_DC_LostSlsSnpsht_v。  ProdtCd =   ProdtGPI。  ProdtCd 
  AND    GlblCatCoreFcs。  GlblCatCoreFcsCd =   ProdtGPI。  GlblCatCoreFcsCd 
  AND    GlblCatSum。  GlblCatSumCd =   GlblCatCoreFcs。  GlblCatSumCd 
  GROUP   BY    Dly_DC_LostSlsSnpsht_v。  PlntCd，  Dly_DC_LostSlsSnpsht_v。  DivCd，  GlblCatSum。  GlblCatSumCd，（ coalesce （  GlblCatSum。   GlblCatSumDesc，'  Unknow'）），  Dly_DC_LostSlsSnpsht_v。  ProdtCd，  ProdtGPI。  ProdtLglLongNm，  Dly_DC_LostSlsSnpsht_v。  WhlslPrcAmt_USD 
  END  
  GO


作为解决方案1的补充，您可以按照以下步骤操作：



1.了解贵公司是否有文件记录命名约定规则。



2.为存储过程创建一个有效的名称

这不是一个笑话。一个好名字可以在以后保存很多解释，你也可能必须遵循某些命名约定。



3.确定查询中的变量。

这些是您在WHERE子句中使用的列。

这将构建存储过程的参数列表。

还标识每个变量的数据类型。



4.根据解决方案1中的模板创建存储过程。



5.复制您对程序正文的查询。



6.用输入参数替换WHERE子句中的列。


不确定你想要什么，但你可以使用SQL提供的默认模板创建存储过程



你可以在这个过程中添加日期参数以使其可重用（日期）与BETWEEN运算符一起使用的值）

 CREATE PROCEDURE <   Procedure_Name，    sysname，    ProcedureName  >  
  - 在此处添加存储过程的参数
 <   @ Param1，   < span class =code-attribute> sysname，    @ p1  >  <   Datatype_For_Param1，   ，    int  >  = <   Default_Value_For_Param1，   ，    0  > ，
 <   @ Param2 ，    sysname，   < span class =code-attribute> @ p2  >  <   Datatype_For_Param2，   ，    int  >  = <   Default_Value_For_Param2，   ， < span class =code-attribute>   0  >  
 AS 
 BEGIN 
  - 添加SET NOCOUNT ON以防止
的额外结果集 - 干扰SELECT语句。 
 SET NOCOUNT ON; 
 
  - 在此插入程序语句
 SELECT <   @ Param1，    sysname，    @ p1  > ，<   @ Param2，    sysname，    @ p2  >  
 END 
 GO


select "Dly_DC_LostSlsSnpsht_v"."PlntCd" "Plnt_Id_Cd" , "Dly_DC_LostSlsSnpsht_v"."DivCd" "Div_Cd" ,
"GlblCatSum"."GlblCatSumCd" "Gbl_Cat_Sum_Cd" , (coalesce("GlblCatSum"."GlblCatSumDesc",
'Unknow')) "Gbl_Cat_Sum_Desc" , "Dly_DC_LostSlsSnpsht_v"."ProdtCd" "Prod_Cd" ,
"ProdtGPI"."ProdtLglLongNm" "Matl_Nm" , "Dly_DC_LostSlsSnpsht_v"."WhlslPrcAmt_USD" "Whlsl_Prc__USD_" ,
sum("Dly_DC_LostSlsSnpsht_v"."EstdLostSlsQty") "Estd_Lost_Sales_Qty" ,
sum("Dly_DC_LostSlsSnpsht_v"."EstdLostSlsAmt") "Estd_Lost_Sales_Amt" ,
sum("Dly_DC_LostSlsSnpsht_v"."EstdDmndQty") "Tot_AO_Dmd_Qty" ,
(sum("Dly_DC_LostSlsSnpsht_v"."EstdLostSlsQty") * 1.0e0) / (NULLIF(sum("Dly_DC_LostSlsSnpsht_v"."EstdDmndQty"),
0)) "Estd_Lost_Sales__"
from "EIS"."Dly_DC_LostSlsSnpsht_v" "Dly_DC_LostSlsSnpsht_v",
"EIS"."GlblCatSum" "GlblCatSum", "EIS"."ProdtGPI" "ProdtGPI",
"EIS"."GlblCatCoreFcs" "GlblCatCoreFcs"
where "Dly_DC_LostSlsSnpsht_v"."CalRunDt" between date - 1
and date
and "Dly_DC_LostSlsSnpsht_v"."ProdtCd" = "ProdtGPI"."ProdtCd"
and "GlblCatCoreFcs"."GlblCatCoreFcsCd" = "ProdtGPI"."GlblCatCoreFcsCd"
and "GlblCatSum"."GlblCatSumCd" = "GlblCatCoreFcs"."GlblCatSumCd"
group by "Dly_DC_LostSlsSnpsht_v"."PlntCd", "Dly_DC_LostSlsSnpsht_v"."DivCd", "GlblCatSum"."GlblCatSumCd", (coalesce("GlblCatSum"."GlblCatSumDesc", 'Unknow')), "Dly_DC_LostSlsSnpsht_v"."ProdtCd", "ProdtGPI"."ProdtLglLongNm", "Dly_DC_LostSlsSnpsht_v"."WhlslPrcAmt_USD"

解决方案

Hi,

If the query you mentioned is the exact one you are going to use, 

 CREATE PROCEDURE ProcedureName
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    -- Insert statements for procedure here
    SELECT 
    "Dly_DC_LostSlsSnpsht_v"."PlntCd" "Plnt_Id_Cd"
    ,"Dly_DC_LostSlsSnpsht_v"."DivCd" "Div_Cd"
    ,"GlblCatSum"."GlblCatSumCd" "Gbl_Cat_Sum_Cd"
    ,(coalesce("GlblCatSum"."GlblCatSumDesc",'Unknow')) "Gbl_Cat_Sum_Desc"
    ,"Dly_DC_LostSlsSnpsht_v"."ProdtCd" "Prod_Cd"
    ,"ProdtGPI"."ProdtLglLongNm" "Matl_Nm"
    ,"Dly_DC_LostSlsSnpsht_v"."WhlslPrcAmt_USD" "Whlsl_Prc__USD_"
    ,SUM("Dly_DC_LostSlsSnpsht_v"."EstdLostSlsQty") "Estd_Lost_Sales_Qty"
    ,SUM("Dly_DC_LostSlsSnpsht_v"."EstdLostSlsAmt") "Estd_Lost_Sales_Amt"
    ,SUM("Dly_DC_LostSlsSnpsht_v"."EstdDmndQty") "Tot_AO_Dmd_Qty"
    ,(SUM("Dly_DC_LostSlsSnpsht_v"."EstdLostSlsQty") * 1.0e0) / (NULLIF(SUM("Dly_DC_LostSlsSnpsht_v"."EstdDmndQty"),0)) "Estd_Lost_Sales__"
    FROM
    "EIS"."Dly_DC_LostSlsSnpsht_v" "Dly_DC_LostSlsSnpsht_v"
    ,"EIS"."GlblCatSum" "GlblCatSum"
    ,"EIS"."ProdtGPI" "ProdtGPI"
    ,"EIS"."GlblCatCoreFcs" "GlblCatCoreFcs"
    
WHERE "Dly_DC_LostSlsSnpsht_v"."CalRunDt" BETWEEN DATE - 1
AND DATE
AND "Dly_DC_LostSlsSnpsht_v"."ProdtCd" = "ProdtGPI"."ProdtCd"
AND "GlblCatCoreFcs"."GlblCatCoreFcsCd" = "ProdtGPI"."GlblCatCoreFcsCd"
AND "GlblCatSum"."GlblCatSumCd" = "GlblCatCoreFcs"."GlblCatSumCd"
GROUP BY "Dly_DC_LostSlsSnpsht_v"."PlntCd", "Dly_DC_LostSlsSnpsht_v"."DivCd", "GlblCatSum"."GlblCatSumCd", (coalesce("GlblCatSum"."GlblCatSumDesc", 'Unknow')), "Dly_DC_LostSlsSnpsht_v"."ProdtCd", "ProdtGPI"."ProdtLglLongNm", "Dly_DC_LostSlsSnpsht_v"."WhlslPrcAmt_USD" 
END
GO


As a complement to Solution 1 you could follow these steps:

1. Find out if your company have documented naming convention rules.

2. Create a valid name to your stored procedure
This is not a joke. A good name saves a lot of explaining later and you also might have to follow certain naming conventions.

3. Identify the variables in your query.
These are the columns you use in the WHERE clause.
This will build up the parameter list of the stored procedure.
Also identify the data type for each variable.

4. Create a stored procedure according to the template in solution 1.

5. Copy your query into the body of the procedure.

6. Replace the columns in the WHERE clause with your input parameters.


Not sure what you want, but you can create a stored procedure using default template provided by SQL

You can add date parameters to this procedure to make it reusable (the date values used with BETWEEN operator)

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO


这篇关于如何将此sql语句转换为storeproc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆