Create and execute function in SQL Server

Question

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 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.

i want to search only not save or insert record into database — hashim
– hashim, Commented Nov 21, 2016 at 11:58
You use INNER JOINs that requires that each id has to be an entry in all four tables. If this is the case? Otherwise, use LEFT JOINs. — fbcomps
– fbcomps, Commented Nov 21, 2016 at 12:26
Check it without any condition. if it returns no data then you have change your join condition or check data in fyour main table is matching with other table or not — mansi
– mansi, Commented Nov 21, 2016 at 12:34
Projects.ProjectName like N'%'+ @location +'%' - is this correct? Looks like a typo. — IVNSTN
– IVNSTN, Commented Nov 21, 2016 at 12:59

3N1GM4 · Accepted Answer · 2016-11-21 15:05:07Z

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

answered Nov 21, 2016 at 15:05

3N1GM4

3,3463 gold badges22 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

hashim Over a year ago

it is worked thank very much, if you don't mind i want to contact with you on Skype or mail please .

3N1GM4 Over a year ago

Great, I'm glad you got the solution you were looking for. I don't hand out my contact details to people I don't know though I'm afraid - why did you want to email/Skype with me?

hashim Over a year ago

because i want to learn more from you and get more experience if you don't mind because i am beginner ?

3N1GM4 Over a year ago

I understand (I'm here to learn too!), but if you have more questions, just post them and then the whole SO community can help you, not just me (and trust me, there are literally thousands of others here much more knowledgable and experienced than I am).

hashim Over a year ago

ok, i want to add max and min for search criteria i want to insert max and min area for search criteria

|

mendosi · Accepted Answer · 2016-11-21 15:47:16Z

0

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.

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
  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     +'%')
 )
 GO

 SELECT * FROM dbo.SearchProjects('',' ','');

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

edited Nov 21, 2016 at 15:47

answered Nov 21, 2016 at 13:55

mendosi

2,0611 gold badge14 silver badges18 bronze badges

6 Comments

3N1GM4 Over a year ago

Doesn't this assume there is always a value in each of the LocationId, PurposeId and TypeID fields of the Projects table which matches a value in Locations.ID, Purpose.ID or Types.ID respectively? I think we should be using LEFT OUTER JOIN here instead of INNER JOIN. Unless hashim can confirm there will always be records in each of the joined tables for any given record in Projects.

3N1GM4 Over a year ago

And the call SELECT * FROM dbo.SearchProjects('',' ',''); will only return records where the associated PurposeName contains a space.

hashim Over a year ago

i update the question i changed the or operator with and operator

hashim Over a year ago

@3N1GM4 yes their is records in each of joined tables

3N1GM4 Over a year ago

@hashim: specifically, for every value of dbo.Projects.LocationID, dbo.Projects.PurposeID and dbo.Projects.TypeID, there are records with matching values in dbo.Locations.ID, dbo.Purposes.ID and dbo.Types.ID respectively? i.e. every single project has a Location, Purpose and Type recorded against it and all recorded Locations, Purposes and Types exist in dbo.Locations, dbo.Purposes and dbo.Types respectively?

|

Collectives™ on Stack Overflow

Create and execute function in SQL Server

2 Answers 2

6 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related