• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

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)
0
brgdotnet
Asked:
brgdotnet
3 Solutions
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now