Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    24

    Unhappy Unanswered: SP Default output parameter does not work?

    OK! Here is an example:

    create proc testing @input1 tinyint = 4, @output1 tinyint = 1 output
    as

    begin
    if (@input1 > 10 ) and (@input1 <=15)
    begin
    select @ouptut1 = 0
    end
    end

    go

    declare @aa tinyint
    execute testiing @output1=@aa output
    select @aa
    go


    ** @aa is NULL????????

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I don't think output params can take a default value.

    But you do know that your IF block will return false, right?


    http://manuals.sybase.com/onlinebook...819;pt=43819#X
    Thanks,

    Matt

  3. #3
    Join Date
    Sep 2002
    Posts
    24
    If I execute from the SQL Advantage - Query -Execute Stored Procedure... The result @aa is 1 (Which is what I expected)

    But if I type in the exact words, the result is NULL(@aa is NULL)??? I can't find any where on sybase doc stay that it does not take default output?

  4. #4
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    hailieu

    1 You need to understand the meaning and handling of Null. The correct term in your question is 'Null', not 'Default'. Null handling is defined by ANSI, not Sybase.
    2 The SQL code is actually working correctly, the result is NULL. That is, the server side is correct, Sybases handling of NULL is correct, consistent and ANSI-compliant. On the client side, there are usually variations (depending on the client side tool used: SQL Advantage, VB, PB, perl, etc) in how the NULL that is returned from the server is INTERPRETED. MS client side tools usually have a default value (in your case, 1).
    3 Your initialisation (default setting) of the output parameter is meaningless since the output parameter is reset after execution begins. Default values are only meaningful for input parameters.
    4 Your code breaks two simple standards (good programming practices), intended for consistent behaviour regardless of the client side tool calling it:
    - initialise all variables to appropriate values
    - handle Null values correctly
    (eg. return TRUE or FALSE; you are returning TRUE or UNKNOWN which IS Null)
    5 Simply insert the following after the first BEGIN:
    SELECT @output = 0 -- or whatever you want FALSE to be signalled with
    6 Normally, you do not need an output parameter (I realise your code is an example) for this kind of need as there is an [procedure] execution staus that can be returned. The return status is distinct from an output parameter. You can rewrite it thus:
    Code:
    CREATE PROC testing 
        @input1 tinyint = 4  -- default
    AS
    
    BEGIN
    IF (@input1 > 10 ) AND (@input1 <=15)
        RETURN 0  -- true, success
    ELSE
        RETURN 1  -- false, failure
    END
    go
    
    DECLARE @RET_VALUE tinyint
    EXECUTE testing 99
    IF (@RET_VALUE != 0)
        PRINT "FAILED"  -- or whatever
    go
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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