SQL Stored Procedure and Function

Anything can be programmable with defined syntax and common lib.

 1 ALTER PROCEDURE [dbo].[sp_GetLaborHourPerDayEmployee](@au_Date DATETIME, @au_employeeID VARCHAR(30))
 2     -- Add the parameters for the stored procedure here
 3 AS
 4 BEGIN
 5     -- SET NOCOUNT ON added to prevent extra result sets from
 6     -- interfering with SELECT statements.
 7     SET NOCOUNT ON;
 8     DECLARE @au_ItemDate DATETIME;
 9 
10     SET @au_ItemDate = CONVERT(VARCHAR(10), @au_Date, 120);
11     
12     SELECT SUM(ISNULL(T1.Hours,0)) as Hours
13     FROM [dbo].[LBS_Maintenance] as T0
14     left join [dbo].[LBS_LaborHour] as T1
15     on T0.MaintenanceID = T1.MaintenanceID     
16     where T0.IsFlag = 0 
17     and CONVERT(VARCHAR(10),T0.ItemDate,120) = @au_ItemDate
18     and T0.EmployeeID = @au_employeeID
19     --and (T2.CurrentStatus = 'Active' or (T2.CurrentStatus = 'Suspend' and T2.SuspendWhat = 'Billing'))            
20 END

 

 

 

 1 USE [OES_LBS]
 2 GO
 3 /****** Object:  UserDefinedFunction [dbo].[fn_GetLaborHourPerDayEmployee]    Script Date: 08/07/2013 16:17:00 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 -- =============================================
 9 -- Author:        <Author,,Name>
10 -- Create date: <Create Date,,>
11 -- Description:    <Description,,>
12 -- =============================================
13 ALTER FUNCTION [dbo].[fn_GetLaborHourPerDayEmployee](@au_Date DATETIME, @au_employeeID VARCHAR(30))
14 RETURNS NUMERIC(5,2)  
15 AS
16 BEGIN
17     DECLARE @ret NUMERIC(5,2)
18     SET @ret = 0
19     SELECT @ret = A0.Hours
20     FROM
21     (
22         SELECT SUM(ISNULL(T1.Hours,0)) as Hours
23         FROM [dbo].[LBS_Maintenance] as T0
24         left join [dbo].[LBS_LaborHour] as T1
25         on T0.MaintenanceID = T1.MaintenanceID     
26         where IsFlag = 0 
27         and T0.ItemDate = @au_Date
28         and T0.EmployeeID = @au_employeeID
29         --and (T2.CurrentStatus = 'Active' or (T2.CurrentStatus = 'Suspend' and T2.SuspendWhat = 'Billing'))        
30         --and T1.Hours != 0
31     )as A0
32 RETURN(@ret)
33 END

 

 

 

转载于:https://www.cnblogs.com/iiiDragon/p/3243386.html