Can anyone help me on cursors - 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 2005 Forums

Transact-SQL (2005)

Can anyone help me on cursors

Author

Topic

K_P
Starting Member

10 Posts

Posted - 2008-04-08 : 14:16:39

Could not complete cursor operation because the table schema changed after the cursor was declared.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 14:19:18

What did you (or someone else) do to the table for which the cursor was declared?

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

K_P
Starting Member

10 Posts

Posted - 2008-04-08 : 14:26:49

Hi Peso,

I called a procedure

Alter PROCEDURE ChangeEmp
As
Declare @employeeno sysname
declare @sql sysname
declare @ELEN Int
declare @newValue sysname

--newProc 'Employee','10000','010000'
--newProc 'Employee','10049','010049'

Declare ChangeEmp Cursor for
select EmployeeNo from Employee where employeeNo like '01%'
open ChangeEmp
Fetch next from ChangeEmp into @employeeno
while (@@Fetch_Status=0)
begin
set @ELEN = len(@employeeno)
set @newValue = RIGHT( @employeeno, @ELEN -1 )

exec newProc 'Employee', @newValue, @employeeno
PRINT 'MED_EmployeeNo ---' + @employeeno + ' Changed to ' + @newValue
Fetch next from ChangeEmp into @employeeno
end

close ChangeEmp
deallocate ChangeEmp
return

Wherein newProc is another procedure
ALTER PROCEDURE NEWProc
(
@tablename sysname = null,
@nvalue varchar(10) = null,
@ovalue varchar(10) = null
)
As
Declare @employeeno sysname
declare @sql sysname

ALTER TABLE IHSamples DISABLE TRIGGER ALL
ALTER TABLE IHNoiseSamples DISABLE TRIGGER ALL
ALTER TABLE EmployeeLetters DISABLE TRIGGER ALL

ALTER TABLE IHSamples NOCHECK CONSTRAINT FK_0000137
ALTER TABLE IHNoiseSamples NOCHECK CONSTRAINT FK_0000138
ALTER TABLE EmployeeLetters NOCHECK CONSTRAINT FK_0000231

UPDATE Employee SET EmployeeNo ='''+@nvalue+ ''' WHERE EmployeeNo = '''+ @ovalue +'''
UPDATE IHSamples SET EmployeeNo ='''+@nvalue+ ''' WHERE EmployeeNo = '''+ @ovalue +'''
UPDATE IHNoiseSamples SET EmployeeNo ='''+@nvalue+ ''' WHERE EmployeeNo = '''+ @ovalue +'''
UPDATE EmployeeLetters SET EmployeeNo = '''+@nvalue+ ''' WHERE EmployeeNo = '''+ @ovalue +'''

ALTER TABLE IHNoiseSamples CHECK CONSTRAINT FK_0000138
ALTER TABLE IHSamples CHECK CONSTRAINT FK_0000137
ALTER TABLE EmployeeLetters CHECK CONSTRAINT FK_0000231

ALTER TABLE IHSamples ENABLE TRIGGER ALL
ALTER TABLE IHNoiseSamples ENABLE TRIGGER ALL
ALTER TABLE EmployeeLetters ENABLE TRIGGER ALL

PRINT 'All tables EmployeeLetters, IHNoiseSamples, IHSamples EmployeeNo Updated ---'

exec MED_CHANGEEMPNO1 @tablename , 'EMPLOYEENO' , @nvalue, @ovalue

and MED_CHANGEEMPNO1 is another proc to check the employeeno constraint in the tables of database.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 04:24:41

Why are you altering the table for each row for Employee table?

Madhivanan

Failing to plan is Planning to fail

K_P
Starting Member

10 Posts

Posted - 2008-04-09 : 06:29:42

hey ,i have to write a scipt to change the empno in all tables existing while checking the constraints, so i need to temporarily disable that, update table and then enable.

K_P
Starting Member

10 Posts

Posted - 2008-04-09 : 06:43:14

i updated the tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources