CODEWITHSUNDEEP
sqlsql-serverwhere-clausesql-in
Ryan Jacques
Ryan Jacques

Reputation: 3

how to use Wildcard with `WHERE IN(' ')` statment

I have a WHERE IN ('') statement, if nothing is entered, how do I select ALL?

Upvotes: 0

Views: 921

Answers (2)

StepUp
StepUp

Reputation: 38189

If you want to select all values, if there is no value for a variable, then just use ISNULL function( will have an index scan):

WHERE yourField IN (ISNULL(@yourNullVariable, yourField))

Let me show an example:

DECLARE @table TABLE
(
    FooDate DATETIME,
    FooBar VARCHAR(50) 
)

INSERT INTO @table
(
    FooDate,
    FooBar
)
VALUES
(   GETDATE(), -- FooDate - datetime
    'A'         -- FooBar - varchar(50)
    )
, ('2019-02-18', 'AA')
, ('2019-01-18', 'B')
, ('2019-01-18', 'BB')

DECLARE @fooVariable VARCHAR(50) = NULL
SELECT * FROM @table tbl
WHERE  tbl.FooBar = ISNULL(@fooVariable, tbl.FooBar)

OUTPUT:

FooDate                    FooBar
2019-02-18 16:37:20.920      A
2019-02-18 00:00:00.000      AA
2019-01-18 00:00:00.000      B
2019-01-18 00:00:00.000      BB

Or use dynamic SQL( will have an index seek):

DECLARE @fooVariable VARCHAR(50) = NULL
DECLARE @sql NVARCHAR(max), @where NVARCHAR(255);

SET @sql = N'SELECT * FROM Yourtable tbl'
IF @fooVariable IS NOT NULL
    BEGIN 
        SET @where = ' tbl.FooBar = ' + @fooVariable
        SET @sql = CONCAT(@sql, @where)
    END


EXEC SP_EXECUTESQL @SQL

Upvotes: 0

Marcelo Vismari
Marcelo Vismari

Reputation: 1179

Depend on database you are working. But you can do something like this:

-- SQLServer
DECLARE @in varchar(20) = ''
SELECT * FROM <table> WHERE (@in = '' or <field> IN (''))

-- OR
IF @in = '' 
    SELECT * FROM <table>
ELSE
    SELECT * FROM <table> WHERE <field> IN ('')

Upvotes: 2

Related Questions