How to convert this sql statement to storeproc

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

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"

Posted 27-Oct-14 0:29am

Member 11182972

Add a Solution

Comments

Amol_B 27-Oct-14 8:53am

If you got answer to your Question mark it as answer, accept solution and close the question

3 solutions

Solution 3

Hi,

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

SQL

 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

Posted 27-Oct-14 0:50am

Praneet Nadkar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Accepted Answer · 2014-10-27T00:45:00

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.

Amol_B · Accepted Answer · 2014-10-27T00:34:00

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)

XML

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