ODBC and Sybase

[Big Trev]

Not sure if this is a bug in Microsoft.Data.Odbc, but here goes anyway. I have a table in Sybase 11.9.2, with 2 columns, ID and Description. I have the following stored procedure, CREATE PROCEDURE Get @ID PK_SMALLINT = NULL Select * from Table where ( (@ID = NULL) or (ID = @ID) ) The problem is that when i run the stored procedure without passing a Parameter from Dot Net, i only get the column names returned in my result set, yet when i run it from ISQL or similar, i get the complete contents of the table. Has anyone seen this before? When i pass in an ID value, i get 1 row returned as expected. It looks like there is some problem with or statements of this type, and the odbc data adapter.

[Daniel Turini]

Sybase has the most unstable ODBC drivers I've seen so far. Be careful with them. But what you're seeing is not an ODBC error, this is a common mistake. This query leads to unpredictable results:

Select * from Table
where ( (@ID = NULL) or (ID = @ID) )

Because of the expression WHERE field = NULL. The right way of doing a comparison with NULL is with the IS NULL expression. This query always run as expected:

Select * from Table
where ( (@ID IS NULL) or (ID = @ID) )

BTW, I think that maybe what you want is this:

Select * from Table
where ( (ID IS NULL) or (ID = @ID) )

because when @ID is NULL, this condition is true for all rows, so the 1st query will really return all rows lazy isn't my middle name.. its my first.. people just keep calling me Mel cause that's what they put on my drivers license. - Mel Feik

[Big Trev]

Thanks, using IS NULL has fixed the problem.