June 26, 2009 at 12:14 am
Hi Expertz
While creating a stored procedure im getting error
Msg 141, Level 15, State 1, Procedure shiftwise_attendance_details_new, Line 195
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
for this part of the TSQL
select distinct @emp_id=emp.employee_id,@User_Id=emp.User_Id,@user_name=amt.user_name,
@s_date=leave.leave_start_dt ,
l@e_date=eave.leave_end_dt ,
@item_code=(case when leave.half_day ='Y' then leave_type+'-H/D' else leave_type end)
,@half_day=leave.half_day
from employee_tbl emp,ats_map_tbl amt,tl_leave_tbl leave
where employee_status='A'
and emp.employee_id=amt.employee_id
and emp.employee_id=leave.employee_id
and amt.organization_id=@organization_id
and (leave.status='A' )
and ( ( ( leave.leave_start_dt between @start_date and @end_date)
and (leave.leave_end_dt between @start_date and @end_date) )
or leave.leave_start_dt >= @start_date and leave.leave_start_dt <=@end_date
or leave.leave_end_dt >= @start_date and leave.leave_end_dt <= @end_date
or leave.leave_start_dt <= @start_date and leave.leave_end_dt >= @end_date)
while @@rowcount > 0
begin
if @s_date < @start_date
begin
select @s_date = @start_date
end
if @e_date > @end_date
begin
select @e_date = @end_date
end
while (@s_date <= @e_date)
begin
select @day = datepart(weekday,@s_date)
If @day != 1
begin
If @day != 7
begin
select @count = count(*) from #temp1 where ATSDATE = @s_date and emp_id=@emp_id
If @count > 0
begin
update #temp1 set item_code=@item_code,half_day=@half_day where ATSDATE = @s_date and emp_id=@emp_id
end
If @count < 1
begin
select @exp_punchin_time=asm.shiftin_time,@exp_punchout_time=asm.shiftout_time from ATS_shift_master asm, ATS_shift_empl ase
where ase.employee_id=@emp_id and ase.shift_id=asm.shift_id
and (ase.effective_date) = (select max(effective_date) from ATS_shift_empl where employee_id=@emp_id and effective_date <= @s_date) insert into #temp1 values
(
@emp_id, @User_Id, @user_name, @s_date,
@exp_punchin_time, '00:00:00',
@exp_punchout_time, '00:00:00',
( case @item_code when 'REG' then 'null' else '00:00:00' end ),
'00:00:00', @item_code,@half_day
)
end
end
end
select @s_date = dateadd(day,1,@s_date)
end
end
[/code]
Please help.........
Tanx 😀
June 26, 2009 at 12:24 am
check all the variable names you assign.
l@e_date may be treated as a column alias.
June 26, 2009 at 12:42 am
.
Tanx 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply