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:
CREATE PROC testing
@input1 tinyint = 4 -- default
IF (@input1 > 10 ) AND (@input1 <=15)
RETURN 0 -- true, success
RETURN 1 -- false, failure
DECLARE @RET_VALUE tinyint
EXECUTE testing 99
IF (@RET_VALUE != 0)
PRINT "FAILED" -- or whatever
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