problems with IF @test_var = NULL

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?
thanxFirst 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
--|||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|||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
"Rudderius" <dries@.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