cursor concepts - 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)

cursor concepts

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-04-13 : 06:07:56

hi,

In oracle:

Cursor c1 is select * from -----------;

In sql server:

Declare c1 cursor foe select * from -------;

What i required is:

In Oracle:

for i in c1 loop
update----set---

what we have to use for the above in sql server

could anyone help me out.How to use that?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 06:16:43

1 What are you trying to do with cursor?
2 Try googleing Cursor+SQL Server

Madhivanan

Failing to plan is Planning to fail

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-04-13 : 06:18:11

Hey,

If i understand correctly, you want to know how to loop through the cursor right?

Regards
RT

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-04-13 : 06:26:32

hi,
i have written like this
CREATE Procedure [dbo].[usp_PatientidUpdate](@OldPatient Varchar(255), @NewPatient Varchar(255))
AS
Begin
declare
@temp table (temp_table_name varchar(100))
-- New Script - Start
declare
c1 cursor for
select t1.Patient_id,
t1.Patient_firstname,
t1.Patient_lastname,
t2.Patient_address1,
t2.Patient_address2,
t2.Patient_city,
t2.Patient_zipcode,
t3.State_code
FROM EMRPATIENTSMASTER t1,
EMRPATIENTSPERSNLINFO t2,
EMRSTATESLKUP t3
WHERE t1.Patient_id = t2.Patient_id
AND t2.state_id = t3.state_id
-- New Script - End
insert into @temp select C.table_name
from
information_schema.columns
C,INFORMATION_SCHEMA.TABLES T
where column_name = 'PATIENT_ID' and C.Table_name =T.Table_name
and
T.Table_name not in ('EMRPatientsMaster','EMRPatientsPersnlInfo','EMRPatientsPharmaInfo','EMRPatientWorkmansComp','EMRPatientInsurance','EMRPatientOtherInfo')
and
T.TAbLE_TYPE='BASE TABLE'
declare @table_name
varchar(100)
while (exists(select temp_table_name from @temp))
begin
select @table_name=temp_table_name from @temp
exec (' update '+@table_name+' set PATIENT_ID='''+@NewPatient+''' where PATIENT_ID='''+@OldPatient+'''')
delete from @temp where
temp_table_name = @table_name
end
-- New Script - Start
open c1
FETCH c1 INTO @temp table
WHILE @@FETCH_STATUS = 0
begin

UPDATE EMRCorrespondenceHistory th SET th.RECIPIENT_FULLNAME = i.PATIENT_FIRSTNAME || i.PATIENT_LASTNAME,
th.RECIPIENT_FIRSTNAME=i.PATIENT_FIRSTNAME,
th.RECIPIENT_ADDRESS1 =i.PATIENT_ADDRESS1,
th.RECIPIENT_ADDRESS2 =i.PATIENT_ADDRESS2,
th.RECIPIENT_CITY =i.PATIENT_CITY,
th.RECIPIENT_ZIP =i.PATIENT_ZIPCODE,
th.RECIPIENT_STATE =i.STATE_CODE
WHERE th.Patient_id = i.Patient_id
-- New Script - End
FETCH c1 INTO @temp table
end
CLOSE fix_data_cursor
DEALLOCATE fix_data_cursor
end
end
End

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-04-13 : 07:06:32

Hi,
this is for oracle and want this block in sql server could anyone help me out

FOR i IN c1 LOOP
UPDATE EMRCorrespondenceHistory th SET th.RECIPIENT_FULLNAME = i.PATIENT_FIRSTNAME || i.PATIENT_LASTNAME,
th.RECIPIENT_FIRSTNAME=i.PATIENT_FIRSTNAME,
th.RECIPIENT_ADDRESS1 =i.PATIENT_ADDRESS1,
th.RECIPIENT_ADDRESS2 =i.PATIENT_ADDRESS2,
th.RECIPIENT_CITY =i.PATIENT_CITY,
th.RECIPIENT_ZIP =i.PATIENT_ZIPCODE,
th.RECIPIENT_STATE =i.STATE_CODE
WHERE th.Patient_id =i.Patient_id;
--NEW End
End Loop;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources