Sql server存储过程 [英] Sql server stored procedure

问题描述

Emp_No，In_Time_Punch数据类型datetime，Out_Time_Punch类型日期时间，小时，状态，备注，W_Days，Att_Date，正常时间，正常退出时间

我想要存储过程

显示工作小时数Hrs = Out_Time_Punch-In_Time_Punch

，数据类型为datetime

我还希望存储过程显示备注，如果让In_Time_Punch为空或空为FORGOT IN TIME PUNCH



我尝试了什么：



我试过搜索google我有试过很多公式却失败了

创建程序usp_EmployeeAttendance

（

@Emp_No NVARCHAR（50），

@In_Time_Punch datetime，

@Out_Time_Punch datetime，

@Remarks NVARCHAR（50），@ Status varchar（10），

@Att_Date date，@ Hrs nchar（10），@ W_Days int

）





AS



BEGIN





IF（@In_Time_Punch> '7：25：00'）

BEGIN

set @Remarks ='LC'

END

ELSE IF（@In_Time_Punch =''）

BEGIN

set @Remarks ='IN PUNCH FORGET'

IF（@In_Time_Punch< '16：35：00'）

BEGIN

设置@Remarks ='EL'

结束

ELSE IF（@Out_Time_Punch =''）

BEGIN

设置@Remarks ='OUT PUNCH FORGET'

END

END

END

解决方案

尝试使用ISNULL： ISNULL（Transact-SQL） [ ^ ]

  ELSE   IF （ISNULL（ @ In_Time_Punch ，' '）= ' '）

但是......你需要查看那个序列。

At你有这个时刻

  ELSE   IF  （ @ In_Time_Punch  = ' '）
  BEGIN  
  set   @备注 = '  IN PUNCH FORGET' 
  IF （ @ In_Time_Punch < '  16:35:00'）
  BEGIN  
  set  < span class =code-sdkkeyword> @ Remarks  = '  EL' 
  END  
  ELSE   IF （< span class =code-sdkkeyword> @ Out_Time_Punch  = ' '）
  BEGIN  
  set   @备注 = '  OUT PUNCH FORGET' 
  END  
  END  

这意味着测试：

  IF （ @ In_Time_Punch < '  16:35:00'）
 

只有在保证失败时才会被检查，因为它必须为空才能获得进入那个区块。

我要做的是先检查我的值，然后对t进行计算嘿是合理的：

如果（时间不合适）
备注=FORGOT
其他
 ... 

Emp_No,In_Time_Punch data type datetime, Out_Time_Punch type datetime,Hrs,Status,Remarks,W_Days,Att_Date,Normal in Time,Normal Out Time
I want stored procedure
to show total hours worked Hrs=Out_Time_Punch-In_Time_Punch
with datatype datetime
also I want stored procedure to show remarks if lets In_Time_Punch is empty or null that FORGOT IN TIME PUNCH

What I have tried:

I tried searching google I have tried many formulas but failed
CREATE PROCEDURE usp_EmployeeAttendance
(
@Emp_No NVARCHAR(50),
@In_Time_Punch datetime,
@Out_Time_Punch datetime,
@Remarks NVARCHAR(50),@Status varchar(10),
@Att_Date date,@Hrs nchar(10),@W_Days int
)


AS

BEGIN


IF(@In_Time_Punch >'7:25:00')
BEGIN
set @Remarks = 'LC'
END
ELSE IF(@In_Time_Punch ='')
BEGIN
set @Remarks = 'IN PUNCH FORGET'
IF (@In_Time_Punch <'16:35:00')
BEGIN
set @Remarks = 'EL'
END
ELSE IF(@Out_Time_Punch ='')
BEGIN
set @Remarks = 'OUT PUNCH FORGET'
END
END
END

解决方案

Try using ISNULL: ISNULL (Transact-SQL)[^]

ELSE IF(ISNULL(@In_Time_Punch, '') = '')

But...you need to look at that sequence.
At the moment you have

ELSE IF(@In_Time_Punch ='')
    BEGIN
    set @Remarks = 'IN PUNCH FORGET'
    IF (@In_Time_Punch <'16:35:00')
        BEGIN
        set @Remarks = 'EL'
        END
    ELSE IF(@Out_Time_Punch ='')
        BEGIN
        set @Remarks = 'OUT PUNCH FORGET'
        END
    END

Which means that the test:

IF (@In_Time_Punch <'16:35:00')

is only checked if it is guaranteed to fail since it has to be empty to get into that block.
What I would do is check my values first, then do the calculation on if they are reasonable:

if (in time is not OK)
   Remarks = "FORGOT"
else
   ...

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