CURSOR ERROR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CURSOR ERROR

Author

Topic

umapathy
Starting Member

24 Posts

Posted - 2007-10-03 : 08:22:23

alter procedure USp_UpdateEmployeeRoleValues
(
@InRoleID int,
@nmEmployeeCode int,
@NewEmployeeCode int,
@vcEmpSubGroup VARCHAR(1000)=null,
@bussinessUnit int=null,
@Location int=null,
@vcPayRollAreaCode VARCHAR(100)=null,
@WorkSchedule varchar(100)=null
)
as
begin
declare @bu bit
declare @band bit
declare @loc bit
declare @pa bit
declare @ws bit
declare @strsql nvarchar(3000)
declare @strsql2 nvarchar(4000)
declare @strsql3 nvarchar(2000)
declare @strsql4 nvarchar(3000)
declare @strsql5 nvarchar(3000)
declare @RegisID bigint
declare @tab table (TblTrn_ResignationSLA bigint)

set @strsql3='UPDATE dbo.TblTrn_Employee_Roles set nmEmployeeCode='+convert(varchar(20),@NewEmployeeCode) +'where
TblTrn_Employee_Roles.nmEmployeeCode='+Convert(varchar(9),@nmEmployeeCode)+'
and TblTrn_Employee_Roles.boEnabled=1 and TblTrn_Employee_Roles.inRoleId='+convert(varchar(20),@InRoleID)

select @bu=boBUWise,@band=boBandWise,
@loc=boLocationWise,
@pa=boParollAreaWise,
@ws=boWorkScheduleWise from TblMst_User_Role
where inRoleId=@InRoleID

if @bu=1 --and (@bussinessUnit !=null)
begin
set @strsql3=@strsql3+ ' and TblTrn_Employee_Roles.vcL3_Code= '+convert(varchar(9),@bussinessUnit)
end

if @band=1 --and @vcEmpSubGroup <> ''
begin
set @strsql3=@strsql3+ ' and dbo.TblTrn_Employee_Roles.vcEmpSubGroup='''+ @vcEmpSubGroup+''''
end

if @loc=1
begin
set @strsql3=@strsql3+ ' and dbo.TblTrn_Employee_Roles.vcLoc_Code= '+convert(varchar(9),@Location)
end

if @pa=1 --and @vcPayrollAreaCode <> ''
begin
set @strsql3=@strsql3+ ' and dbo.TblTrn_Employee_Roles.vcPayrollAreaCode='''+ @vcPayrollAreaCode+''''
end

if @ws=1
begin
set @strsql3=@strsql3+ ' and dbo.TblTrn_Employee_Roles.WorkSchedule='''+@WorkSchedule+''''--+convert(varchar(9),@WorkSchedule)
end

exec sp_executesql @strsql3
print @strsql3

declare ResignationSLA_cursor cursor for

select distinct dbo.TblTrn_ResignationSLA.biResignationSLAID from dbo.TblTrn_Employee_Roles
inner join dbo.TblTrn_ResignationSLA on dbo.TblTrn_Employee_Roles.nmEmployeeCode=dbo.TblTrn_ResignationSLA.nmEmployeeCode
inner join dbo.TblMst_Resignation on dbo.TblMst_Resignation.biResignationID=dbo.TblTrn_ResignationSLA.biResignationID
inner join dbo.TblMst_Employee on dbo.TblMst_Resignation.nmEmployeeCode=dbo.TblMst_Employee.nmEmployeeCode
where TblTrn_ResignationSLA.inRoleID=@InRoleID and dbo.TblTrn_Employee_Roles.boEnabled=1 and dbo.TblTrn_Employee_Roles.nmEmployeeCode=@nmEmployeeCode

open ResignationSLA_cursor

FETCH from ResignationSLA_cursor into @RegisID
print 'hai'

while @@FETCH_STATUS = 0
BEGIN

select @bu=boBUWise,@band=boBandWise,
@loc=boLocationWise,
@pa=boParollAreaWise,
@ws=boWorkScheduleWise from TblMst_User_Role
where inRoleId=@InRoleID
print 'hai1'
if @bu=1 --and (@bussinessUnit !=null)
begin
set @strsql2=@strsql2+ ' and TblTrn_Employee_Roles.vcL3_Code= '+convert(varchar(9),@bussinessUnit)
end
if @band=1 --and @vcEmpSubGroup <> ''
begin
set @strsql2=@strsql2+ ' and dbo.TblTrn_Employee_Roles.vcEmpSubGroup='''+ @vcEmpSubGroup+''''
end

if @loc=1
begin
set @strsql2=@strsql2+ ' and dbo.TblTrn_Employee_Roles.vcLoc_Code= '+convert(varchar(9),@Location)
end

if @pa=1 --and @vcPayrollAreaCode <> ''
begin
set @strsql2=@strsql2+ ' and dbo.TblTrn_Employee_Roles.vcPayrollAreaCode='''+ @vcPayrollAreaCode+''''
end

if @ws=1
begin
set @strsql2=@strsql2+ ' and dbo.TblTrn_Employee_Roles.WorkSchedule='''+@WorkSchedule+''''--+convert(varchar(9),@WorkSchedule)
end
exec sp_executesql @strsql2

UPDATE TblTrn_ResignationSLA SET nmEmployeeCode=@NewEmployeeCode WHERE dbo.TblTrn_ResignationSLA.biResignationSLAID=@RegisID and nmEmployeeCode=@nmEmployeeCode

FETCH NEXT from ResignationSLA_cursor into @RegisID

END
close ResignationSLA_cursor
deallocate ResignationSLA_cursor
END

exec USp_UpdateEmployeeRoleValues 0,127797,139416,null,null,null,null,null
this is my parameters
i got output
but one problem
127797 this is old value employee code

139416 this is new employee code

when ever executing first time it is working fine
if i interchange values
curser unable to update values
what is the problem
could you please go through once
please solve this issue

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 08:31:04

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90372
I don't think you need CURSOR at all, as suggested earlier by Kristen.

Rethink and redo code to set-based solution.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources