Create and execute function with more than parameters in SQL Server

Question

I have four tables:

1-dbo.Projects(id,ProjectName,Areas,PaymentSystem,location.id,purpose.id,types.id,etc)

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, also have textboxes for max and min values.

I will search with all fields and max, min values - I reached to this query but missed the query to get or filter max and min :

ALTER FUNCTION SearchProjects (
@location NVARCHAR(50),
@purpose NVARCHAR(50),
@type NVARCHAR(50),
@max nvarchar(50),
@min 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.Purpose 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) + '%'
AND p.Areas like @max
AND p.Areas like @min
 )
 GO
SELECT * FROM dbo.SearchProjects('','','','200','250');

I want to pass max and min values to function then return filter on area column with the values , if i have in area column values from 100 to 300 in each records i want pass min value 100 and max value 200 then get all result matched with this values — hashim
– hashim, Commented Nov 22, 2016 at 9:54
Possible duplicate of Create and execute function in SQL Server — IVNSTN
– IVNSTN, Commented Nov 22, 2016 at 9:56

Mahesh · Accepted Answer · 2016-11-22 10:01:07Z

1

From my understanding you want filter for your area column for max and min value which you can achieve by using BETWEEN

AND p.Areas BETWEEN @min AND @max

So your WHERE condition will look something like,

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) + '%'
    AND p.Areas BETWEEN @min AND @max

answered Nov 22, 2016 at 10:01

Mahesh

8,9422 gold badges37 silver badges55 bronze badges

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

4 Comments

hashim Over a year ago

i need something else when i call this line get all results : SELECT FROM dbo.SearchProjects('','','','','');

hashim Over a year ago

if i do not pass min or max value get all results

Mahesh Over a year ago

@hashim That will happen as you are not passing anything to filter out data. If you need anything else in please specify in question with details.

hashim Over a year ago

ok, no i tried to select without passing values no result return

Collectives™ on Stack Overflow

Create and execute function with more than parameters in SQL Server

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related