473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Help in stored procedure Using Sql Server 2008

Palyadav
Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[SpLeaveApprovalSaveNew]
  2.     @LeaveApplicationId int,
  3.     @LeaveId int,
  4.     @EmployeeId int,
  5.     @LeaveApprovalFromDate datetime,
  6.     @LeaveApprovalToDate datetime,
  7.     @LeaveApprovalHalfDay bit,
  8.     @LeaveApprovalApplicaitonDate datetime,
  9.     @LeaveApprovalDuration numeric(18,2),
  10.     @LeaveApprovalDate datetime,
  11.     @LeaveApprovalRemark nvarchar(15),
  12.     @LeaveApprovalApplicationBy nvarchar(15)
  13.  
  14. AS
  15.     Declare @Status varchar(10)
  16.     Declare @Date datetime
  17.     Declare @LeaveOnHolidayWeekOff varchar(50)
  18.     Declare @ShiftWeekOff varchar(MAX)
  19.     Declare @ShiftHolidays varchar(MAX)
  20.     Declare @WeekOffDate varchar(max)
  21.     Declare @HolidayDate varchar(max)
  22.     Declare @ShiftName varchar(max)
  23.     Declare @ShiftStartTime varchar(max)
  24.     Declare @ShiftEndTime varchar(max)
  25.     Declare @ShiftLeave varchar(max)
  26.     BEGIN
  27.  
  28.             INSERT INTO LeaveApprovalMaster(LeaveApplicationId,LeaveId,EmployeeId,LeaveApprovalFromDate,LeaveApprovalToDate,LeaveApprovalHalfDay,LeaveApprovalApplicaitonDate, 
  29.                                          LeaveApprovalDuration, LeaveApprovalDate,LeaveApprovalRemark,LeaveApprovalApplicationBy)
  30.                    VALUES(@LeaveApplicationId,@LeaveId,@EmployeeId,@LeaveApprovalFromDate,@LeaveApprovalToDate,@LeaveApprovalHalfDay,
  31.                    @LeaveApprovalApplicaitonDate,@LeaveApprovalDuration,@LeaveApprovalDate,@LeaveApprovalRemark,@LeaveApprovalApplicationBy)
  32.  
  33.            --Update Status in LeaveApplicationMaster  
  34.            UPDATE LeaveApplicationMaster SET LeaveApplicationApprovalFlag = 1 WHERE EmployeeId = @EmployeeId
  35.  
  36.            --Select data from shiftallocation  
  37.            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
  38.  
  39.            --Insert data in EmployeeTempAttendanceDetail
  40.           If Not Exists(Select 1 from EmployeeTempAttendanceDetail where EmployeeId = @EmployeeId and [Date] between CAST (@LeaveApprovalFromDate AS DATETIME) and CAST (@LeaveApprovalToDate AS DATETIME))
  41.             BEGIN                 
  42.                 While(CAST (@LeaveApprovalFromDate AS DATETIME) <= CAST (@LeaveApprovalToDate AS DATETIME))
  43.                 BEGIN
  44.                  --- General Setting if Leave comes on holiday and/or weekoff is true
  45.                SET @LeaveOnHolidayWeekOff = (Select 1 from GeneralSetting where Consider_Leave_If_WeeklyOff_Holiday ='true')
  46.                IF(@LeaveOnHolidayWeekOff > 0)
  47.                     BEGIN
  48.                         SET @Status = 'L' 
  49.                                INSERT INTO EmployeeTempAttendanceDetail(EmployeeId,Date,Status,ShiftName,ShiftStartTime,ShiftEndTime)
  50.                                              VALUES(@EmployeeId,@LeaveApprovalFromDate,@Status,@ShiftName,@ShiftStartTime,@ShiftEndTime) 
  51.                         SET @Status = NULL
  52.                         SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
  53.                     END
  54.               ELSE
  55.                 --- General setting if Leave comes on holiday and/or weekoff is false
  56.                       SELECT @ShiftWeekOff = ShiftAllocationDate  from ShiftAllocation Where ShiftAllocationDate between CAST (@LeaveApprovalFromDate AS DATETIME) and CAST (@LeaveApprovalToDate AS DATETIME) and ShiftId = -1 and ShiftId2 = -1 
  57.                       SELECT @ShiftHolidays = ShiftAllocationDate from ShiftAllocation Where ShiftAllocationDate between CAST (@LeaveApprovalFromDate AS DATETIME) and CAST (@LeaveApprovalToDate AS DATETIME) and ShiftId = -2 and ShiftId2 = -2  
  58.  
  59.  
  60.                     IF(@ShiftWeekOff <= @LeaveApprovalFromDate And @ShiftWeekOff >= @LeaveApprovalToDate)
  61.                     BEGIN
  62.                         SET @Status = 'L' 
  63.                                INSERT INTO EmployeeTempAttendanceDetail(EmployeeId,Date,Status,ShiftName,ShiftStartTime,ShiftEndTime)
  64.                                              VALUES(@EmployeeId,@LeaveApprovalFromDate,@Status,@ShiftName,@ShiftStartTime,@ShiftEndTime) 
  65.                         SET @Status = NULL
  66.                         SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
  67.                     END
  68.                 ELSE IF(@ShiftWeekOff = @LeaveApprovalFromDate)
  69.                         BEGIN
  70.                             SET @Status='WO'
  71.                                 INSERT INTO EmployeeTempAttendanceDetail(EmployeeId,Date,Status,ShiftName,ShiftStartTime,ShiftEndTime)
  72.                                              VALUES(@EmployeeId,@LeaveApprovalFromDate,@Status,@ShiftName,@ShiftStartTime,@ShiftEndTime) 
  73.                             SET @Status = NULL
  74.                             SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
  75.                         END
  76.                   ELSE IF(@ShiftHolidays = @LeaveApprovalFromDate)
  77.                         BEGIN
  78.                             SET @Status='H'
  79.                                  INSERT INTO EmployeeTempAttendanceDetail(EmployeeId,Date,Status,ShiftName,ShiftStartTime,ShiftEndTime)
  80.                                             VALUES(@EmployeeId,@LeaveApprovalFromDate,@Status,@ShiftName,@ShiftStartTime,@ShiftEndTime) 
  81.                             SET @Status = NULL
  82.                             SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
  83.                         END
  84.  
  85.                 END
  86.               END
  87.          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 ?
Dec 14 '11 #1
7 2259
Rabbit
12,516 Expert Mod 8TB
Here is the relevant part of your stored procedure.
Expand|Select|Wrap|Line Numbers
  1. While(CAST (@LeaveApprovalFromDate AS DATETIME) <= CAST (@LeaveApprovalToDate AS DATETIME)) 
  2.     BEGIN
  3.  
  4.     IF(@LeaveOnHolidayWeekOff > 0) 
  5.         SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
  6.  
  7.     IF(@ShiftWeekOff <= @LeaveApprovalFromDate And @ShiftWeekOff >= @LeaveApprovalToDate) 
  8.         SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate) 
  9.     ELSE IF(@ShiftWeekOff = @LeaveApprovalFromDate) 
  10.         SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate) 
  11.     ELSE IF(@ShiftHolidays = @LeaveApprovalFromDate) 
  12.         SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate) 
  13.  
  14.     END 
  15. 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.
Dec 14 '11 #2
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. ?
Dec 15 '11 #3
Rabbit
12,516 Expert Mod 8TB
I have no idea what you need to change. I don't know your business requirements.
Dec 15 '11 #4
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

Expand|Select|Wrap|Line Numbers
  1. While(CAST (@LeaveApprovalFromDate AS DATETIME) <= CAST (@LeaveApprovalToDate AS DATETIME))
  2.  
Now in while loop i check this condition

Expand|Select|Wrap|Line Numbers
  1. SET @LeaveOnHolidayWeekOff = (Select 1 from GeneralSetting where Consider_Leave_If_WeeklyOff_Holiday ='true')
  2.                IF(@LeaveOnHolidayWeekOff > 0)
if this condition is true then i want insert record and in that record set Status='L'

Expand|Select|Wrap|Line Numbers
  1. BEGIN
  2.                         SET @Status = 'L' 
  3.                                INSERT INTO EmployeeTempAttendanceDetail(EmployeeId,Date,Status,ShiftName,ShiftStartTime,ShiftEndTime)
  4.                                              VALUES(@EmployeeId,@LeaveApprovalFromDate,@Status,@ShiftName,@ShiftStartTime,@ShiftEndTime) 
  5.                         SET @Status = NULL
  6.                         SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)
  7.                     END
else part
General setting if Leave comes on holiday and/or weekoff is false

I find ShiftWeekOff and ShiftHolidays date

Expand|Select|Wrap|Line Numbers
  1. SELECT @ShiftWeekOff = ShiftAllocationDate  from ShiftAllocation Where ShiftAllocationDate between CAST (@LeaveApprovalFromDate AS DATETIME) and CAST (@LeaveApprovalToDate AS DATETIME) and ShiftId = -1 and ShiftId2 = -1 
  2.                       SELECT @ShiftHolidays = ShiftAllocationDate from ShiftAllocation Where ShiftAllocationDate between CAST (@LeaveApprovalFromDate AS DATETIME) and CAST (@LeaveApprovalToDate AS DATETIME) and ShiftId = -2 and ShiftId2 = -2  
  3.  
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
Dec 15 '11 #5
Rabbit
12,516 Expert Mod 8TB
Look at these IF conditions:
Expand|Select|Wrap|Line Numbers
  1.     IF(@ShiftWeekOff <= @LeaveApprovalFromDate And @ShiftWeekOff >= @LeaveApprovalToDate)  
  2.         SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)  
  3.     ELSE IF(@ShiftWeekOff = @LeaveApprovalFromDate)  
  4.         SET @LeaveApprovalFromDate = dateadd(day,1,@LeaveApprovalFromDate)  
  5.     ELSE IF(@ShiftHolidays = @LeaveApprovalFromDate)  
  6.         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.
Dec 15 '11 #6
ya its true..I remove this condition(IF(@ShiftWeekOff <= @LeaveApprovalFromDate And @ShiftWeekOff >= @LeaveApprovalToDate) )then also not getting insert 5 entry in EmployeeTempAttendanceDetail.
Dec 16 '11 #7
Rabbit
12,516 Expert Mod 8TB
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.
Dec 16 '11 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

0
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...
0
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...
3
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: ...
3
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...
3
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...
1
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...
1
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
0
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
3
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
0
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...
1
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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....
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.