SQL Stored Procedure and Function

Anything can be programmable with defined syntax and common lib.

ALTER PROCEDURE [dbo].[sp_GetLaborHourPerDayEmployee](@au_Date DATETIME, @au_employeeID VARCHAR(30))
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @au_ItemDate DATETIME;

    SET @au_ItemDate = CONVERT(VARCHAR(10), @au_Date, 120);
    
    SELECT SUM(ISNULL(T1.Hours,0)) as Hours
    FROM [dbo].[LBS_Maintenance] as T0
    left join [dbo].[LBS_LaborHour] as T1
    on T0.MaintenanceID = T1.MaintenanceID     
    where T0.IsFlag = 0 
    and CONVERT(VARCHAR(10),T0.ItemDate,120) = @au_ItemDate
    and T0.EmployeeID = @au_employeeID
    --and (T2.CurrentStatus = 'Active' or (T2.CurrentStatus = 'Suspend' and T2.SuspendWhat = 'Billing'))            
END

 

 

 

USE [OES_LBS]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_GetLaborHourPerDayEmployee]    Script Date: 08/07/2013 16:17:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[fn_GetLaborHourPerDayEmployee](@au_Date DATETIME, @au_employeeID VARCHAR(30))
RETURNS NUMERIC(5,2)  
AS
BEGIN
    DECLARE @ret NUMERIC(5,2)
    SET @ret = 0
    SELECT @ret = A0.Hours
    FROM
    (
        SELECT SUM(ISNULL(T1.Hours,0)) as Hours
        FROM [dbo].[LBS_Maintenance] as T0
        left join [dbo].[LBS_LaborHour] as T1
        on T0.MaintenanceID = T1.MaintenanceID     
        where IsFlag = 0 
        and T0.ItemDate = @au_Date
        and T0.EmployeeID = @au_employeeID
        --and (T2.CurrentStatus = 'Active' or (T2.CurrentStatus = 'Suspend' and T2.SuspendWhat = 'Billing'))        
        --and T1.Hours != 0
    )as A0
RETURN(@ret)
END