4 Replies - 511 Views - Last Post: 13 August 2016 - 12:01 PM

#1 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 148
  • View blog
  • Posts: 1,043
  • Joined: 17-November 10

Stored Proc Disc

Posted 12 August 2016 - 09:08 PM

Hi, I wanted to run this by you all and see if this code would be something you would agree upon or simply reject it.
I did what the practical question asked, but having so many doubts about it that forced me to post this simple proc here to get some "what you should have"s and "what you shouldn't have"s.

Also, in my last post, one questioned, why I don't use truncate instead of drop. I couldn't answer that time because I was unsure about the theory side of things and only know that you must be damn well sure about truncating something because it would wipe out the table or database from the existence. Drop however can take conditions.

That leads me to believe that when all else fails and someone just want to be rid of the db, you truncate it.

Anyways, coming back to the code.

--IN YOUR TSQL2012 DATABASE CREATE A STORED PROCEDURE THAT 
--ACCEPTS THE PARAMETERS OF START DATE, END DATE, EMPLOYEE NAME OR EMP_ID
--THIS PROCEDURE SHOULD SHOW THE TOTAL SUM OF ORDERS THE EMPLOYEE GENERATES 
--IN THAT PERIOD.

USE TSQL2012
GO
--IF EXISTS DROP THE PROCEDURE
IF OBJECT_ID(N'dbo.SUM_OF_ORDERS') IS NOT NULL
DROP PROCEDURE dbo.SUM_OF_ORDERS
GO
--CREATE THE PROCEDURE
CREATE PROCEDURE dbo.SUM_OF_ORDERS
AS
DECLARE
@EMP_NUM INT = NULL,
@START_DATE DATE = NULL,
@END_DATE DATE = NULL

SET @EMP_NUM = 3;
SET @START_DATE = (SELECT TOP 1 MIN(O.ORDERDATE) FROM SALES.ORDERS AS O GROUP BY O.EMPID ORDER BY O.EMPID);
SET @END_DATE = (SELECT TOP 1 MAX(O.ORDERDATE) FROM SALES.ORDERS AS O GROUP BY O.EMPID ORDER BY O.EMPID);

SET NOCOUNT ON
SELECT	O.EMPID AS EMPLOYEE_NUMBER, 
		@START_DATE AS S_DATE, 
		@END_DATE AS E_DATE, 
		SUM(OD.UNITPRICE * OD.QTY) AS TOTAL_BUSINESS FROM 
		SALES.ORDERS AS O 
		INNER JOIN
		SALES.ORDERDETAILS AS OD
		ON
		OD.ORDERID = O.ORDERID
		WHERE O.EMPID = @EMP_NUM
		GROUP BY O.EMPID
		ORDER BY O.EMPID ASC



I would appreciate any review.

Is This A Good Question/Topic? 0
  • +

Replies To: Stored Proc Disc

#2 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,569
  • Joined: 12-December 12

Re: Stored Proc Disc

Posted 13 August 2016 - 12:56 AM

You need to provide a clear question. What are you asking us to review or comment upon? Otherwise, should this topic be merged with your previous one? Is it a continuation?
Was This Post Helpful? 0
  • +
  • -

#3 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 148
  • View blog
  • Posts: 1,043
  • Joined: 17-November 10

Re: Stored Proc Disc

Posted 13 August 2016 - 02:11 AM

View Postandrewsw, on 13 August 2016 - 12:56 AM, said:

You need to provide a clear question. What are you asking us to review or comment upon? Otherwise, should this topic be merged with your previous one? Is it a continuation?


This one is not a continuation of the previous one.

I'll try to be as clear as possible here:
-Would you recommend any better way to write this same procedure?
-It would be nice if you can tell me what I should've done and
-what I shouldn't have done while writing this procedure.

This post has been edited by Nitewalkr: 13 August 2016 - 02:27 AM

Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: Stored Proc Disc

Posted 13 August 2016 - 06:55 AM

Well, the top 1 here doesn't make much sense.
SET @START_DATE = (SELECT TOP 1 MIN(O.ORDERDATE) FROM SALES.ORDERS AS O GROUP BY O.EMPID ORDER BY O.EMPID);



Neither does the order by. Wait, aren't you look for a specfic empid here?
Perhaps:
select @START_DATE = MIN(ORDERDATE), @END_DATE = MAX(ORDERDATE)
    FROM SALES.ORDERS
    WHERE EMPID = @EMP_NUM




Though, given your final query, I recon you have too many variables floating about.

I'm just going to have to offer this one up:
SELECT
        O.EMPID AS EMPLOYEE_NUMBER, 
        MIN(O.ORDERDATE) AS S_DATE, 
        MAX(O.ORDERDATE) AS E_DATE, 
        SUM(OD.UNITPRICE * OD.QTY) AS TOTAL_BUSINESS
    FROM SALES.ORDERS AS O 
        INNER JOIN SALES.ORDERDETAILS AS OD
            ON OD.ORDERID = O.ORDERID
    WHERE O.EMPID = @EMP_NUM
    GROUP BY O.EMPID
    -- pointless ORDER BY O.EMPID ASC


Was This Post Helpful? 0
  • +
  • -

#5 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 148
  • View blog
  • Posts: 1,043
  • Joined: 17-November 10

Re: Stored Proc Disc

Posted 13 August 2016 - 12:01 PM

TOP 1 in set because I was looking for only one value that matches the Emp ID.

You mean to say that I should've just assigned the variables to the columns instead of looking for a specific value from the specific column?
If my understanding makes any sense.

As such

AS
DECLARE

@EMP_NUM INT = NULL,
@START_DATE DATE = NULL,
@END_DATE DATE = NULL

 

--SET @EMP_NUM = 3;
--SET @START_DATE = (SELECT TOP 1 MIN(O.ORDERDATE) FROM SALES.ORDERS AS O GROUP BY O.EMPID ORDER BY O.EMPID);
--SET @END_DATE = (SELECT TOP 1 MAX(O.ORDERDATE) FROM SALES.ORDERS AS O GROUP BY O.EMPID ORDER BY O.EMPID);

 

SET NOCOUNT ON

SELECT  O.EMPID AS EMPLOYEE_NUMBER,
        @START_DATE = MIN(O.ORDERDATE) AS S_DATE,
        @END_DATE = MAX(O.ORDERDATE) AS E_DATE,
        SUM(OD.UNITPRICE * OD.QTY) AS TOTAL_BUSINESS FROM
        SALES.ORDERS AS O
        INNER JOIN
        SALES.ORDERDETAILS AS OD
        ON
        OD.ORDERID = O.ORDERID
        WHERE O.EMPID = @EMP_NUM
        GROUP BY O.EMPID




EDIT: Actually wait, I don't have to use aggregates if I am keying in the dates do I?

SELECT  O.EMPID AS EMPLOYEE_NUMBER,
        @START_DATE = O.ORDERDATE AS S_DATE,
        @END_DATE = O.ORDERDATE AS E_DATE,
        SUM(OD.UNITPRICE * OD.QTY) AS TOTAL_BUSINESS FROM
        SALES.ORDERS AS O
        INNER JOIN
        SALES.ORDERDETAILS AS OD
        ON
        OD.ORDERID = O.ORDERID
        WHERE O.EMPID = @EMP_NUM
        GROUP BY O.EMPID



This post has been edited by Nitewalkr: 13 August 2016 - 12:04 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1