Not able to understand ISNULL functioning.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not able to understand ISNULL functioning.

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-05 : 09:46:44

I am using adventureworks data base. I went through this article on sqlservercentral. http://www.sqlservercentral.com/links/279595/61172

I am not able to understand as to why this statement fails to produce results.

Declare @ReorderPoint int
SET @ReorderPoint = NULL
SELECT * FROM Production.Product
WHERE ReorderPoint > ISNULL(@ReorderPoint,ReorderPoint)

This statement gives result.

Declare @ReorderPoint int
SET @ReorderPoint = NULL
SELECT * FROM Production.Product
WHERE ReorderPoint >= ISNULL(@ReorderPoint,ReorderPoint)

If someone can explain working of above two.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 09:51:56

When @ReorderPoint is null

SELECT * FROM Production.Product
WHERE ReorderPoint > ISNULL(@ReorderPoint,ReorderPoint)

becomes

SELECT * FROM Production.Product
WHERE ReorderPoint > ReorderPoint

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-06 : 23:57:21

i think what you were trying to do was this:-

SELECT * FROM Production.Product 
WHERE ReorderPoint > @ReorderPoint
OR @ReorderPoint IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources