Isnull

[baburk]

hI all,

One NULL is not equlal to another null.

So I use the functin if(ISNULL(@i, NULL) = NULL).

But this also didn't produce the correct Output.

May I know Why?

I had created a TEMP table and worked like this.

What the wrong.and how to change that one?.

When printing @i It produce only a single space ' '

DECLARE @i VARCHAR(20)
BEGIN
SET NOCOUNT ON

CREATE TABLE #temp (Item varchar(20))

INSERT INTO #temp(Item) VALUES('erty')

SELECT @i = Item FROM #temp WHERE Item = 'we'

DROP TABLE #temp

PRINT @i

IF(ISNULL(@i, NULL) = NULL)
BEGIN
PRINT 'Null'
END
ELSE
BEGIN
PRINT 'Not Null'
END

SET NOCOUNT OFF
END

[vksingh24]

hI all,

One NULL is not equlal to another null.

So I use the functin if(ISNULL(@i, NULL) = NULL).

But this also didn't produce the correct Output.

May I know Why?

I had created a TEMP table and worked like this.

What the wrong.and how to change that one?.

When printing @i It produce only a single space ' '

DECLARE @i VARCHAR(20)
BEGIN
SET NOCOUNT ON

CREATE TABLE #temp (Item varchar(20))

INSERT INTO #temp(Item) VALUES('erty')

SELECT @i = Item FROM #temp WHERE Item = 'we'

DROP TABLE #temp

PRINT @i

IF(ISNULL(@i, NULL) = NULL)
BEGIN
PRINT 'Null'
END
ELSE
BEGIN
PRINT 'Not Null'
END

SET NOCOUNT OFF
END

To compare the NULL with NULL we use IS Operator:

DECLARE @i VARCHAR(20)
BEGIN
SET NOCOUNT ON 

CREATE TABLE #temp (Item varchar(20))

INSERT INTO #temp(Item) VALUES('erty')

SELECT @i = Item FROM #temp WHERE Item = 'we'

DROP TABLE #temp

PRINT @i

IF(ISNULL(@i, NULL) IS NULL)
BEGIN
PRINT 'Null'
END
ELSE
BEGIN
PRINT 'Not Null'
END

SET NOCOUNT OFF
END

[PreethiParkavi]

Hi Friend,
The use of ISNULL operator is assign the value to a variable, when it is NULL and it's intention is not to check for NULL value. we can check for NULL value with

IS NULL

clause.so, we can change your T-SQL to :

DECLARE @i VARCHAR(20)
BEGIN
SET NOCOUNT ON
CREATE TABLE #temp (Item varchar(20))
INSERT INTO #temp(Item) VALUES('erty')
SELECT @i = Item FROM #temp WHERE Item = 'we'
DROP TABLE #temp
PRINT @i
IF(ISNULL(@i, NULL) is NULL) -- IF(@i is NULL)--
PRINT 'Null'
ELSE
PRINT 'Not Null'
SET NOCOUNT OFF
END

Here , we check for NULL value with resultant ISNULL operator or we can directly compare using @i is NULL.

Thanks,
Preethi

[ck9663]

isnull(exp1, exp2)
is a function that:

1. checks if the first expression is null.
2. if it's null, return the second expression.
3. if it's not, return the first expression.

to check if the expression is null try:

IF @exp is null

or

WHERE dbo.myTable.myC olumn IS NULL

to negate...

IF @exp IS NOT NULL

or

WHERE dbo.myTable.myC olumn IS NULL

-- ck