CODEWITHSUNDEEP
sqlsql-server
Null Head
Null Head

Reputation: 2941

How to filter rows based on input param in sql?

I am having big time problem writing this query.

If my sample data looks like this ...

id   Fruit name     Group      Type
-----------------------------------------
1    Orange         Citrus     null
2    Mango          Sweet      null
3    Red chilly     Hot        null
4    Green chilly   Hot        null
5    Green chilly   Hot        Organic 1
6    Green chilly   Hot        Organic 2

I would like to create a stored procedure which accepts a @FoodType parameter.

In my table, FruitName and Type columns can potentially make a compound unique key, but I did not create one though.

What's the best approach to write this kind of queries?

Update:
When 'Organic 2' is passed, since Type is not defined for row 4, row 6 takes precedence over row 4.

Upvotes: 0

Views: 420

Answers (2)

marc_s
marc_s

Reputation: 754488

Try this if you're on SQL Server 2005 or newer (you didn't mention that...):

CREATE PROCEDURE dbo.GetFoods(@FoodType VARCHAR(20))
AS
    ;WITH Data AS
    (
        SELECT
            id, FruitName, GroupName, FruitType,
            RowNo = ROW_NUMBER() OVER (PARTITION BY FruitName ORDER BY FruitType DESC)
        FROM dbo.Fruits
        WHERE (FruitType IS NULL OR FruitType = @FoodType)
    )
    SELECT 
        id, FruitName, GroupName, FruitType
    FROM Data
    WHERE RowNo = 1

This seems to do what you're looking for in my case.

If you pass in NULL, you get back rows 1, 2, 3 and 4

EXEC dbo.GetFoods @FoodType = NULL

enter image description here

and if you call it using Organic 2, you get back:

EXEC dbo.GetFoods @FoodType = 'Organic 2'

enter image description here

Upvotes: 1

Gonzalo.-
Gonzalo.-

Reputation: 12672

Your question is not clear; anyway, considering that you want to return 1,2, 3, 4 and 6 when 'Organic 2' is passed, you could creathe this stored. It will returns the fields where is NULL, and also, if the parameter is not NULL, will filter with that criteria.

create procedure dbo.YourProcedureName
(
@FoodType varchar(20)
)
AS
BEGIN
select
id,Fruit, name, Group,Type 
from yourTable where Type IS NULL or Type = @FoodType 
END

Upvotes: 0

Related Questions