We are using Sybase ASE for an application we are developing from scratch. We have tables which have Bit columns. In Sybase, Bit datatypes cannot be null. This is fine for the records we are storing in the tables, but we have a design issue for our SEARCH stored procedures. We are relying heavily on code generators for our stored procedures and the corresponding data access layer classes (in .NET using Dapper). Since we are not able to pass NULL for the Bit parameters to our SEARCH stored procedures, we are only able to retrieve records with either 1 or 0 values to the Bit columns. Example below:
Table1 Column1 INT NULL, Column2 BIT SPROC1 @Column1 INT NULL, @Column2 BIT SELECT Column1, Column2 FROM Table1 WHERE Column1 = ISNULL(@Column1, Column1) AND Column2 = ISNULL(@Column2, Column2) --since @Column2 contains either 1 or 0, this search sproc cannot return both
There are workarounds to this such as introducing another BIT parameter to denote whether to skip matching the bit column, but since we write and maintain the stored procedure code generators we want to keep the stored procedures as simple as possible. Not to mention this has to be done for every bit column/parameter. Another way is to of course change the bit datatype to tinyint. But is this a valid/good design? Doesn't seem to be a good practice to me, but that should fix all our Sybase bit woes. We can create a custome tinyint datatype that's nullable and should allow only 0 and 1. If we change to tinyint, we will have no choice but to change the boolean properties of our data access classes to int16 (I'm ok with this, just to get around the bit issue).
Based on the above generator, passing null means do not filter based on that column. If you can modify the generator code, please check the below solution:
In Sybase, bit data type, accepts the values other than 0 and 1 are accepted but are always interpreted as 1.
The stored proc can be invoked by passing (-1) or passing (3) ; when one aims to not to filter based on that column. And the generator's code can be updated to produce the following sql on the fly:
SELECT Column1, Column2 FROM Table1 WHERE Column1 = ISNULL(@Column1, Column1) AND ( Column2 = ISNULL(@Column2, Column2) or ( @column2 not in (1,0) ) ) Column3 = ISNULL(@Column3, Column3) AND .....