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