Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    3

    Unanswered: Problem creating stored procedure on Sybase db 12.5.0.2

    Hi,

    I am trying to create the below stored procedure and getting error that TEXT and IMAGE are invalid for parameters or local variables.

    CREATE PROCEDURE TESTPROC @DateFrom datetime,
    @DateTo datetime,
    @WhereClause text
    AS
    DECLARE @SQLString text
    SET ROWCOUNT 200
    Select @SQLString = 'SELECT COL1,COL2,COL3 ' +
    ' FROM TABLE1,TABLE2 NOHOLDLOCK WHERE TABLE1.TYPE = TABLE2.TYPE AND COL1 >= ''' +
    convert(varchar,@DateFrom,3) + ''' AND COL1 <= ''' + convert(varchar,@DateTo,3) + ''' ' + @WhereClause + '
    ORDER BY COL1 DESC'

    EXEC @SQLString
    GO


    Can someone let me know how can I create this procedure and use it successflly.

    I tried changing text type to varchar(255) but the input for Whereclause variable can be of any length, also value lenth of @SQLString is more than 255 and could not use text type for both @Whereclause and @SQLString local variables.

    Thanks in advance
    Srinivas

  2. #2
    Join Date
    Jun 2007
    Posts
    3
    Here is the log generated while running the above SP Query.

    Server Message: Number 2739, Severity 16
    Server 'HVNSSYBASE1252RND', Procedure 'TESTPROC', Line 1:
    TEXT and IMAGE datatypes are invalid for parameters or local variables.

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by velidandas
    Can someone let me know how can I create this procedure and use it successflly
    The error shows you can't text as the variable type and your initial post said that 255 wasn't large enough to hold the SQL generated.

    So why not change the text type to a larger size than 255? I believe Sybase 12.5 will support varchars of up to 16k but I'm unsure whether you need to ask the DBA's to set this for you (it may have something to do with page size).

    The other option is to build the SQL in an external program and run it from there rather than in the stored proc.

Posting Permissions

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