none
ISNULL function in TSQL

    Question

  • Hi,

    I'm using ISNULL function in a user defined function.

    CREATE FUNCTION [dbo].[FN_Get_Order_State] 
    (
    	@Order_No VARCHAR(50)
    )
    RETURNS VARCHAR(50)
    AS
    BEGIN
    	DECLARE @Order_State VARCHAR(50)
    	
    	SELECT     @Order_State = ISNULL(DB_State,'Open')
            FROM       TBL_TRN_ORDER
            WHERE      (Order_No = @Order_No)
    
    	RETURN @Order_State
    END

    This compiles and executes.
    But the problem i have is when i use a non-existent order number as the parameter. I just get a null value as the result.
    Ideally i need to get the value Open if the order doesn't exist.

    Any ideas to get this working for me?

    Thanks in advance,
    Geethanga.

    geethanga
    Saturday, August 01, 2009 6:16 AM

Answers


  • When the order number is not existence, there will not be any row selected and hence the @Order_State will not get assign with 'Open'. However if the order is found but the DB_State is NULL it will get assigned with 'Open'.

    You can use ISNULL on the return statement like RETURN ISNULL(@Order_State, 'Open')


    CREATE FUNCTION [dbo].[FN_Get_Order_State]
    (
        @Order_No VARCHAR(50)
    )
    RETURNS VARCHAR(50)
    AS
    BEGIN
        DECLARE @Order_State VARCHAR(50)
       
        SELECT     @Order_State = ISNULL(DB_State,'Open')
            FROM       TBL_TRN_ORDER
            WHERE      (Order_No = @Order_No)
    
        RETURN ISNULL(@Order_State, 'Open')
    END
    

    KH Tan
    • Marked as answer by Geethanga Saturday, August 01, 2009 6:25 AM
    Saturday, August 01, 2009 6:21 AM

All replies


  • When the order number is not existence, there will not be any row selected and hence the @Order_State will not get assign with 'Open'. However if the order is found but the DB_State is NULL it will get assigned with 'Open'.

    You can use ISNULL on the return statement like RETURN ISNULL(@Order_State, 'Open')


    CREATE FUNCTION [dbo].[FN_Get_Order_State]
    (
        @Order_No VARCHAR(50)
    )
    RETURNS VARCHAR(50)
    AS
    BEGIN
        DECLARE @Order_State VARCHAR(50)
       
        SELECT     @Order_State = ISNULL(DB_State,'Open')
            FROM       TBL_TRN_ORDER
            WHERE      (Order_No = @Order_No)
    
        RETURN ISNULL(@Order_State, 'Open')
    END
    

    KH Tan
    • Marked as answer by Geethanga Saturday, August 01, 2009 6:25 AM
    Saturday, August 01, 2009 6:21 AM
  • Thanks Tan,

    I removed the ISNULL from SELECT and added it to return as you tell. Now it's working.

    Love MSDN
    Saturday, August 01, 2009 6:27 AM
  • Just beware of the difference between ISNULL and COALESCE.

    DECLARE @t VARCHAR(2)
    SELECT ISNULL(@t, 'Open'), COALESCE(@t, 'Open')

    Saturday, August 01, 2009 5:57 PM
  • And who doesn't know?

    /me raises hand

    /me tests Peso's case

    Op    Open

    It has to do with the type. ISNULL() returns 2 characters because @t is VARCHAR(2). COALESCE returns all the characters.
    Monday, August 03, 2009 12:07 PM
    Moderator