在另一个存储过程中写入存储过程 [英] 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

在这个输出中，我的平均值以分钟为单位显示，我想以 HH:MM(hour,minutes) 显示平均值，所以我将其编写为单独的存储过程以获得小时和分钟的平均值，该存储过程是这样的:

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

这里是这样的:平均29:6.插入在第一个存储过程中获取平均值..我想在我的第一个存储过程中显示这个平均值..所以我如何在第一个存储过程中编写这个存储过程，或者有没有其他方法来获得这个平均值在第一个存储过程中

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

推荐答案

你可以把你的程序改成这样的函数

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

使用 ist like

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
 ....

这篇关于在另一个存储过程中写入存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！