March 3, 2005 at 8:24 am
G'day all,
Here is an interesting little bit of SQL. I have a solution that works, and one that fails. Setting ANSI_NULLS off is not an option. The actual table and query has more columns. The solution that fails will work if and only if the TblName column has been initialized in TblLog. Is there a cleaner way to write the working solution?
Thanks in advance for your thoughts,
Wayne
SET ANSI_NULLS ON
GO
CREATE TABLE TblLog (
LogID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Comment NVARCHAR (3000) NOT NULL,
TblName NVARCHAR (50) NULL
)
GO
INSERT INTO TblLog (Comment)
SELECT 'Comment 1'
UNION
SELECT 'Comment 2'
GO
DECLARE @InTblName NVARCHAR (50)
SELECT * FROM TblLog WHERE TblName = ISNULL(@InTblName, TblName) -- fails when TblName is not initialized, works if TblName has data
SELECT * FROM TblLog WHERE ISNULL(TblName,'-1') = ISNULL(@InTblName, ISNULL(TblName,'-1')) -- always works
March 3, 2005 at 8:41 am
March 3, 2005 at 8:48 am
Excellent article Antares,
However there was never a question of why the code behaves the way it does. I understand the behavior of ANSI_NULLS, = NULL, IS NULL, ISNULL(), and so on. The question is how to write the working solution in a cleaner manner.
Thanks for the fast response, and have a great day!
Wayne
March 3, 2005 at 12:55 pm
A couple of possibilities:
SELECT * FROM TblLog WHERE ISNULL(TblName,'-1') = ISNULL(@InTblName, '-1')
This has the built in assumption that '-1' is not legal in the field TblName. If this is true, then it should work the same as your longer version
Reason: If there is a NULL in @InTblName, you only wish to match NULL values in TblName. With '-1' being a special value, the compare succeeds.
This also suggests that, depending on how the query parser handles ISNULL(@InTblName, '-1'), that you should set the parameter to '-1' before performing the query, leaving:
Set @InTblName = ISNULL(@InTblName, '-1')
SELECT * FROM TblLog WHERE ISNULL(TblName,'-1') = @InTblName
You always have the verbose version:
... WHERE (@InTblName Is NULL AND TblName Is NULL) OR (TblName = @InTblName)
which is, IMO, clearer as well, and should provide a good query plan.
March 4, 2005 at 6:43 am
Actually I think the assumption is wrong this
SELECT * FROM TblLog WHERE ISNULL(TblName,'-1') = ISNULL(@InTblName, '-1')
will not return the same as this
SELECT * FROM TblLog WHERE ISNULL(TblName,'-1') = ISNULL(@InTblName, ISNULL(TblName,'-1'))
First I am going to make the assumption that if @InTblName has no value you want all reccords.
The first will return a matching record if @InTblName is supplied or only all NULLS because if -1 is not valid then when @InTblName it is looking for match of -1 which will only occurr on a NULL record.
The longer version will return match when @InTblName is supplied, All when @InTblName is null because it takes into account TblName = TblName where TblName is not null and -1 = -1 where TblName is Null.
In addition if make @InTblName -1 then you will get only the records where TblName Iis null.
So your best method will be
SELECT * FROM TblLog WHERE ISNULL(TblName,'-1') = ISNULL(@InTblName, ISNULL(TblName,'-1'))
March 4, 2005 at 7:44 am
Brendthess: Antares is correct. Your solution dropped various rows that should have been returned.
Antares: Your assumptions and conclusions are completely correct. Thank you for the confirmation that I am on the right track.
G'day all,
Wayne
March 10, 2005 at 5:42 pm
Poot. I didn't think through the logic fully.
Ah well, can't win them all.
I should have written:
WHERE @InTblName is Null OR Tblname = @InTblName
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply