CODEWITHSUNDEEP
sql-server
SunMan
SunMan

Reputation: 53

Can we use operator in function?

Why using operator is not working for me in the function?

IF OBJECT_ID('Sales.fn_FilteredExtension','IF') IS NOT NULL DROP FUNCTION Sales.fn_FilteredExtension;
GO

CREATE FUNCTION Sales.fn_FilteredExtension
(
    @lowqty smallint,
    @highqty smallint
)
RETURNS TABLE AS RETURN
(
    SELECT unitprice,qty
           FROM Sales.OrderDetails where (qty <= @highqty) AND (qty >= @lowqty)
);
GO

When I run 

SELECT * FROM Sales.fn_FilteredExtension(20,15)

I get blank results but when I use 

IF OBJECT_ID('Sales.fn_FilteredExtension','IF') IS NOT NULL DROP FUNCTION Sales.fn_FilteredExtension;
GO

CREATE FUNCTION Sales.fn_FilteredExtension
(
    @lowqty smallint,
    @highqty smallint
)
RETURNS TABLE AS RETURN
(
    SELECT unitprice,qty
           FROM Sales.OrderDetails where qty BETWEEN @highqty AND @lowqty
);
GO

I get the output. What's the difference ?

Upvotes: 0

Views: 40

Answers (2)

Gerard
Gerard

Reputation: 301

I think you are getting your parameters mixed up and then reversed Your BETWEEN statement takes the lowest value as the first parameter and the higher value as the second parameter and so it shouldnt work if @lowqty actually receives the smallest value

Change "FROM Sales.OrderDetails where qty BETWEEN @highqty AND @lowqty"

To "FROM Sales.OrderDetails where qty BETWEEN @lowqty AND @highqty"

and then make sure @lowqty is actually the smallest value passed

Easy mix up given what the word BETWEEN means in common usage and then wrong values coming in

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

You need to pass correct arguments:

SELECT * FROM Sales.fn_FilteredExtension(20,15)
<=>
SELECT * FROM Sales.fn_FilteredExtension(15,20)

Upvotes: 2

Related Questions