代码之家 › 专栏 › 技术社区 › Doonie Darkoo

如何在存储过程中将表的ID作为参数传递?

sql-server

Doonie Darkoo · 技术社区 · 5 年前

我创建了一个存储过程,用于获取员工缺勤的天数信息。我将员工ID作为参数值单独传递,以获取员工的详细信息。我想做的是一次获取所有员工的所有信息,而不在参数中传递任何值。我知道这似乎是一个愚蠢的问题,但我现在想不出任何事情,也许工作了这么长时间,我跳过了基本的东西来得到我想要的。

以下是程序:

ALTER PROCEDURE HRM.ProcRptEmployeeAbsentsDays
@empCode bigint, 
@dateFrom date,
@dateTo date
AS
BEGIN
    SELECT ID, CalendarDate, (Status) Absents
    FROM ( 
    SELECT ID, 
    CASE 
        WHEN IsOffDay = 1 AND TimeIn1 IS NULL AND TimeOut1 IS NULL AND PreviousDayTimeIn IS NULL AND PreviousDayTimeOut IS NULL AND NextDayTimeIn IS NULL AND NextDayTimeOut IS NULL THEN 1
        WHEN IsOffDay = 1 AND (PreviousDayTimeIn IS NOT NULL OR PreviousDayTimeOut IS NOT NULL OR NextDayTimeIn IS NOT NULL OR NextDayTimeOut IS NOT NULL) THEN 0
        WHEN IsPublicHoliday = 1 AND TimeIn1 IS NULL AND TimeOut1 IS NULL AND PreviousDayTimeIn IS NULL AND PreviousDayTimeOut IS NULL AND NextDayTimeIn IS NULL AND NextDayTimeOut IS NULL THEN 1
        WHEN IsPublicHoliday = 1 AND (PreviousDayTimeIn IS NOT NULL OR PreviousDayTimeOut IS NOT NULL OR NextDayTimeIn IS NOT NULL OR NextDayTimeOut IS NOT NULL) THEN 0
        WHEN TimeIn1 IS NULL AND TimeOut1 IS NULL THEN 1 ELSE 0 
    END Status,
    CalendarDate
    FROM
    (
      SELECT 
      (SELECT ID FROM HRM.tbl_EmployeeInfo Info WHERE Info.ID = @empCode) ID, CalendarDate, 
        CASE WHEN (SELECT CAST(DOJ AS DATE) FROM HRM.tbl_EmployeeInfo Info WHERE Info.ID = @empCode)<CAST(CalendarDate AS DATE) THEN 
        CASE 
        WHEN DATENAME(dw,CAST(CalendarDate AS DATE)) = (SELECT OffDay1 FROM HRM.tbl_Shift WHERE ID = (SELECT ShiftCode FROM [dbo].[fnGetEmployeeShiftCode](CAST(CalendarDate AS DATE), @empCode))) THEN 1 
        WHEN DATENAME(dw,CAST(CalendarDate AS DATE)) = (SELECT OffDay2 FROM HRM.tbl_Shift WHERE ID = (SELECT ShiftCode FROM [dbo].[fnGetEmployeeShiftCode](CAST(CalendarDate AS DATE), @empCode))) THEN 1 
        ELSE 0 END 
        ELSE 0 END 
        AS IsOffDay,
        CASE WHEN (SELECT CAST(DOJ AS DATE) FROM HRM.tbl_EmployeeInfo Info WHERE Info.ID = @empCode)<CAST(CalendarDate AS DATE) THEN 
        [dbo].[fnIsPublicHoliday](CAST(CalendarDate AS DATE), @empCode) 
        END AS IsPublicHoliday,
        PreviousDayTimeIn    = [dbo].[fnGetTimeInOut](1,DATEADD(DD, -1, CAST(CalendarDate AS DATE)), @empCode, 1),
        TimeIn1    = [dbo].[fnGetTimeInOut](1,CAST(CalendarDate AS DATE), @empCode, 1),
        NextDayTimeIn    = [dbo].[fnGetTimeInOut](1,DATEADD(DD, 1, CAST(CalendarDate AS DATE)), @empCode, 1),

        PreviousDayTimeOut   = ISNULL(([dbo].[fnGetTimeInOut](0,DATEADD(DD, -1, CAST(CalendarDate AS DATE)), @empCode, 2)), ([dbo].[fnGetTimeInOut](0,DATEADD(DD, -1, CAST(CalendarDate AS DATE)), @empCode, 1))),

        TimeOut1   = ISNULL(([dbo].[fnGetTimeInOut](0,CAST(CalendarDate AS DATE), @empCode, 2)), ([dbo].[fnGetTimeInOut](0,CAST(CalendarDate AS DATE), @empCode, 1))),

        NextDayTimeOut   = ISNULL(([dbo].[fnGetTimeInOut](0,DATEADD(DD, 1, CAST(CalendarDate AS DATE)), @empCode, 2)), ([dbo].[fnGetTimeInOut](0,DATEADD(DD, 1, CAST(CalendarDate AS DATE)), @empCode, 1)))

      FROM [CalendarDates] 
      WHERE CAST(CalendarDate AS DATE) BETWEEN '2019-12-01' AND '2019-12-31'
    )T
    )S
    WHERE Status = 1
END
GO

正如你所见,我必须提供 @empCode 为了获得每个员工的信息,我想从如下表格中提供ID HRM.ProcRptEmployeeAbsentsDays (select id from hrm.tbl_employeeinfo), '2019-12-01', '2019-12-31' 但这似乎不是正确的方法,所以它返回了错误。谁能给我指引正确的方向吗?

0 回复 | 直到 5 年前

Sumit Tiwary 5 年前

干得好!

ALTER PROCEDURE HRM.ProcRptEmployeeAbsentsDays
@dateFrom date,
@dateTo date
AS
declare @minempcode int,@maxempcode int
set @minempcode=(select min(id) from hrm.tbl_employeeinfo)
set @maxempcode=(select max(id) from hrm.tbl_employeeinfo)
while(@minempcode<=@maxempcode)
BEGIN
    SELECT ID, CalendarDate, (Status) Absents
    FROM ( 
    SELECT ID, 
    CASE 
        WHEN IsOffDay = 1 AND TimeIn1 IS NULL AND TimeOut1 IS NULL AND PreviousDayTimeIn IS NULL AND PreviousDayTimeOut IS NULL AND NextDayTimeIn IS NULL AND NextDayTimeOut IS NULL THEN 1
        WHEN IsOffDay = 1 AND (PreviousDayTimeIn IS NOT NULL OR PreviousDayTimeOut IS NOT NULL OR NextDayTimeIn IS NOT NULL OR NextDayTimeOut IS NOT NULL) THEN 0
        WHEN IsPublicHoliday = 1 AND TimeIn1 IS NULL AND TimeOut1 IS NULL AND PreviousDayTimeIn IS NULL AND PreviousDayTimeOut IS NULL AND NextDayTimeIn IS NULL AND NextDayTimeOut IS NULL THEN 1
        WHEN IsPublicHoliday = 1 AND (PreviousDayTimeIn IS NOT NULL OR PreviousDayTimeOut IS NOT NULL OR NextDayTimeIn IS NOT NULL OR NextDayTimeOut IS NOT NULL) THEN 0
        WHEN TimeIn1 IS NULL AND TimeOut1 IS NULL THEN 1 ELSE 0 
    END Status,
    CalendarDate
    FROM
    (
      SELECT 
      (SELECT ID FROM HRM.tbl_EmployeeInfo Info WHERE Info.ID = @minempCode) ID, CalendarDate, 
        CASE WHEN (SELECT CAST(DOJ AS DATE) FROM HRM.tbl_EmployeeInfo Info WHERE Info.ID = @minempCode)<CAST(CalendarDate AS DATE) THEN 
        CASE 
        WHEN DATENAME(dw,CAST(CalendarDate AS DATE)) = (SELECT OffDay1 FROM HRM.tbl_Shift WHERE ID = (SELECT ShiftCode FROM [dbo].[fnGetEmployeeShiftCode](CAST(CalendarDate AS DATE), @minempcode))) THEN 1 
        WHEN DATENAME(dw,CAST(CalendarDate AS DATE)) = (SELECT OffDay2 FROM HRM.tbl_Shift WHERE ID = (SELECT ShiftCode FROM [dbo].[fnGetEmployeeShiftCode](CAST(CalendarDate AS DATE), @minempcode))) THEN 1 
        ELSE 0 END 
        ELSE 0 END 
        AS IsOffDay,
        CASE WHEN (SELECT CAST(DOJ AS DATE) FROM HRM.tbl_EmployeeInfo Info WHERE Info.ID = @minempCode)<CAST(CalendarDate AS DATE) THEN 
        [dbo].[fnIsPublicHoliday](CAST(CalendarDate AS DATE), @minempcode) 
        END AS IsPublicHoliday,
        PreviousDayTimeIn    = [dbo].[fnGetTimeInOut](1,DATEADD(DD, -1, CAST(CalendarDate AS DATE)), @minempCode, 1),
        TimeIn1    = [dbo].[fnGetTimeInOut](1,CAST(CalendarDate AS DATE), @minempcode, 1),
        NextDayTimeIn    = [dbo].[fnGetTimeInOut](1,DATEADD(DD, 1, CAST(CalendarDate AS DATE)), @minempcode, 1),

        PreviousDayTimeOut   = ISNULL(([dbo].[fnGetTimeInOut](0,DATEADD(DD, -1, CAST(CalendarDate AS DATE)), @minempcode, 2)), ([dbo].[fnGetTimeInOut](0,DATEADD(DD, -1, CAST(CalendarDate AS DATE)), @minempcode, 1))),

        TimeOut1   = ISNULL(([dbo].[fnGetTimeInOut](0,CAST(CalendarDate AS DATE), @minempcode, 2)), ([dbo].[fnGetTimeInOut](0,CAST(CalendarDate AS DATE), @minempcode, 1))),

        NextDayTimeOut   = ISNULL(([dbo].[fnGetTimeInOut](0,DATEADD(DD, 1, CAST(CalendarDate AS DATE)), @minempcode, 2)), ([dbo].[fnGetTimeInOut](0,DATEADD(DD, 1, CAST(CalendarDate AS DATE)), @minempcode, 1)))

      FROM [CalendarDates] 
      WHERE CAST(CalendarDate AS DATE) BETWEEN '2019-12-01' AND '2019-12-31'
    )T
    )S
    WHERE Status = 1
set @minempcode=@minempcode+1;
END
GO

我添加了两个变量 @minempcode,@maxempcode 在你的电脑上使用while loop min(id) 和 max(id) 从…起 hrm.tbl_employeeinfo .如果您的id不是按顺序排列的,则根据 ID 把它拿走 min 和 max 值,然后在其上循环。希望它能奏效!