stored procedure - 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)

stored procedure

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-06-01 : 06:20:38

hi can anyone help me in giving this oracle stored procedure into sql server.

create or replace PROCEDURE UPDATE_SCREENIMMUNDATA AS

CURSOR ezEMRxGrpSpfScreenImmunRecords IS
SELECT SCREEN_IMMUN_ID, RECORD_TYPE FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID > 0
ORDER BY SCREEN_IMMUN_ID;

CURSOR ezEMRxGroups IS
SELECT GROUP_ID FROM EMRGROUPMASTER WHERE STATUS = 1;
CURSOR ezEMRxScreenImmunRecords IS
SELECT SCREEN_IMMUN_ID, RECORD_TYPE FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID = 0 AND STATUS = 1
ORDER BY SCREEN_IMMUN_ID;
currentGroupID NUMERIC(20,0);
currentScreenImmunID NUMERIC(20,0);
currentScreenImmunMasterID NUMERIC(20,0);
currentScreenImmunRecordType VARCHAR2(2);
seedScreenImmunID NUMERIC(20,0);
updateQuery1 VARCHAR2(4000);
updateQuery2 VARCHAR2(4000);
updateQuery3 VARCHAR2(4000);
propertyName VARCHAR2(50);
BEGIN

seedScreenImmunID := 2500;
OPEN ezEMRxGrpSpfScreenImmunRecords;
LOOP
FETCH ezEMRxGrpSpfScreenImmunRecords INTO currentScreenImmunMasterID, currentScreenImmunRecordType;
EXIT WHEN ezEMRxGrpSpfScreenImmunRecords%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Processing screening / immunization record, Group Specific - ' || currentScreenImmunMasterID);
updateQuery1 := 'UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID = ' || seedScreenImmunID || ' WHERE SCREEN_IMMUN_ID = ' || currentScreenImmunMasterID;
updateQuery2 := 'UPDATE EMRPatientScreeningDetails SET SCREENING_REFERENCE_ID = ' || seedScreenImmunID || ' WHERE SCREENING_REFERENCE_ID = ' || currentScreenImmunMasterID;
updateQuery3 := 'UPDATE EMRPatientImmunizationDetails SET IMMUNIZATION_REFERENCE_ID = ' || seedScreenImmunID || ' WHERE IMMUNIZATION_REFERENCE_ID = ' || currentScreenImmunMasterID;

execute immediate updateQuery1;
execute immediate updateQuery2;
execute immediate updateQuery3;
commit;
seedScreenImmunID := seedScreenImmunID + 1;
END LOOP;

CLOSE ezEMRxGrpSpfScreenImmunRecords;

propertyName := 'SCREEN_IMMUN_ID';

updateQuery1 := 'update emrids set ezemrxid = ' || seedScreenImmunID || ' where property_name = ''' || propertyName || '''';
execute immediate updateQuery1;
commit;
END;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 06:55:06

This is a Microsoft SQL Server forum.
Maybe you should try your luck over at www.dbforums.com and your vendor of RDBMS.

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-01 : 07:13:27

quote:
hi can anyone help me in giving this oracle stored procedure into sql server.

I think OP needs help to convert the Oracle SP into MSSQL T-SQL

KH
[spoiler]Time is always against us[/spoiler]

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-06-01 : 07:46:50

create PROCEDURE UPDATE_SCREENIMMUNDATA1 AS
DECLARE
@currentGroupID NUMERIC(20,0),
@currentScreenImmunID NUMERIC(20,0),
@currentScreenImmunMasterID NUMERIC(20,0),
@currentScreenImmunRecordType VARCHAR(2),
@seedScreenImmunID NUMERIC(20,0),
@updateQuery1 VARCHAR2(4000),
@updateQuery2 VARCHAR2(4000),
@insertQuery VARCHAR2(4000),
@propertyName VARCHAR2(50),
@screenImmunRecord EMRScreenImmunLkup%ROWTYPE
BEGIN
Declare ezEMRxGroups CURSOR for SELECT GROUP_ID FROM EMRGROUPMASTER WHERE STATUS = 1;
Declare ezEMRxScreenImmunRecords CURSOR for SELECT * FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID = 0
ORDER BY SCREEN_IMMUN_ID;

--Data Migration for group specific screenings and immunizations records.
SELECT ezEMRxID INTO currentScreenImmunID FROM EMRIDS WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID';
OPEN ezEMRxGroups
FETCH NEXT FROM ezEMRxGroups INTO @currentGroupID;
WHILE @@FETCH_Status = 0
OPEN ezEMRxScreenImmunRecords
FETCH NEXT FROM ezEMRxScreenImmunRecords INTO @screenImmunRecord
WHILE @@FETCH_Status = 0

--updateQuery1 := 'UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID = ' || currentScreenImmunID || ' WHERE SCREEN_IMMUN_ID = ' || currentScreenImmunMasterID;
screenImmunRecord.MASTER_REFERENCE_ID = screenImmunRecord.SCREEN_IMMUN_ID
screenImmunRecord.SCREEN_IMMUN_ID = currentScreenImmunID
screenImmunRecord.GROUP_ID = currentGroupID
INSERT INTO EMRSCREENIMMUNLKUP VALUES screenImmunRecord
SET @updateQuery1 = 'UPDATE EMRPatientScreeningDetails SET SCREENING_REFERENCE_ID = ' + currentScreenImmunID + ' WHERE PATIENT_ID IN ( SELECT DISTINCT PATIENT_ID FROM EMRPATIENTSMASTER WHERE LOCATION_ID IN ( SELECT DISTINCT LOCATION_ID FROM EMRLOCATIONMASTER WHERE GROUP_ID = ' + currentGroupID + '))'
SET @updateQuery2 = 'UPDATE EMRPatientImmunizationDetails SET IMMUNIZATION_REFERENCE_ID = ' + currentScreenImmunID + ' WHERE PATIENT_ID IN ( SELECT DISTINCT PATIENT_ID FROM EMRPATIENTSMASTER WHERE LOCATION_ID IN ( SELECT DISTINCT LOCATION_ID FROM EMRLOCATIONMASTER WHERE GROUP_ID = ' + currentGroupID + '))'

EXEC @updateQuery1
EXEC @updateQuery2

SET @currentScreenImmunID = @currentScreenImmunID + 1
END
CLOSE ezEMRxScreenImmunRecords

END

CLOSE ezEMRxGroups

SET @updateQuery1 = 'update emrids set ezemrxid = ' + seedScreenImmunID + ' where property_name = ''' + propertyName + ''''
EXEC @updateQuery1
END

GETTING EXCEPTIONS

Msg 102, Level 15, State 1, Procedure UPDATE_SCREENIMMUNDATA1, Line 12
Incorrect syntax near '%'.
Msg 137, Level 15, State 2, Procedure UPDATE_SCREENIMMUNDATA1, Line 24
Must declare the scalar variable "@screenImmunRecord".
Msg 102, Level 15, State 1, Procedure UPDATE_SCREENIMMUNDATA1, Line 29
Incorrect syntax near 'screenImmunRecord'.
Msg 102, Level 15, State 1, Procedure UPDATE_SCREENIMMUNDATA1, Line 32
Incorrect syntax near 'screenImmunRecord'.
Msg 156, Level 15, State 1, Procedure UPDATE_SCREENIMMUNDATA1, Line 46
Incorrect syntax near the keyword 'CLOSE'.
Msg 102, Level 15, State 1, Procedure UPDATE_SCREENIMMUNDATA1, Line 50
Incorrect syntax near 'END'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 07:49:38

1. Get rid of all cursors and write 3 UPDATE statements instead.
2. Get rid of the cursors.

E 12°55'05.63"
N 56°04'39.26"

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-06-01 : 07:51:35

what is %rowtype variable of oracle in MSSql

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-06-01 : 07:52:54

can you send me the code as i am new to sql server

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 08:03:59

Try this. Get rid of the cursors

CREATE PROCEDURE dbo.uspUpdateScreenImmunData
AS

SET NOCOUNT ON

CREATE TABLE	#Lookup
		(
			SCREEN_IMMUN_ID DECIMAL(20, 0),
			rowID DECIMAL(20, 0)
		)

DECLARE	@seedScreenImmunID INT

SET	@seedScreenImmunID = 2500

INSERT		#Lookup
		(
			SCREEN_IMMUN_ID,
			rowID
		)
SELECT		SCREEN_IMMUN_ID,
		@seedScreenImmunID - 1 + ROW_NUMBER() OVER (ORDER BY SCREEN_IMMUN_ID) AS rowID
FROM		EMRSCREENIMMUNLKUP
WHERE		GROUP_ID > 0 

-- Update query #1
UPDATE		t
SET		t.SCREEN_IMMUN_ID = l.rowID
FROM		EmrScreenImmunLkup AS t
INNER JOIN	#Lookup AS l ON l.SCREEN_IMMUN_ID = t.SCREEN_IMMUN_ID

-- Update query #2
UPDATE		t
SET		t.SCREENING_REFERENCE_ID = l.rowID
FROM		EMRPatientScreeningDetails AS t
INNER JOIN	#Lookup AS l ON l.SCREEN_IMMUN_ID = t.SCREEN_IMMUN_ID

-- Update query #3
UPDATE		t
SET		t.IMMUNIZATION_REFERENCE_ID = d.seedScreenImmunID
FROM		EMRPatientImmunizationDetails AS d ON d.IMMUNIZATION_REFERENCE_ID = t.SCREEN_IMMUN_ID

-- Update query #4
UPDATE	emrids
set	ezemrxid = @seedScreenImmunID + (SELECT COUNT(*) FROM #Lookup)
WHERE	property_name = 'SCREEN_IMMUN_ID'

DROP TABLE	#Lookup
GO

E 12°55'05.63"
N 56°04'39.26"

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-06-02 : 04:07:49

they are asking to give in cursors way only

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 05:44:22

Why? It makes no sense?
Microsoft SQL Server works better without the use of a CURSOR in most cases.

And what do you mean by "to give in"?
Are you a consultant who is paid to convert to code? Is this a homework question?

In real life, four update statements as I suggest above will perform much, much, better than any CURSOR solution.
It will also be easier to maintain and debug in the future.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources