CODEWITHSUNDEEP
sql-servert-sqlisnull
Natalia
Natalia

Reputation: 21

T-SQL using 2 parameters and isNull

Could someone help me to understand the logic of this query (T-SQL in SQL Server 2014) in simple way?

Select 
    c.ContractID 
From 
    dba.contract as c 
Inner Join 
    dba.Person as r on (c.ContractID = r.ContractID 
                        and IsNull(isPrimary, 0) = 1)

The part that I dont understand is the isNull(isPrimary, 0) = 1.

What does that mean? Btw isPrimary is one of the columns in dba.person

Thank you so much!

Upvotes: 0

Views: 1969

Answers (4)

Rigerta
Rigerta

Reputation: 4039

The IS_NULL function is only replacing the value of isPrimary to 0, in case the isPrimary is equal to NULL.

Your check is only true when isPrimary is not null (because if it is, it will be replaced by 0) AND isPrimary = 1.

SELECT c.contractid 
FROM   dba.contract AS c 
       INNER JOIN dba.person AS r ON (c.contractid = r.contractid AND isprimary = 1)
WHERE isprimary IS NOT NULL

Upvotes: 0

user7715598
user7715598

Reputation:

Sample script for you to understand IsNull(isPrimary, 0) = 1 condition gives the result and helps in handling Null pointer exception.

DECLARE @table AS TABLE (Id int, isPrimary varchar(20))
INSERT INTO @table
SELECT 1,1001  UNION ALL
SELECT 2,1002  UNION ALL
SELECT 3,NULL  UNION ALL
SELECT 4,1004
SELECT Id,ISNULL(isPrimary,0) UIdnum FROM @table

SELECT * FROM @table WHERE ISNULL(isPrimary,0)=1

SELECT * FROM @table WHERE ISNULL(isPrimary,0)=0

Upvotes: 0

Evgeny
Evgeny

Reputation: 637

If you want to watch how it works, you can create table in your database:

use [your_database_name];
create table dbo.test_table
(
    t int null
);

insert into dbo.test_table 
values (0), (1), (2), (NULL);

select t, isnull(t, 0) as function_result
from dbo.test_table

Upvotes: 0

Anurag Dadheech
Anurag Dadheech

Reputation: 629

isNull(isPrimary, 0) = 1

isNull is a function of SQL which is used to verify null variable and above snippet describe as if isPrimary variable is null then replace this null value with 0. the purpose of this method to handle null pointer exception.

Upvotes: 1

Related Questions