CODEWITHSUNDEEP
sql-serverstored-procedures
user2603688
user2603688

Reputation: 171

write stored procedure inside the other stored procedure

I have a stored procedure like this:

ALTER PROCEDURE [dbo].[Performance] 

     @startdate NVARCHAR(100), 
     @enddate NVARCHAR(100)

AS
BEGIN
     SET NOCOUNT ON;

     SELECT
          l.LocName
         ,v.Vtype
         ,SUM(DATEDIFF(MI, t.Paydate, t.DelDate)) AS TotalDiff
         ,CONVERT(DECIMAL(10, 1), AVG(CONVERT(NUMERIC(18, 2), DATEDIFF(MI, t.Paydate, t.DelDate)))) AS Average
     FROM Transaction_tbl t
     LEFT JOIN VType_tbl v ON t.vtid = v.vtid
     LEFT JOIN Location_tbl l ON t.Locid = l.Locid
     WHERE t.Locid IN
          (
               SELECT t1.Locid
               FROM Transaction_tbl t1
          )
          AND dtime BETWEEN '' + @startdate + '' AND '' + @enddate + ''
          AND status >= 5
     GROUP BY
          v.Vtype
         ,l.LocName
         ,l.Locid
     ORDER BY l.Locid

END

LocName             Vtype       TotalDiff   Average

Fashion Avenue     VIP           84          2.1
Fashion Avenue     Normal        14007       200.1
Address Hotel    Normal          33169        1745.7

in this out put my average is showing in minutes,i want to show average in HH:MM(hour,minutes),so i wrote as separate stored procedure for getting average in hours and minutes,that stored procedure is like this:

ALTER PROCEDURE [dbo].[test] @locid INT
AS
BEGIN
     DECLARE
          @Mns DECIMAL
         ,@dec DECIMAL

     SELECT @dec = AVG(CONVERT(NUMERIC(18, 2), DATEDIFF(MI, t.Paydate, t.DelDate)))
     FROM Transaction_tbl t
     WHERE Locid = @locid;

     SELECT @Mns = @dec % 60;

     SELECT Avearge = 
            CONVERT(VARCHAR(10), (CONVERT(DECIMAL(10), @dec / 60))) + ':'
          + CONVERT(VARCHAR, @Mns)

END

here am getting out put Like this: Avearge 29:6. insted of getting average value in first stored procedure ..i want to show this average values in my first stored procedure..so how i can write this stored procedur inside the first store procedure,or is ther any other way to get this average value in first stored procedure

Upvotes: 0

Views: 254

Answers (2)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

If you look at this line of code:

CONVERT(DECIMAL(10, 1), AVG(CONVERT(NUMERIC(18, 2), DATEDIFF(MI, t.Paydate, t.DelDate)))) AS Average

...don't you think that if you wrap with another function call it will get more ugly and unreadable?

You could write this a bit differently. Let's see.

Step 1 - extract timespan calculation in a separate expression

SELECT
  ...,
  CONVERT(DECIMAL(10, 1), AVG(CONVERT(NUMERIC(18, 2), datecalc.val))) AS Average
FROM 
  Transaction_tbl t
  CROSS APPLY(
    SELECT val = DATEDIFF(MI, t.Paydate, t.DelDate)
  ) datecalc
  ...

This way your minutes are always available to the rest of the query if you need them.

Step 2 - remove CONVERT for AVG()

It's more readable to convert it like this:

SELECT
  ...,
  CONVERT(DECIMAL(10, 1), AVG(datecalc.val * 1.0)) AS Average
FROM 
  Transaction_tbl t
  CROSS APPLY(
    SELECT val = DATEDIFF(MI, t.Paydate, t.DelDate)
  ) datecalc
  ...

Obviously your AVG() has to calculate precisely, and this way you don't loose any precision.

Step 3 - forget the procedure/function

If you don't need the conversion to HH:MM anywhere else in your code but here, don't create any functions to avoid polluting your schema. If you use SQL Server 2008 or higher, TIME type can help you.

Algorithm to get your minutes to HH:MM yould be:

  • get your minutes to DATETIME ('1900-01-01 HH:MI:SS') using DATEADD(MI, Average, 0)
  • get TIME ('HH:MI:SS') from this ugly thing, using CAST(DATEADD(MI, Average, 0) as TIME)
  • get LEFT(CAST(DATEADD(MI, Average, 0) as TIME), 5)

To demonstrate what I mean, here's the code:

;WITH orig AS (
    SELECT id = 1, dt = CAST('2013-07-23 09:01' as datetime), dt2 = CAST('2013-07-23 09:00' as datetime)
    UNION ALL SELECT id = 1, '2013-07-24 01:00', '2013-07-23 12:00'
    UNION ALL SELECT id = 2, '2013-07-23 10:15', '2013-07-23 10:07'
    UNION ALL SELECT id = 2, '2013-07-23 09:10', '2013-07-23 08:00') -- STEP#0: data sample
,avgs AS (
    SELECT
      id,
      val = avg(val1 * 1.0) -- STEP#2: calculating average timespan in minutes
    FROM orig
    CROSS APPLY(
      SELECT val1 = DATEDIFF(MI, dt2, dt) -- STEP#1: calculating timespan in minutes
    ) spancalc
    GROUP BY id)
SELECT
  id,
  str = LEFT(val2, 5) -- STEP#4: getting 'HH:MI' string output
FROM 
  avgs
  CROSS APPLY(
    SELECT 
      val2 = CAST(DATEADD(MI, val, 0) as TIME) -- STEP#3: getting average timespan to TIME format
  ) timecalc

Upvotes: 0

bummi
bummi

Reputation: 27377

You could cahnge your Procedure to a Function like this

Create FUNCTION [dbo].[test] (@dec NUMERIC(18, 2)) RETURNS Varchar(50) 
AS
BEGIN
     DECLARE
          @Mns DECIMAL

     DECLARE @Average  Varchar(50) 

     SELECT @Mns = @dec % 60;

     SELECT @Average = 
            CONVERT(VARCHAR(10), (CONVERT(DECIMAL(10), @dec / 60))) + ':'
          + CONVERT(VARCHAR, @Mns)
     RETURN @Average     

END

And use ist like

 ....    
 SELECT
      l.LocName
     ,v.Vtype
     ,SUM(DATEDIFF(MI, t.Paydate, t.DelDate)) AS TotalDiff
     ,[dbo].[test](
     CONVERT(DECIMAL(10, 1), AVG(CONVERT(NUMERIC(18, 2), DATEDIFF(MI, t.Paydate, t.DelDate))))
     ) AS Average
 FROM Transaction_tbl t
 ....

Upvotes: 1

Related Questions