如何在SQL的临时表中插入行值 [英] how to insert row values in temp table in sql

问题描述

我想返回此临时表并在此之后删除.
我有表InOutRegister(punchdate，timein，timeout).
在timein列中的值不为null，但在timeout列中的值可以为null.
输入值为@ Option = 0，@ username =''a''@password =''b''@year =''2012''和@ month ="June''.
但是，当我调试此proc时，它会发出警告.

I want to return this temp table and delete after that.
I have table InOutRegister(punchdate,timein,timeout).
In timein column values are not null but in timeout column values may be null.
And input values are @Option=0,@username=''a'' @password=''b'' @year=''2012'' and @month=''June''.
But when I debug this proc it give warning.

Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.

ALTER PROCEDURE dbo.AttendenceViewSP
	
	(
	@Option int,
	@username varchar(20),
	@password varchar(20),
	@year  varchar(5),
	@month  varchar(12)
	)

AS
begin

declare @firstdate datetime,@empcode int,@DaysInMonth int,@pdate varchar(20)
     if @Option=0
     begin
    CREATE TABLE #UserAttendenceView 
   (
    empcode varchar(15),
    timein varchar(15),
    timeout varchar(15)
    )

                if @month= 'June'
                begin
                    select @firstdate=convert(datetime, '01' + '/' + '06' + '/' + @year)
                end
      select @DaysInMonth=datediff(day, dateadd(day, 1-day(@firstdate), @firstdate), dateadd(month, 1, dateadd(day, 1-day(@firstdate), @firstdate))) 
     select @empcode=EmpCode from MstUser where UserName=@username and UserPass=@password

    while(@DaysInMonth>0)
    begin
    set @pdate=convert(varchar(11),@firstdate,103);
   
    insert into #UserAttendenceView select max(punchdate),max(TimeIN),max(TimeOUT) from InOutRegister where EmpCode=@empcode and PunchDate=@pdate;
     set @firstdate=dateadd(day,1,@firstdate);
   
     set @DaysInMonth= @DaysInMonth-1;
     end
   

     end
	RETURN
	 IF OBJECT_ID('eResourceMgmt.mdf.#UserAttendenceView') IS NOT NULL DROP TABLE #UserAttendenceView 
end

推荐答案

警告是，至少打孔日期之一的值，TimeIN或TimeOUT为null，并且被MAX函数忽略.
如果您想摆脱警告，请
1)从您的数据中删除空值
或
2)在WHERE子句中添加一个过滤器以忽略空值.

What it is warning is that the value of at least one of punchdate, TimeIN or TimeOUT is null, and is being ignored by the MAX function.
If you want to get rid of the warnings then
1) Remove the nulls from your data
or
2) Add a filter to your WHERE clause to ignore null values.

max是一个聚合函数.
该警告表示，在列[TimeOUT]中，您具有诸如"17:00"，NULL和"17:30"之类的值，max()将忽略NULL值，并返回17:30，这可能是您想要的，也可能不是您想要的.

希望这会有所帮助，

Pablo.

max is an aggregate function.
The warning means that in the column [TimeOUT] you have values like ''17:00'', NULL and ''17:30'', max() will ignore the NULL value(s), and return 17:30, which may be or not be what you wanted.

Hope this helps,

Pablo.

while循环的结尾在
之后写

End of the while loop write following

delete  from #UserAttendenceView where PunchDate is null
select * from #UserAttendenceView

这篇关于如何在SQL的临时表中插入行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！