How to get SQL Function run a different query and return value from either query?

Question

I need a function, but cannot seem to get it quite right, I have looked at examples here and elsewhere and cannot seem to get this just right, I need an optional item to be included in my query, I have this query (which works):

SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR
(1000),Description)) AS LenDesc FROM tblItem
WHERE Title = @Title AND Manufacturer = @Manufacturer
ORDER BY LenDesc DESC

This works within a Function, however the Manufacturer is Optional for this search - which is to find the description of a similar item, if none is present, the other query is:

SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR
(1000),Description)) AS LenDesc FROM tblItem
WHERE Title = @Title ORDER BY LenDesc DESC

Which is missing the Manufacturer, how to I get my function to use either query based on the Manufacturer Value being present or not. The reason is I will have a function which first checks an SKU for a Description, if it is not present - it uses this method to get a Description from a Similar Product, then updates the product being added with the similar product's description.

Here is the function so far:

ALTER FUNCTION [dbo].[GetDescriptionByTitleManufacturer]
(   
    @Title varchar(400),
    @Manufacturer varchar(160)
)
RETURNS TABLE 
AS
RETURN (
    SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR
    (1000),Description)) AS LenDesc FROM tblItem
    WHERE Title = @Title AND Manufacturer = @Manufacturer
    ORDER BY LenDesc DESC
    )

I've tried adding BEGINs and IF...ELSEs but get errors or syntax problems each way I try it, I want to be able to do something like this pseudo-function (which does not work):

ALTER FUNCTION [dbo].[GetDescriptionByTitleManufacturer]
(   
    @Title varchar(400),
    @Manufacturer varchar(160)
)
RETURNS TABLE 
AS
BEGIN
IF (@Manufacturer = Null)
RETURN (
    SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR
    (1000),Description)) AS LenDesc FROM tblItem
    WHERE Title = @Title ORDER BY LenDesc DESC
    )
ELSE
RETURN (
    SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR
    (1000),Description)) AS LenDesc FROM tblItem
    WHERE Title = @Title AND Manufacturer = @Manufacturer
    ORDER BY LenDesc DESC
    )

END

Answer 1

Could you do something like this?

ALTER FUNCTION [dbo].[GetDescriptionByTitleManufacturer]
(   
    @Title varchar(400),
    @Manufacturer varchar(160)
)
RETURNS TABLE 
AS
RETURN (
    SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR
    (1000),Description)) AS LenDesc FROM tblItem
    WHERE Title = @Title AND (@Manufacturer IS NULL OR Manufacturer = @Manufacturer)
    ORDER BY LenDesc DESC
    )

Answer 2

You could try using NULL when manufacturer doesnt apply:

SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR
(1000),Description)) AS LenDesc FROM tblItem
WHERE Title = @Title AND (Manufacturer = @Manufacturer OR @Manufacturer IS NULL)
ORDER BY LenDesc DESC

Answer 3

When you do AND (Manufacturer = @Manufacturer OR @Manufacturer IS NULL) you eliminate the use of an index. There are many subtle performance implications to dynamic searchs, you should read Dynamic Search Conditions in T-SQL by Erland Sommarskog

If you have the proper SQL Server 2008 version (SQL 2008 SP1 CU5 (10.0.2746) and later), you can use this little trick to actually use an index:

add OPTION (RECOMPILE) onto your query, see Erland's article, and SQL Server will resolve the OR from within (@Manufacturer IS NULL OR Manufacturer = @Manufacturer) before the query plan is created based on the values of the local variables, and an index can be used.

ALTER FUNCTION [dbo].[GetDescriptionByTitleManufacturer]
(   
    @Title varchar(400),
    @Manufacturer varchar(160)
)
RETURNS TABLE 
AS
RETURN
(SELECT TOP 100 
     PERCENT SKU, Description, LEN(CONVERT(VARCHAR(1000),Description)) AS LenDesc 
     FROM tblItem
         WHERE Title = @Title 
         AND (@Manufacturer IS NULL OR Manufacturer = @Manufacturer)
     ORDER BY LenDesc DESC
     OPTION (RECOMPILE) ---<<<<only valid for SQL 2008 SP1 CU5 (10.0.2746) and later
)
GO