execution faling for stored procedure

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)

execution faling for stored procedure

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-06-02 : 00:48:33

hi,
my procedure is like this


create PROCEDURE UPDATE_SCREENIMM UNDATA AS
DECLARE
  @currentGroupID        NUMERIC(20,0),
  @currentScreenImmunID  NUMERIC(20,0),
  @currentScreenImmunMasterID NUMERIC(20,0),
  @currentScreenImmunRecordType VARCHAR(2),
  @seedScreenImmunID     NUMERIC(20,0),
  @updateQuery1          VARCHAR(4000),
  @updateQuery2          VARCHAR(4000),
  @updateQuery3         VARCHAR(4000),
  @propertyName         VARCHAR(50)
BEGIN
        Declare ezEMRxGrpSpfScreenImmunRecords CURSOR  for SELECT SCREEN_IMMUN_ID, RECORD_TYPE FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID > 0 
                    ORDER BY SCREEN_IMMUN_ID
        Declare ezEMRxGroups CURSOR for SELECT SCREEN_IMMUN_ID, RECORD_TYPE FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID > 0 
                    ORDER BY SCREEN_IMMUN_ID
        Declare ezEMRxScreenImmunRecords CURSOR for SELECT SCREEN_IMMUN_ID, RECORD_TYPE FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID = 0 AND STATUS = 1 
                    ORDER BY SCREEN_IMMUN_ID
         SET @seedScreenImmunID = 2500
      OPEN ezEMRxGrpSpfScreenImmunRecords
              FETCH NEXT FROM ezEMRxGrpSpfScreenImmunRecords INTO @currentScreenImmunMasterID,@currentScreenImmunRecordType
                   WHILE @@FETCH_Status = 0
              BEGIN
               SET  @updateQuery1 = 'UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID = ' + @seedScreenImmunID + ' WHERE SCREEN_IMMUN_ID = ' + @currentScreenImmunMasterID
               SET  @updateQuery2 = 'UPDATE EMRPatientScreeningDetails SET SCREENING_REFERENCE_ID = ' + @seedScreenImmunID + ' WHERE SCREENING_REFERENCE_ID = ' + @currentScreenImmunMasterID;
               SET  @updateQuery3 = 'UPDATE EMRPatientImmunizationDetails SET IMMUNIZATION_REFERENCE_ID = ' + @seedScreenImmunID + ' WHERE IMMUNIZATION_REFERENCE_ID = ' + @currentScreenImmunMasterID;
                    EXEC @updateQuery1
                    EXEC @updateQuery2
                    EXEC @updateQuery3
              SET @seedScreenImmunID = @seedScreenImmunID+1
                    END
      CLOSE ezEMRxGrpSpfScreenImmunRecords
     DEALLOCATE ezEMRxGrpSpfScreenImmunRecords
              SET @propertyName = 'SCREEN_IMMUN_ID'
              SET @updateQuery1 = 'update emrids set ezemrxid = ' +  @seedScreenImmunID + ' where property_name = ''' + @propertyName + ''''
         EXEC @updateQuery1
 END
 GO

compliation succeded
but while execution
exec UPDATE_SCREENIMMUNDATA

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

SQLRatankalwa
Starting Member

42 Posts

Posted - 2009-06-02 : 02:55:39

Can you please post your table structures that would help analyzing your problem.

Ratan Kalwa
SQL Server Professionals User Group

http://www.revalsys.com

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-06-02 : 02:58:22

SP_HELP EMRSCREENIMMUNLKUP

@SCREEN_IMMUN_ID NUMERIC(20,0),
@RECORD_TYPE VARCHAR(2),
@GENDER NUMERIC(20,0),
@SUB_GENDER NUMERIC(20,0),
@DESCRIPTION VARCHAR(500),
@STARTING_AGE_OPERATOR VARCHAR(255),
@STARTING_AGE_VALUE NUMERIC(20,0),
@STARTING_AGE_UNITS VARCHAR(255),
@FREQUENCY VARCHAR(500),
@STARTING_AGE_LIMIT_RANGE NUMERIC(20,0),
@STARTING_AGE_LIMIT_UNITS VARCHAR(255),
@AGE_END_LIMIT NUMERIC(20,0),
@AGE_END_LIMIT_UNITS VARCHAR(255),
@REPEAT INT,
@REPEAT_AGE_VALUE NUMERIC(20,0),
@REPEAT_AGE_UNITS VARCHAR(255),
@FREQUENCY_LIMIT NUMERIC(20,0),
@REPEAT_CONDITION VARCHAR(255),
@GROUP_ID NUMERIC(20,0),
@STATUS NUMERIC(5,0),
@CDC_CODE VARCHAR(50),
@SCREEN_COMMENTS VARCHAR(255),
@MASTER_REFERENCE_ID NUMERIC(20,0)

SP_HEP EMRIDS
EZEMRXID INT,
PROPERTY_NAME VARCHAR(255)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 03:35:31

What is wrong with the suggestion I posted here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=126809

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

prav3575
Starting Member

5 Posts

Posted - 2009-06-02 : 08:35:12

Dear Rajasehkar

since @seedScreenImmunID variable has been declared as Numeric, concatenation using '+' may not be allowed in all update statements.

a.rameshk
Starting Member

19 Posts

Posted - 2009-06-02 : 09:49:51

You can follow this since you are concatenation numeric value to string you are getting that error.I am giving some sample code to rectify that.
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID'
/* Specify Parameter Format */
SET @ParameterDefinition = '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-06-02 : 09:54:34

quote:
Originally posted by Peso

What is wrong with the suggestion I posted here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=126809

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

Because you eliminated his cursors, correctly, which for whatever reason seems to be a requirement of his? Because you questioned, again correctly, his role pertaining to the code (i.e. consultant,homework)? Maybe the poster figures if the question is asked enough, they'll finally receive the answer they WANT, not the best approach to the solution - IMO

Terry

-- Procrastinate now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources