Post reply

Set a variable value if it is null

  • July 6, 2006 at 3:55 am

    #191137

    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.

  • July 6, 2006 at 4:01 am

    #647701

    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,

    Karl
    source control for SQL Server

  • July 6, 2006 at 5:51 am

    #647717

    thanks Karl.

  • July 6, 2006 at 6:30 am

    #647729

    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)
    Intro to Tally Tables and Functions

  • July 6, 2006 at 8:37 am

    #647768

    Thanks for your help and advice, Jeff.

  • July 7, 2006 at 9:22 am

    #648023

    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