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-07-03 : 04:35:36

[code]
Begin

Declare
@appointmentIds varchar(4000),
@cnt int,
@SQL nvarchar (4000),
@TSQL nvarchar (4000),
@EMR_TRANSACTION_ID numeric(20,0),
@ID_VALUE numeric(20,0),
@APPOINTMENT_ID numeric(20,0),
@CONSULTANT_ID varchar(50),
@VISIT_TYPE_ID numeric(20,0);
DECLARE appmt_Cursor CURSOR FAST_FORWARD FOR
select appointment_ids from EMRTPBilledItems where appointment_ids is not null;
OPEN appmt_Cursor
FETCH NEXT FROM appmt_Cursor INTO @appointmentIds;
WHILE @@FETCH_Status = 0
BEGIN
EXEC ('Declare task_Cursor CURSOR FOR select EMR_TRANSACTION_ID,ID_VALUE,APPOINTMENT_ID,CONSULTANT_ID,VISIT_TYPE_ID from (select EMR_TRANSACTION_ID,ID_VALUE from EMRSupplementaryTransactions_bak where ID_VALUE IN (select APPOINTMENT_ID from EMRAppointmentDetails_bak where APPOINTMENT_STATUS_ID=1 and appointment_id IN ('+ @appointmentIds + ')) )Trans, (select APPOINTMENT_STATUS_ID,APPOINTMENT_ID,CONSULTANT_ID,VISIT_TYPE_ID from EMRAppointmentDetails_bak ) APPMT Where Trans.ID_VALUE=APPMT.APPOINTMENT_ID AND APPOINTMENT_STATUS_ID=1');
Open task_Cursor
FETCH NEXT FROM task_Cursor INTO @EMR_TRANSACTION_ID,@ID_VALUE,@APPOINTMENT_ID,@CONSULTANT_ID,@VISIT_TYPE_ID;

WHILE @@FETCH_Status = 0

BEGIN

--PRINT @TSQL;
IF @VISIT_TYPE_ID != 10
BEGIN
INSERT INTO EMRTransactions_bak (EMR_TRANSACTION_ID,FLOW_ID,NODE_DEFINITION_ID,ROLE_NAME,USER_LOGIN,PATIENT_ID,TASK_ID,ID_VALUE,FLOW_DETAILS_ID,IS_EXPRESS)
SELECT EMR_TRANSACTION_ID,FLOW_ID,NODE_DEFINITION_ID,'CONSULTANT',@CONSULTANT_ID,PATIENT_ID,9,@APPOINTMENT_ID,1021,IS_EXPRESS FROM EMRSupplementaryTransactions_bak
WHERE EMR_TRANSACTION_ID=@EMR_TRANSACTION_ID;
DELETE EMRSupplementaryTransactions_bak WHERE EMR_TRANSACTION_ID=@EMR_TRANSACTION_ID;
UPDATE EMRAppointmentDetails_bak SET APPOINTMENT_STATUS_ID=2,LAST_UPDATED_DATE=CREATED_DATE,
LAST_UPDATED_USER=USER_LOGIN WHERE APPOINTMENT_ID=@APPOINTMENT_ID;
DELETE EMRAppointmentDetails_bak WHERE APPOINTMENT_ID=@APPOINTMENT_ID;
PRINT 'Consultant :-APPOINTMENT_ID=' +CONVERT(varchar(30), @APPOINTMENT_ID) + ',EMR_TRANSACTION_ID=' +CONVERT(varchar(30), @EMR_TRANSACTION_ID);
END
ELSE IF @VISIT_TYPE_ID = 10
BEGIN
PRINT 'Lab :-APPOINTMENT_ID=' +CONVERT(varchar(30), @APPOINTMENT_ID) + ',EMR_TRANSACTION_ID=' +CONVERT(varchar(30), @EMR_TRANSACTION_ID);
END
FETCH NEXT FROM task_Cursor INTO @EMR_TRANSACTION_ID,@ID_VALUE,@APPOINTMENT_ID,@CONSULTANT_ID,@VISIT_TYPE_ID;

End

CLOSE task_Cursor

DEALLOCATE task_Cursor

FETCH NEXT FROM appmt_Cursor INTO @appointmentIds;

END

CLOSE appmt_Cursor

DEALLOCATE appmt_Cursor

END

[/code]

hi how to remove the if condition IF @VISIT_TYPE_ID != 10

and place it in the above cursor declaration

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 05:26:00

can i ask what you're trying to achieve here?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-05 : 08:50:28

quote:
EXEC ('Declare task_Cursor CURSOR FOR select EMR_TRANSACTION_ID,ID_VALUE,APPOINTMENT_ID,CONSULTANT_ID,VISIT_TYPE_ID from (select EMR_TRANSACTION_ID,ID_VALUE from EMRSupplementaryTransactions_bak where ID_VALUE IN (select APPOINTMENT_ID from EMRAppointmentDetails_bak where APPOINTMENT_STATUS_ID=1 and appointment_id IN ('+ @appointmentIds + ')) )Trans, (select APPOINTMENT_STATUS_ID,APPOINTMENT_ID,CONSULTANT_ID,VISIT_TYPE_ID from EMRAppointmentDetails_bak where VISIT_TYPE_ID <> 10) APPMT Where Trans.ID_VALUE=APPMT.APPOINTMENT_ID AND APPOINTMENT_STATUS_ID=1');

Not sure correct or not...

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-05 : 10:53:55

What are you doing here ? First you update table EMRAppointmentDetails_bak and them immediately you delete it.

UPDATE EMRAppointmentDetails_bak 
SET    APPOINTMENT_STATUS_ID = 2,
       LAST_UPDATED_DATE     = CREATED_DATE,
       LAST_UPDATED_USER     = USER_LOGIN 
WHERE  APPOINTMENT_ID        = @APPOINTMENT_ID;

DELETE EMRAppointmentDetails_bak 
WHERE  APPOINTMENT_ID        = @APPOINTMENT_ID;

Not sure if this is what you are trying to do . . . try


INSERT INTO EMRTransactions_bak 
(
        EMR_TRANSACTION_ID, 
        FLOW_ID, 
        NODE_DEFINITION_ID, 
        ROLE_NAME, 
        USER_LOGIN, 
        PATIENT_ID, 
        TASK_ID, 
        ID_VALUE, 
        FLOW_DETAILS_ID, 
        IS_EXPRESS
)
SELECT 	b.EMR_TRANSACTION_ID, 
        b.FLOW_ID, 
        b.NODE_DEFINITION_ID, 
        'CONSULTANT', 
        d.CONSULTANT_ID, 
        b.PATIENT_ID, 
        9, 
        d.APPOINTMENT_ID, 
        1021, 
        b.IS_EXPRESS 
FROM 	EMRSupplementaryTransactions_bak b
	INNER JOIN EMRAppointmentDetails_bak d	ON	b.ID_VALUE		= d.APPOINTMENT_ID
	INNER JOIN EMRTPBilledItems i		ON	d.APPOINTMENT_ID	= i.APPOINTMENT_IDS
WHERE	d.APPOINTMENT_STATUS_ID	= 1 
AND	i.APPOINTMENT_ID	is not null
AND	d.VISIT_TYPE_ID		<> 10

DELETE 	d
FROM	EMRSupplementaryTransactions_bak d
WHERE 	EXISTS
	(
		SELECT	*
		FROM	EMRTransactions_bak x
		WHERE	x.EMR_TRANSACTION_ID = d.EMR_TRANSACTION_ID;
	)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources