November 21, 2002 at 11:48 am
I have a stored procedure which retrievs data from a table using the COALESCE function to eliminate null variables which are passed. It is something like this:
CREATE PROCEDUER sp_something
@name VARCHAR(15) = NULL,
@phone VARCHAR(17) = NULL,
@dateupdated DATETIME = NULL
AS
SELECT name, phone, dateupdated
FROM TABLE1'
WHERE name = COALESCE(@name, name) AND
phone = COALESE(@phone, phone) AND
dateupdated = COALESCE(@dateupdated, dateupdated)
This works fine provided that none of the records contain a null value. When a field has a null value, such as dateupdated, it does not return those records. Has anyone found a way around this problem, other than asigning default values to fields. IE. set dateupdated = N/A for all records until they are actually updated with a logical value.
Thanks
November 21, 2002 at 12:10 pm
Couldn't you rewrite this to be something like this:
CREATE PROCEDUER sp_something
@name VARCHAR(15) = NULL,
@phone VARCHAR(17) = NULL,
@dateupdated DATETIME = NULL
AS
SELECT name, phone, dateupdated
FROM TABLE1'
WHERE (name = COALESCE(@name, name) or (name is null and @name is null) AND
(phone = COALESE(@phone, phone) or (phone is null and @phone is null) AND
(dateupdated = COALESCE(@dateupdated, dateupdated) or (dateupdated is null and @dateupdated is null)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 21, 2002 at 12:17 pm
That is not a bad idea, but my actual table has about 17 fields so it becomes kind of a coding mess.
November 21, 2002 at 12:39 pm
You can use SET ANSI_NULLS OFF which will evaluate NULL = NULL as true.
You should also not that your query will not use any indexes, because it does not know if any of the variables are NULL. You should have decide on your main criteria and ensure that your queries have column = @variable. Without this your query performance will be very poor.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply