Click here to Skip to main content
14,354,963 members
Rate this:
Please Sign up or sign in to vote.
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
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
Comments
Member 7909353 12-Jun-12 3:24am
   
when condition is not satisfied temp table row have all null values.I do not want row having all null values
Rate this:
Please Sign up or sign in to vote.

Solution 2

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.
   
Comments
Member 7909353 12-Jun-12 3:24am
   
when condition is not satisfied temp table row have all null values.I do not want row having all null values
Rate this:
Please Sign up or sign in to vote.

Solution 3

End of the while loop write following
delete  from #UserAttendenceView where PunchDate is null
select * from #UserAttendenceView
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100