How to get SQL Function run a different query and return value from either query?
- by RoguePlanetoid
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
