IS NOT NULL not working in stored procedure when SET ANSINULL ON

[viper78]

Hi,

I have a simple query in a stored procedure that have something like "WHERE Col1 IS NOT NULL". The query run correctly when SET ANSINULL OFF but not working when SET ANSINULL ON. (IS NULL work correctly in two situation.)

below is my code

I'm using Sybase 11.0 running in Sun Solaris 5.6

CREATE TABLE test
(
F1 VARCHAR ( 100 ) NULL,
F2 VARCHAR ( 100 ) NULL
)

INSERT INTO test VALUES ( 'data1', 'hello')
INSERT INTO test VALUES ( 'data2', 'dfdf')
INSERT INTO test VALUES ( 'data3', NULL)

CREATE PROC usp_test
AS
SELECT * FROM test WHERE F2 IS NOT NULL
GO

SET ANSINULL OFF
EXEC usp_test
--the result is expected

SET ANSINULL ON
EXEC usp_test
--no result


Pls help

Thank you