Why doesn't SQL2008 interpret the conditional WHERE clause in my stored procedure the way 2005 did?

Question

My procedure was working fine in a 2005 database, but my PC died and when I set it up again I installed 2008 rather than 2005 - now this procedure doesn't return any results. I ask the question about the difference between the 2 versions simply because I have used this logic before and I haven't changed the procedure since I created it and it was working, the only change has been the fact I am now using SQL 2008

I wrote the procedure in Visual Studio and have noticed that when I paste the select statement into the SQL pane for the table that it is restructured and expanded so that each variation that could be expressed by combining the ANDs and ORs.

What I need is to be able to call this procedure optionally passing either parameter; so if I pass only the componentType it should evaluate the final statement part of the statement and use the value passed - if no value was passed then it would match the IS NULL side of the condition.

ALTER PROCEDURE dbo.uspSel_ComponentByType(
  @filterText  VARCHAR(50) = NULL 
, @componentType CHAR(2)  = NULL) 
AS 
SELECT  [pkComponentID], [ComponentType], [ComponentName], [fkSupplierID], [Cost], [WastageCost]
FROM    [tblComponents] AS c INNER JOIN
        [tblSuppliers] AS s ON [c].[fkSupplierID] = [s].[pkSupplierID]
WHERE   ([ComponentName] LIKE @filterText + '%' OR [SupplierName] LIKE @filterText + '%')
 AND    [c].[IsDeleted] = 0
 AND    (@componentType IS NULL OR [ComponentType] = @componentType)

Answer 1

I'm no SQL pro, but if I understand your intentions right, you should be able to use the sproc with no/either/both parameters and get expected results if you default @filterText to '' instead of NULL.

ALTER PROCEDURE dbo.uspSel_ComponentByType(
    @filterText           VARCHAR(50)     = ''      ,
    @componentType        CHAR(2)         = NULL
) 
AS 
    SELECT  ... --the rest of the sproc is unchanged

As David M clarified, this is probably because SQL Server 2005 and 2008 handles string concatenation with NULL values differently. It seems that in SQL Server 2005

'A string' + NULL = 'A string';

while in SQL Server 2008

'A string' + NULL = NULL;

By changing the default to '', we made the default comparison '' + '%', which will always be treated as % and match everything, instead of NULL + '%', which will end upp NULL and match nothing.

Answer 2

Looks like different default handling of concatenating a null value - it is now concatenating NULL with '%' to give NULL, causing the LIKE comparison to fail. If you replace the NULL default with an empty string for @filterText you have a stored procedure that is not affected by such a difference in behaviour.

Answer 3

I guess it's due to SET options, especially likely is CONCAT_NULL_YIELDS_NULL, with ANSI_NULLS as a bubbler