Help in stored procedure Using Sql Server 2008

[Palyadav]

ALTER PROCEDURE [dbo].[SpLeaveApprovalSaveNew]
	@LeaveApplicationId int,
	@LeaveId int,
	@EmployeeId int,
	@LeaveApprovalFromDate datetime,
	@LeaveApprovalToDate datetime,
	@LeaveApprovalHalfDay bit,
	@LeaveApprovalApplicaitonDate datetime,
	@LeaveApprovalDuration numeric(18,2),
	@LeaveApprovalDate datetime,
	@LeaveApprovalRemark nvarchar(15),
	@LeaveApprovalApplicationBy nvarchar(15)
	
AS
	Declare @Status varchar(10)
	Declare @Date datetime
	Declare @LeaveOnHolidayWeekOff varchar(50)
	Declare @ShiftWeekOff varchar(MAX)
	Declare @ShiftHolidays varchar(MAX)
	Declare @WeekOffDate varchar(max)
	Declare @HolidayDate varchar(max)
	Declare @ShiftName varchar(max)
    Declare @ShiftStartTime varchar(max)
    Declare @ShiftEndTime varchar(max)
    Declare @ShiftLeave varchar(max)
    BEGIN

			INSERT INTO LeaveApprovalMaster(LeaveApplicationId,LeaveId,EmployeeId,LeaveApprovalFromDate,LeaveApprovalToDate,LeaveApprovalHalfDay,LeaveApprovalApplicaitonDate, 
	                                     LeaveApprovalDuration, LeaveApprovalDate,LeaveApprovalRemark,LeaveApprovalApplicationBy)
	               VALUES(@LeaveApplicationId,@LeaveId,@EmployeeId,@LeaveApprovalFromDate,@LeaveApprovalToDate,@LeaveApprovalHalfDay,
	               @LeaveApprovalApplicaitonDate,@LeaveApprovalDuration,@LeaveApprovalDate,@LeaveApprovalRemark,@LeaveApprovalApplicationBy)
	               
	       --Update Status in LeaveApplicationMaster  
	       UPDATE LeaveApplicationMaster SET LeaveApplicationApprovalFlag = 1 WHERE EmployeeId = @EmployeeId
	       
	       --Select data from shiftallocation  
	       SELECT @ShiftName=M.ShiftName,@ShiftStartTime=M.ShiftStartTime,@ShiftEndTime=M.ShiftEndTime from LeaveApprovalMaster L INNER JOIN ShiftAllocation S ON L.EmployeeId=S.EmployeeId INNER JOIN ShiftMaster M ON S.ShiftId = M.ShiftId
	       
	       --Insert data in EmployeeTempAttendanceDetail
	      If Not Exists(Select 1 from EmployeeTempAttendanceDetail where EmployeeId = @EmployeeId and [Date] between CAST (@LeaveApprovalFromDate AS DATETIME) and CAST (@LeaveApprovalToDate AS DATETIME))
			BEGIN                 
				While(CAST (@LeaveApprovalFromDate AS DATETIME) <= CAST (@LeaveApprovalToDate AS DATETIME))
				BEGIN
				 --- General Setting if Leave comes on holiday and/or weekoff is true
	           SET @LeaveOnHolidayWeekOff = (Select 1 from GeneralSetting where Consider_Leave_If_WeeklyOff_Holiday ='true')
	           IF(@LeaveOnHolidayWeekOff > 0)
					BEGIN
						SET @Status = 'L' 
	   						INSERT INTO EmployeeTempAttendanceDetail(EmployeeId,Date,Status,ShiftName,ShiftStartTime,ShiftEndTime)
	                                         VALUES(@EmployeeId,@LeaveApprovalFromDate,@Status,@ShiftName,@ShiftStartTime,@ShiftEndTime) 
						SET @Status = NULL
						SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
					END
			  ELSE
				--- General setting if Leave comes on holiday and/or weekoff is false
					  SELECT @ShiftWeekOff = ShiftAllocationDate  from ShiftAllocation Where ShiftAllocationDate between CAST (@LeaveApprovalFromDate AS DATETIME) and CAST (@LeaveApprovalToDate AS DATETIME) and ShiftId = -1 and ShiftId2 = -1 
					  SELECT @ShiftHolidays = ShiftAllocationDate from ShiftAllocation Where ShiftAllocationDate between CAST (@LeaveApprovalFromDate AS DATETIME) and CAST (@LeaveApprovalToDate AS DATETIME) and ShiftId = -2 and ShiftId2 = -2  
			   		
			      
					IF(@ShiftWeekOff <= @LeaveApprovalFromDate And @ShiftWeekOff >= @LeaveApprovalToDate)
					BEGIN
						SET @Status = 'L' 
	   						INSERT INTO EmployeeTempAttendanceDetail(EmployeeId,Date,Status,ShiftName,ShiftStartTime,ShiftEndTime)
	                                         VALUES(@EmployeeId,@LeaveApprovalFromDate,@Status,@ShiftName,@ShiftStartTime,@ShiftEndTime) 
						SET @Status = NULL
						SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
					END
				ELSE IF(@ShiftWeekOff = @LeaveApprovalFromDate)
						BEGIN
							SET @Status='WO'
 				               INSERT INTO EmployeeTempAttendanceDetail(EmployeeId,Date,Status,ShiftName,ShiftStartTime,ShiftEndTime)
	                                         VALUES(@EmployeeId,@LeaveApprovalFromDate,@Status,@ShiftName,@ShiftStartTime,@ShiftEndTime) 
					        SET @Status = NULL
				            SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
						END
				  ELSE IF(@ShiftHolidays = @LeaveApprovalFromDate)
						BEGIN
							SET @Status='H'
 								INSERT INTO EmployeeTempAttendanceDetail(EmployeeId,Date,Status,ShiftName,ShiftStartTime,ShiftEndTime)
	                                        VALUES(@EmployeeId,@LeaveApprovalFromDate,@Status,@ShiftName,@ShiftStartTime,@ShiftEndTime) 
							SET @Status = NULL
							SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
						END
					
				END
	          END
	     END

In this code i m getting interrupt this while loop goes to infinite.my this stored procedure are correct but i think i mistake in syntax.so what is the solution of this stored procedure ?

[Rabbit]

Here is the relevant part of your stored procedure.

While(CAST (@LeaveApprovalFromDate AS DATETIME) <= CAST (@LeaveApprovalToDate AS DATETIME)) 
	BEGIN

	IF(@LeaveOnHolidayWeekOff > 0) 
		SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)

	IF(@ShiftWeekOff <= @LeaveApprovalFromDate And @ShiftWeekOff >= @LeaveApprovalToDate) 
		SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate) 
	ELSE IF(@ShiftWeekOff = @LeaveApprovalFromDate) 
		SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate) 
	ELSE IF(@ShiftHolidays = @LeaveApprovalFromDate) 
		SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate) 

	END 
END

As you can see, if @LeaveApprovalT oDate is after @LeaveApprovalF romDate, and if @LeaveOnHoliday WeekOff is 0, and if @ShiftWeekOff is not between @LeaveApprovalT oDate and @LeaveApprovalF romDate, and if @ShiftWeekOff is not equal to @LeaveApprovalF romDate, and if @ShiftHolidays is not equal to @LeaveApprovalF romDate, then @LeaveApprovalF romDate never changes and you can never exit your loop.

[Palyadav]

So how can i solved this problem ?
I want insert data between from date to to date
with that all condition.
what type of change i need this stored procedure. ?

[Rabbit]

I have no idea what you need to change. I don't know your business requirements.

[Palyadav]

See I want Data insert into EmployeeTempAtt endanceDetail table.
One thing one employee had application for leave fromdate 16/12/2012 to todate 20/12/2012.so in that case i want record in EmployeeTempAtt endanceDetail from 16/12/2012 to 20/12/2012. i.e.5 record should be insert.
so below while loop check condition for 5 time insert record

While(CAST (@LeaveApprovalFromDate AS DATETIME) <= CAST (@LeaveApprovalToDate AS DATETIME))

Now in while loop i check this condition

SET @LeaveOnHolidayWeekOff = (Select 1 from GeneralSetting where Consider_Leave_If_WeeklyOff_Holiday ='true')
               IF(@LeaveOnHolidayWeekOff > 0)

if this condition is true then i want insert record and in that record set Status='L'

BEGIN
                        SET @Status = 'L' 
                               INSERT INTO EmployeeTempAttendanceDetail(EmployeeId,Date,Status,ShiftName,ShiftStartTime,ShiftEndTime)
                                             VALUES(@EmployeeId,@LeaveApprovalFromDate,@Status,@ShiftName,@ShiftStartTime,@ShiftEndTime) 
                        SET @Status = NULL
                        SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
                    END

else part
General setting if Leave comes on holiday and/or weekoff is false

I find ShiftWeekOff and ShiftHolidays date

SELECT @ShiftWeekOff = ShiftAllocationDate  from ShiftAllocation Where ShiftAllocationDate between CAST (@LeaveApprovalFromDate AS DATETIME) and CAST (@LeaveApprovalToDate AS DATETIME) and ShiftId = -1 and ShiftId2 = -1 
                      SELECT @ShiftHolidays = ShiftAllocationDate from ShiftAllocation Where ShiftAllocationDate between CAST (@LeaveApprovalFromDate AS DATETIME) and CAST (@LeaveApprovalToDate AS DATETIME) and ShiftId = -2 and ShiftId2 = -2

then i check date this date is WeekOff Or Holiday
if that date is weekoff then insert status = 'WO' and
date is equal to holiday then insert status='H'


i want this type of output:-

16/12/2011 - Insert Record with Status='L'
17/12/2011 - Insert Record with Status='L'
18/12/2011 - Insert Record with Status='WO'
19/12/2011 - Insert Record with Status='H' (Monday is holiday)
20/12/2011 - Insert Record with Status='L'

Thanks in advance

[Rabbit]

Look at these IF conditions:

    IF(@ShiftWeekOff <= @LeaveApprovalFromDate And @ShiftWeekOff >= @LeaveApprovalToDate)  
        SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)  
    ELSE IF(@ShiftWeekOff = @LeaveApprovalFromDate)  
        SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)  
    ELSE IF(@ShiftHolidays = @LeaveApprovalFromDate)  
        SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)

Your first one can never be satisfied because the shift week off can't be before the from date and after the to date at the same time. It's an impossible condition.

[Palyadav]

ya its true..I remove this condition(IF(@S hiftWeekOff <= @LeaveApprovalF romDate And @ShiftWeekOff >= @LeaveApprovalT oDate) )then also not getting insert 5 entry in EmployeeTempAtt endanceDetail.

[Rabbit]

That's not the only problem with the conditions. Even if you remove that condition, your remaining conditions only cover the days where the from date is the same as the shift week off and shift holidays. You have no condition covering the rest of your business requirements.