Create and execute function in SQL Server

Question

I have four tables:

1. dbo.Projects
2. dbo.Locations (id, location name)
3. dbo.Purpose (id, Purposename)
4. dbo.Types (id, typname)

I have a search criteria, this criteria is filled with data from database tables: locations, purpose and types.

I want to create a function that returns table with search result from projects dependent on other tables. I have created one but it does not do what I need:

 ALTER FUNCTION SearchProjects
 (
     @location nvarchar(50),
     @purpose nvarchar(50),
     @type nvarchar(50)
 )
 RETURNS TABLE
 AS
 RETURN
(
      SELECT dbo.Projects.ProjectName, dbo.Projects.Areas,           
             dbo.Projects.PaymentSystem, dbo.Projects.ReceivedDate,    
             dbo.Projects.PropertyClassification, 
             dbo.Projects.ProjectImage
      FROM dbo.Locations INNER JOIN
      INNER JOIN dbo.Projects ON dbo.Locations.ID = dbo.Projects.ID      
      INNER JOIN dbo.Purpose ON dbo.Locations.ID = dbo.Purpose.ID 
      INNER JOIN dbo.Types ON dbo.Locations.ID = dbo.Types.ID
      WHERE (Projects.ProjectName like N'%'+ @location +'%' 
         and Purpose.PurposeName = N'%'+ @purpose +'%' 
         and Types.TypeName like N'%'+ @type     +'%')
 )
 GO
 SELECT * FROM dbo.SearchProjects('',' ','');

I'm new to SQL SERVER so any help is appreciated.

Answer 1

I would go with:

ALTER FUNCTION SearchProjects (
    @location NVARCHAR(50),
    @purpose NVARCHAR(50),
    @type NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    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 records

SELECT * 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:

ALTER FUNCTION SearchProjects (
    @location NVARCHAR(50),
    @purpose NVARCHAR(50),
    @type NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    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) + '%'
    OR UPPER(ISNULL(pur.PurposeName,N'')) LIKE N'%' + UPPER(@purpose) + '%'
    OR UPPER(ISNULL(t.TypeName,N'')) LIKE N'%' + UPPER(@type) + '%'
     )
GO


SELECT * FROM dbo.SearchProjects('','',''); -- Returns all records

SELECT * 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');