CREATE PROCEDURE ezEMRx_Shift WITH ENCRYPTION AS
DECLARE 
      @appointmentIds varchar(4000),
      @cnt int,
      @SQL nvarchar (4000),
      @TSQL nvarchar (4000),
      @EMR_TRANSACTION_ID numeric(20,0),
      @APPOINTMENT_ID numeric(20,0),
      @USER_LOGIN varchar(50),
      @ID_VALUE numeric(20,0),
      @CREATED_DATE datetime,
      @CONSULTANT_ID varchar(50),
      @SAT_UPDATE_ID numeric(20,0),
      @PREEFIX_ID numeric(20,0), 
      @GSAT_UPDATE_ID varchar(50),
      @PATIENT_ID varchar(20),
      @ISTEMP numeric(1,0),
      @COLUMNLIST varchar(100),
      @VISIT_TYPE_ID numeric(20,0)
BEGIN      
      SELECT  @PREEFIX_ID=PREFIX_ID FROM EMRLOCATIONMASTER;  
       
                Declare task_Cursor CURSOR  FOR select EMR_TRANSACTION_ID,ID_VALUE,APPOINTMENT_ID,
                   CONSULTANT_ID,VISIT_TYPE_ID,CREATED_DATE,PATIENT_ID from (select EMR_TRANSACTION_ID,ID_VALUE,PATIENT_ID
                   from EMRSupplementaryTransactions where ID_VALUE IN 
                 (select APPOINTMENT_ID from EMRAppointmentDetails where APPOINTMENT_STATUS_ID=1 
                  and appointment_id IN ('+ @appointmentIds + ')) )Trans, 
                  (select  APPOINTMENT_STATUS_ID,APPOINTMENT_ID,CONSULTANT_ID,VISIT_TYPE_ID,CREATED_DATE from 
                   EMRAppointmentDetails WHERE  VISIT_TYPE_ID!=10) APPMT Where Trans.ID_VALUE=APPMT.APPOINTMENT_ID 
                   AND APPOINTMENT_STATUS_ID=1 AND VISIT_TYPE_ID!=10
            Open task_Cursor
          FETCH NEXT FROM task_Cursor INTO @EMR_TRANSACTION_ID,@ID_VALUE,@APPOINTMENT_ID,@CONSULTANT_ID,@VISIT_TYPE_ID,@CREATED_DATE,@PATIENT_ID ;

            WHILE @@FETCH_Status = 0 

            BEGIN 
                     SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + @PATIENT_ID FROM INFORMATION_SCHEMA.Columns
                            WHERE table_name = 'EMRSupplementaryTransactions' 
                  --PRINT @TSQL;
                 
                   BEGIN
                     INSERT INTO EMRTransactions (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
                         WHERE EMR_TRANSACTION_ID=@EMR_TRANSACTION_ID;
                      DELETE EMRSupplementaryTransactions WHERE EMR_TRANSACTION_ID=@EMR_TRANSACTION_ID;

quote:

---

Originally posted by rajasekhar857

hi,

my procedure is like this

CREATE PROCEDURE ezEMRx_Shift WITH ENCRYPTION AS
DECLARE 
      @appointmentIds varchar(4000),
      @cnt int,
      @SQL nvarchar (4000),
      @TSQL nvarchar (4000),
      @EMR_TRANSACTION_ID numeric(20,0),
      @APPOINTMENT_ID numeric(20,0),
      @USER_LOGIN varchar(50),
      @ID_VALUE numeric(20,0),
      @CREATED_DATE datetime,
      @CONSULTANT_ID varchar(50),
      @SAT_UPDATE_ID numeric(20,0),
      @PREEFIX_ID numeric(20,0), 
      @GSAT_UPDATE_ID varchar(50),
      @PATIENT_ID varchar(20),
      @ISTEMP numeric(1,0),
      @COLUMNLIST varchar(100),
      @VISIT_TYPE_ID numeric(20,0)
BEGIN      
      SELECT  @PREEFIX_ID=PREFIX_ID FROM EMRLOCATIONMASTER;  
       
                Declare task_Cursor CURSOR  FOR select EMR_TRANSACTION_ID,ID_VALUE,APPOINTMENT_ID,
                   CONSULTANT_ID,VISIT_TYPE_ID,CREATED_DATE,PATIENT_ID from (select EMR_TRANSACTION_ID,ID_VALUE,PATIENT_ID
                   from EMRSupplementaryTransactions where ID_VALUE IN 
                 (select APPOINTMENT_ID from EMRAppointmentDetails where APPOINTMENT_STATUS_ID=1 
                  and  ',' + @appointmentIds + ',' LIKE %,' + CAST(appointment_id AS varchar(10)) + ',%' ) )Trans, 
                  (select  APPOINTMENT_STATUS_ID,APPOINTMENT_ID,CONSULTANT_ID,VISIT_TYPE_ID,CREATED_DATE from 
                   EMRAppointmentDetails WHERE  VISIT_TYPE_ID!=10) APPMT Where Trans.ID_VALUE=APPMT.APPOINTMENT_ID 
                   AND APPOINTMENT_STATUS_ID=1 AND VISIT_TYPE_ID!=10
            Open task_Cursor
          FETCH NEXT FROM task_Cursor INTO @EMR_TRANSACTION_ID,@ID_VALUE,@APPOINTMENT_ID,@CONSULTANT_ID,@VISIT_TYPE_ID,@CREATED_DATE,@PATIENT_ID ;

            WHILE @@FETCH_Status = 0 

            BEGIN 
                     SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + @PATIENT_ID FROM INFORMATION_SCHEMA.Columns
                            WHERE table_name = 'EMRSupplementaryTransactions' 
                  --PRINT @TSQL;
                 
                   BEGIN
                     INSERT INTO EMRTransactions (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
                         WHERE EMR_TRANSACTION_ID=@EMR_TRANSACTION_ID;
                      DELETE EMRSupplementaryTransactions WHERE EMR_TRANSACTION_ID=@EMR_TRANSACTION_ID;

on execution throwing error message like

Msg 8114, Level 16, State 5, Procedure ezEMRx_Shift, Line 32
Error converting data type varchar to numeric.

can you help me out where exactly i have to change for this.

---