Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007
    Posts
    1

    Unanswered: cannot convert varchar to numeric (was "Help please Urgent")

    Here is the code of a procs

    If Exists ( SELECT name
    FROM sysobjects
    WHERE name = 'PRC_GET_SECFN_DETAILS'
    AND type = 'P')
    DROP PROCEDURE PRC_GET_SECFN_DETAILS
    GO
    CREATE PROCEDURE PRC_GET_SECFN_DETAILS
    @pi_lUSER_SEQ_ID numeric (15)/* TABLE NOT FOUND IN METADATA OR METADATA NOT UPDATED */ ,
    @pi_lUSGP_SEQ_ID numeric (15)/* TABLE NOT FOUND IN METADATA OR METADATA NOT UPDATED */ ,
    @pi_SUPERUSER CHAR ,
    @po_SECFDETAILS int OUTPUT ,
    @po_ERRORCODE FLOAT OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON


    DECLARE @adv_rowcount INT

    DECLARE @adv_error INT

    DECLARE @vQuery VARCHAR(4000)
    IF ( @pi_SUPERUSER = 'Y' )
    BEGIN
    SELECT @vQuery = 'SELECT DISTINCT SF.SECF_SEQ_ID,SECF_NAME,SF.APPL_SEQ_ID,APGP_ID,' + '''Y'' AS READ_ACCESS,' + '''Y'' AS CREATE_ACCESS,' + '''Y'' AS UPDATE_ACCESS,' + '''Y'' AS DELETE_ACCESS' + ' FROM SECURITY_FUNCTIONS SF,APPLICATIONS APPL' + ' WHERE APPL.APPL_SEQ_ID = SF.APPL_SEQ_ID' + ' ORDER BY SF.APPL_SEQ_ID,SF.SECF_SEQ_ID'
    END
    ELSE
    BEGIN
    SELECT @vQuery = 'SELECT DISTINCT SF.SECF_SEQ_ID,SECF_NAME,SF.APPL_SEQ_ID,APGP_ID,' + 'DECODE(USEC.USSEC_READ,NULL,UGSEC.UGSEC_READ,USEC .USSEC_READ)READ_ACCESS,' + 'DECODE(USEC.USSEC_CREATE,NULL,UGSEC.UGSEC_CREATE, USEC.USSEC_CREATE)CREATE_ACCESS,' + 'DECODE(USEC.USSEC_UPDATE,NULL,UGSEC.UGSEC_UPDATE, USEC.USSEC_UPDATE)UPDATE_ACCESS,' + 'DECODE(USEC.USSEC_DELETE,NULL,UGSEC.UGSEC_DELETE, USEC.USSEC_DELETE)DELETE_ACCESS' + 'FROM SECURITY_FUNCTIONS SF,' + 'APPLICATIONS APPL,' + '(SELECT * FROM USER_SECURITY WHERE USER_SEQ_ID=' + @pi_lUSER_SEQ_ID + ')USEC,' + 'USER_GROUP_SECURITY UGSEC' + 'WHERE' + 'UGSEC.USGP_SEQ_ID =' + @pi_lUSGP_SEQ_ID + ' AND' + 'APPL.APPL_SEQ_ID = SF.APPL_SEQ_ID AND' + 'UGSEC.SECF_SEQ_ID = USEC.SECF_SEQ_ID(+) AND' + 'SF.SECF_SEQ_ID = UGSEC.SECF_SEQ_ID(+) ORDER BY APPL_SEQ_ID,SF.SECF_SEQ_ID'
    END

    EXEC @vQuery
    SELECT @po_ERRORCODE = 0
    GOTO ExitLabel1
    Exception1:

    IF @adv_rowcount = 0
    BEGIN

    SELECT @po_ERRORCODE = 1
    /* AdventNet SwisSQL (Oracle To Sybase) : Manual Intervention to verify Exception is required */
    END
    ELSE
    BEGIN

    SELECT @po_ERRORCODE = - 1
    RAISERROR 20100 "PRC_GET_SECFN_DETAILS :" , @adv_error
    /* AdventNet SwisSQL (Oracle To Sybase) : Manual Intervention to verify Exception is required */
    END
    ExitLabel1:

    SET NOCOUNT OFF

    END


    GO
    It keep giving me error of cannot convert varchar to numeric.
    I check the tables, they are numeric. Is that casuing the problem?

  2. #2
    Join Date
    Mar 2007
    Posts
    29
    Hi,
    The problem here is, in sybase while using dynamic sql u need to convert all the numeric,integer fields to varchar. Since in dynamic sql v'll store the query in varchar variable and execute it using 'exec @vquery'.

    since u have declared the variables @pi_lUSER_SEQ_ID,@pi_lUSGP_SEQ_ID as numeric u cannot directly use it to fill the vQuery variable(varchar).

    So convert the numeric field to varchar using convert function.

    select @vQuery = 'select ......... + convert(varchar(30),@pi_lUSER_SEQ_ID)+.

    Try executing it.... I hope u will get the desired output.

    Regards,
    Shiya

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •