Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    NY
    Posts
    2

    Unanswered: Stored proc Param verify in Sybase

    Hello everyone,

    Say I have a stored proc with a parameter that takes a null value and a user has a typo in the parameter. Is there any way in Sybase 12.x at the stored procedure level to catch this mistake. As far as I know, Sybase just ignores spurious params.

    Here's a simple example :

    create procedure myProc
    (@theId numeric(12),
    @updateTS datetime=null,
    @debug tinyint=0
    )
    as
    if @updateTS is null
    begin
    select * from MyTable where TheId = @theId
    end
    else
    begin
    select * from MyTable where TheId = @theId and UPD_TS < @updateTS
    end
    go

    For instance, the user types:
    exec myProc @theId = "123", @updateTime = "20030101"
    and is perplexed by results.

    I can't redesign the proc. It's in wide use and the procedure is well-documented. The real example is more complex. (It uses dynamic sql, has about 20 params with most of the params defaulting to null. )

    Thanks for your help,
    Grandpa

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Not possible to change this behavior. In fact, the SP returns what you're asking for...

    I agree, a msg like "error : parameter @xxx doesn't exist for the procedure sp_xxx" will be welcome

  3. #3
    Join Date
    Jul 2003
    Location
    NY
    Posts
    2
    fadace,

    Thanks for the confirmation.

    Could I ask you one other question? Are the defaults for a stored procedure's parameters stored anywhere in the database (except for sp_helptext). I see in 12.5, sp_help indicates if a param is "in or out" but I can't find the defaults listed anywhere. One of my colleagues is putting something together that assesses the params to a stored procedure dynamically and wants to avoid parsing sp_helptext.

    Thanks,
    Grandpa

  4. #4
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    Originally posted by grandpascorpion
    Could I ask you one other question? Are the defaults for a stored procedure's parameters stored anywhere in the database (except for sp_helptext). I see in 12.5, sp_help indicates if a param is "in or out" but I can't find the defaults listed anywhere. One of my colleagues is putting something together that assesses the params to a stored procedure dynamically and wants to avoid parsing sp_helptext.

    Thanks,
    Grandpa
    There may be some representation in syscolumns.

    There is a column syscolumns.cdefault that contains the ID of the procedure that generates the default value for the column. Since syscolumns stores parameters to stored procedures as well as columns in tables and views, the cdefault column may be used to determin the default. If so it's like a default is created for each parameter that has a default value.

    What I'm say may happen, or I may be completely wrong. I haven't checked it out.

    Richard.

Posting Permissions

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