Use of ISNULL in a sql stored procedure

I am looking at a stored procedure that uses the "ISNULL" sql command. I am confused exactly what it is doing on lines 11, 12 and 13 below. Is it just assigning the input values to the column in the database table?

For example on line 11 --> One = ISNULL(inOne, One)
Is it just assigning the column One to the value inOne if the value inOne is null? I am confused about this. Can an expert please explain?

1  Create procedure inProcess
2  @inTwo VARCHAR(16)
3  @inOne VARCHAR(16)
4  @inThree VARCHAR(16)

5  AS
6  SELECT
7  One,
8  Two,
9  Three
10 FROM CUSTOMERS WHERE
11 One = ISNULL(inOne, One) AND
12 Two = ISNULL(inTwo,Two) AND
13 Three=ISNULL(inThree, Three)
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Yes, in this particular case, the ISNULL on line #11 is assigning the value in column "One" if the value in "inOne" is NULL.

The ISNULL function evaluates if the first parameter is NULL. If not null, then that is returned and used for the evaluation of rest of the expression. If NULL, then the value in the second parameter is returned (even if it is NULL).

Here's an example:
USE tempdb;
GO
DECLARE @unInitializedDefaultValue INT;
DECLARE @initializedDefaultValue INT;

DECLARE @someTable TABLE (someColumn INT);

SET @initializedDefaultValue = 99;

INSERT INTO @someTable (someColumn)
VALUES (1),
       (2),
       (NULL),
       (4),
       (5);


SELECT st.someColumn,
       ISNULL(st.someColumn, @unInitializedDefaultValue) AS IsNullCheckWithUninitializedValue,
       ISNULL(st.someColumn, @initializedDefaultValue) AS IsNullCheckWithInitializedValue
FROM @someTable AS st;
GO

/* RESULTS
someColumn  IsNullCheckWithUninitializedValue IsNullCheckWithInitializedValue
----------- --------------------------------- -------------------------------
1           1                                 1
2           2                                 2
NULL        NULL                              99
4           4                                 4
5           5                                 5
*/

Open in new window


Hope it helps.
0
ste5anSenior DeveloperCommented:
Please use the CODE button.. and post valid, concise and complete T-SQL:

CREATE PROCEDURE inProcess
    @inTwo VARCHAR(16) ,
    @inOne VARCHAR(16) ,
    @inThree VARCHAR(16)
AS
    SELECT One ,
           Two ,
           Three
    FROM   ProjectFacts.Customers
    WHERE  One = ISNULL(@inOne, One)
           AND Two = ISNULL(@inTwo, Two)
           AND Three = ISNULL(@inThree, Three);

Open in new window


What it does is simply tested:

DECLARE @inTwo VARCHAR(16) ,
        @inOne VARCHAR(16) ,
        @inThree VARCHAR(16);

SELECT One ,
       Two ,
       Three ,
       ISNULL(@inOne, One) ,
       ISNULL(@inTwo, Two) ,
       ISNULL(@inThree, Three)
FROM   ProjectFacts.Customers;

Open in new window


ISNULL(value1, value2) returns value1 only when it is NOT NULL. Otherwise it returns value2. It also ensures data type compatibility. Thus each ISNULL() tests for a value in the parameter:

One = ISNULL(@inOne, One) => One = @inOne (tests each row for the value)

otherwise

One = ISNULL(@inOne, One) => One = One => always true (tautology)


It is also known as "catch all".
1
Mark WillsTopic AdvisorCommented:
Just tidying up @ste5an's post a little bit

ISNULL(@inOne, One) returns @inOne only when @inOne  is NOT NULL. Otherwise it returns One.
It also ensures data type compatibility. Thus each ISNULL() tests for a value in the parameter:
 
So, the code allows for @inOne being a NULL or a value. When @inOne has a value, the test
One = ISNULL(@inOne, One)  is basically testing for One = @inOne (tests each row for the value)

otherwise, if @inOne is a NULL, then the test
One = ISNULL(@inOne, One)  is basically testing for One = One => always true (tautology)

And the same again for @inTwo and @inThree. It is also known as "catch all".

No points, just helping a mate explain his post :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.