- 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 ?
7 2259
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 @LeaveApprovalToDate is after @LeaveApprovalFromDate, and if @LeaveOnHolidayWeekOff is 0, and if @ShiftWeekOff is not between @LeaveApprovalToDate and @LeaveApprovalFromDate, and if @ShiftWeekOff is not equal to @LeaveApprovalFromDate, and if @ShiftHolidays is not equal to @LeaveApprovalFromDate, then @LeaveApprovalFromDate never changes and you can never exit your loop.
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. ?
I have no idea what you need to change. I don't know your business requirements.
See I want Data insert into EmployeeTempAttendanceDetail 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 EmployeeTempAttendanceDetail 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
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.
ya its true..I remove this condition(IF(@ShiftWeekOff <= @LeaveApprovalFromDate And @ShiftWeekOff >= @LeaveApprovalToDate) )then also not getting insert 5 entry in EmployeeTempAttendanceDetail.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mike |
last post by:
Hi,
I am trying to insert parameters into a stored procedure using DAAB (see
code at the bottom of this post). I am getting the following error:
Object reference not set to an instance of an...
|
by: Bill |
last post by:
Hi,
I made a stored procedure (name = test (Out param1 char(20)) in Mysql 5.0
with a parameter. I use ASP (i know PHP would be maybe a better choice but
it's ASP) and the value of the parameter...
|
by: michelle |
last post by:
I am trying to get an output value from a stored procedure using
sqlDataSource in asp.net 2.0. But I only get a null value for the
output. Can someone please help?
The sqlDataSource:
...
|
by: nikhilbhavsar |
last post by:
Hi Frnds ,
I want to debug a stored procedure.
It is written in SQL. I am using Developement center for creating
stored procedures.
I built the stored procedure with Debug option and also...
|
by: comp_databases_ms-sqlserver |
last post by:
This post is related to SQL server 2000 and SQL Server 2005 all
editions.
Many of my stored procedures create temporary tables in the code. I
want to find a way to find the query plan for these...
|
by: sheenaa |
last post by:
Hello Members,
I m creating my application forms in ASP.Net 2005 C# using the backend SQL Server 2005.
What i have used on forms :: ?
On my first form i have used some...
|
by: ssrirao |
last post by:
Hi,
I have a table in the database and I want to insert the values into this table by using JavaScript. Does anyone know how to execute the Sql stored procedure using Javascript.
Thanks
|
by: SLauren |
last post by:
Hi,
Can anyone please help me how can i debug a mysql stored procedure using toad software??
Thanks in advance,
Lauren
|
by: QueenKirsty |
last post by:
Setup:
1. I have a SQL Server 2008 db (called VantageTrans) with a linked server (a Progress db called VANTAGE - the back end of our financial system).
2. I have a stored procedure...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |