CODEWITHSUNDEEP
sql-server
Mohammad Khandordi
Mohammad Khandordi

Reputation: 65

How to pass blank value as a parameter to a parameterized query and get all data?

I have a table with 5 columns and I want to define one parameterized select query to get some rows. 

ID    Date      LetterNumber    divider1    divider2
-----------------------------------------------------
1   01/01/2013    2654            work       sent
2   01/01/2013    8796            vacation   sent
3   05/01/2013    4638            home       recived
4   05/01/2013    4695            work       recived
5   05/01/2013    2356            child      recived
6   09/03/2013    1358            child      recived
7   20/06/2013    1976            work       sent
8   21/06/2013    2468            vacation   sent
9   21/06/2013    9764            home       sent
10  30/08/2013    5346            work       recived

I want to write ONE query and pass parameters for each divider1 , divider2 and get some data. but some times I may not use one of parameters. for example some times I need to get data by filtering divider2 and get just "sent" and some times filtering divider1 and divider2 to get LetterNumber having "work" value on divider1 and "sent" value on divider2. So, is there a way to sent a blank value as a parameter to unfiltered the field dynamically ?

Upvotes: 1

Views: 1986

Answers (3)

Abrar Khan
Abrar Khan

Reputation: 175

Use OR Condition for example

select * from yourTable where divider1=value1 OR divider2=value2

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

Assuming your parameters for divider1 and divider2 are @d1 and @d2 you can get your data like:

select
    ID,
    Date,
    LetterNumber,
    divider1,
    divider2
from your_table
where 
    (divider1 = @d1 or @d1 is null)
    and (divider2 = @d2 or @d2 is null)

In this case, if you passing null to any of parameters - it will not be used in filtering.

Upvotes: 3

Simone
Simone

Reputation: 1924

SELECT ID, Date, LetterNumber, divider1, divider2
FROM TableA
WHERE (@divider1 = divider1 OR @divider1 IS NULL OR @divider1 = '') AND
(@divider2 = divider2 OR @divider2 IS NULL OR @divider2 = '')

Upvotes: 2

Related Questions