problems with IF @test

8 Sep 2005 11:38 AM

Rudderius

hey,

this is my procedure but is doesn't work. who can tell me what's wrong?

CREATE PROCEDURE new_procedure
     @test_var int
AS
BEGIN
   IF @test_var = NULL
   BEGIN
     INSERT INTO table (isnull) VALUES (0)
   END
   ELSE
   BEGIN
     INSERT INTO table (isnull) VALUES (1)
   END
END

the result of "IF @test_var = NULL" always seems to be false, because I
only get 1 in the column.

Why does this not work?

thanx

8 Sep 2005 11:42 AM

Jens

First of all if would suggest NOT to use reserved snytax word as
columnnames etc. (those one marked as colored in QA :-) ). Second, to
the base problem "= NULL" is NOT the same as "IS NULL" un less you
specify the ANSI_NULLS switch:

SET ANSI_NULLS ON
DECLARE @test varchar(100)
Select 1 where @test = NULL
SELECT 1 where @test is null

GO

SET ANSI_NULLS OFF
DECLARE @test varchar(100)
Select 1 where @test = NULL
SELECT 1 where @test is null

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

8 Sep 2005 11:47 AM

mehal.ua

CREATE PROCEDURE new_procedure
     @test_var int
AS
BEGIN
   IF @test_var is NULL
   BEGIN
     INSERT INTO table (isnull) VALUES (0)
   END
   ELSE
   BEGIN
     INSERT INTO table (isnull) VALUES (1)
   END
END

8 Sep 2005 12:18 PM

Dan Guzman

To add to the other responses, consider simplifying your proc with a CASE
expression:

CREATE PROCEDURE new_procedure
@test_var int
AS
INSERT INTO MyTable (isnull)
VALUES (CASE WHEN @test_var IS NULL THEN 0 ELSE 1 END)
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Rudderius" <dr***@bestopia.be> wrote in message
news:43202221$0$335$ba620e4c@news.skynet.be...
> hey,
>
> this is my procedure but is doesn't work. who can tell me what's wrong?
>
> CREATE PROCEDURE new_procedure
>     @test_var int
> AS
> BEGIN
>   IF @test_var = NULL
>   BEGIN
>     INSERT INTO table (isnull) VALUES (0)
>   END
>   ELSE
>   BEGIN
>     INSERT INTO table (isnull) VALUES (1)
>   END
> END
>
> the result of "IF @test_var = NULL" always seems to be false, because I
> only get 1 in the column.
>
> Why does this not work?
>
> thanx

problems with IF @test_var = NULL