Create And Execute Function In Sql Server

I have four tables: dbo.Projects dbo.Locations (id, location name) dbo.Purpose (id, Purposename) dbo.Types (id, typname) I have a search criteria, this criteria is filled with d

Solution 1:

I would go with:

ALTERFUNCTIONSearchProjects (
    @location NVARCHAR(50),
    @purpose NVARCHAR(50),
    @type NVARCHAR(50))
RETURNSTABLEASRETURN
(
    SELECT  p.ProjectName, 
            p.Areas, 
            p.PaymentSystem, 
            p.ReceivedDate,    
            p.PropertyClassification, 
            p.ProjectImage,
            l.LocationName,
            pur.PurposeName,
            t.TypeName
    FROM dbo.Projects AS p 
    LEFT JOIN dbo.Locations AS l ON p.LocationID = l.ID      
    LEFT JOIN dbo.Purposes pur ON p.PurposeID = pur.ID 
    LEFT JOIN dbo.[Types] AS t ON p.TypeID = t.ID
    WHERE UPPER(ISNULL(l.LocationName,N'')) LIKE N'%' + UPPER(@location) + '%'
    AND UPPER(ISNULL(pur.PurposeName,N'')) LIKE N'%' + UPPER(@purpose) + '%'
    AND UPPER(ISNULL(t.TypeName,N'')) LIKE N'%' + UPPER(@type) + '%'
     )
GO

if you want to return only Projects where all of the criteria are met and an empty string in an input parameter is treated as a wildcard:

SELECT*FROM dbo.SearchProjects('','',''); -- Returns all recordsSELECT*FROM dbo.SearchProjects('north','',''); -- Returns all records with LocationName containing 'north'SELECT*FROM dbo.SearchProjects('','research',''); -- Returns all records with PurposeName containing 'research'SELECT*FROM dbo.SearchProjects('','','closed'); -- Returns all records with TypeName containing 'closed'SELECT*FROM dbo.SearchProjects('north','research',''); -- Returns all records with LocationName containing 'north' and PurposeName containing 'research'

This also removes any case-sensitivity when comparing input parameter values to field values in your tables. I would still use LEFT JOIN instead of an INNER JOIN just in case some project records might have faulty LocationID, PurposeID or TypeID values.

If you want to return Projects where any of the criteria from the input parameters are met (and not treat empty input parameters as wildcards when at least one input parameter contains a value), you could change the ANDs in the WHERE clause to ORs and pass NULL for any input parameters you don't wish to specify a value for:

ALTERFUNCTION SearchProjects (
    @location NVARCHAR(50),
    @purpose NVARCHAR(50),
    @type NVARCHAR(50))
RETURNSTABLEASRETURN
(
    SELECT  p.ProjectName, 
            p.Areas, 
            p.PaymentSystem, 
            p.ReceivedDate,    
            p.PropertyClassification, 
            p.ProjectImage,
            l.LocationName,
            pur.PurposeName,
            t.TypeName
    FROM dbo.Projects AS p 
    LEFTJOIN dbo.Locations AS l ON p.LocationID = l.ID      
    LEFTJOIN dbo.Purposes pur ON p.PurposeID = pur.ID 
    LEFTJOIN dbo.[Types] AS t ON p.TypeID = t.ID
    WHEREUPPER(ISNULL(l.LocationName,N'')) LIKE N'%'+UPPER(@location) +'%'ORUPPER(ISNULL(pur.PurposeName,N'')) LIKE N'%'+UPPER(@purpose) +'%'ORUPPER(ISNULL(t.TypeName,N'')) LIKE N'%'+UPPER(@type) +'%'
     )
GO


SELECT*FROM dbo.SearchProjects('','',''); -- Returns all recordsSELECT*FROM dbo.SearchProjects('north',NULL,NULL); -- Returns all records with LocationName containing 'north'SELECT*FROM dbo.SearchProjects(NULL,'research',NULL); -- Returns all records with PurposeName containing 'research'SELECT*FROM dbo.SearchProjects(NULL,NULL,'closed'); -- Returns all records with TypeName containing 'closed'SELECT*FROM dbo.SearchProjects('north','research',NULL); -- Returns all records with LocationName containing 'north' or PurposeName containing 'research'

NULLing unwanted input parameters is necessary here because calling the function with an empty string in any of the input parameters would result in all records being returned:

SELECT*FROM dbo.SearchProjects('north','','');
SELECT*FROM dbo.SearchProjects('north','research','');
SELECT*FROM dbo.SearchProjects('north','','closed');

Solution 2:

The joins don't look right to me. You didn't fully specify your schema so I'm guessing, but it would be quite unusual to have a situation where those joins are used.

In this sample I changed the columns specified for each of the joins, but I was guessing what the columns might be named in your tables.

AlterFUNCTIONSearchProjects (
    @location nvarchar(50),
    @purpose nvarchar(50),
    @type nvarchar(50))
 RETURNSTABLEASRETURN
(
 SELECT p.ProjectName, 
     p.Areas, 
     p.PaymentSystem, 
     p.ReceivedDate,    
     p.PropertyClassification, 
     p.ProjectImage
  FROM dbo.Projects As p 
  LEFT JOIN dbo.Locations As l ON p.LocationID = l.ID      
  LEFT JOIN dbo.Purpose ON p.PurposeID = Purpose.ID 
  LEFT JOIN dbo.Types As t ON p.TypeID = t.ID
  WHERE (l.LocationName like N'%'+ @location +'%'OR Purpose.PurposeName = N'%'+ @purpose +'%'OR t.TypeName like N'%'+ @type     +'%')
 )
 GOSELECT * FROMdbo.SearchProjects('',' ','');

Sorry if I missed something in the question or comments that shows this is not the answer...