Set a variable value if it is null

  • Hi all, i need to check if an incoming variable is null and set its value to 0 if it's null.

    Here's my proc:

    the variables:

    @BookID int,

    @BookRate decimal (18,2)

    the statement

    UPDATE BooksTbl SET BookRate = @BookRate WHERE (BookID = @BookID)

    the variable i want to check on is @BookRate.

    Thanks for any help and advice.

  • Hi,

    You can check the variable like this.

    if @BookRate is null set @BookRate = 0

    Or you can incorporate the check within the update statement itself using the ISNULL function.

    UPDATE BooksTbl SET BookRate = isnull(@BookRate,0) WHERE (BookID = @BookID)

    Hope that helps,

  • thanks Karl.

  • Or, you could do this...

    SET @variable = ISNULL(@variable,0)

    ... for inline code, you don't even need to change the value... just use the formula...

    SELECT ISNULL(@variable,0)

    FROM yada yada

    ... OR...

    SELECT yada yada

    FROM wgga wugga

    WHERE somecol = ISNULL(@variable,0)

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for your help and advice, Jeff.

  • if this is a stored procedure parameter you can specify the default value

    create procedure dbo.myproc(

        @bookid int = 0,

        @bookrate decimal(18,2) = 0

        )

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply