How to use IN clause in sql server so that it selects everything if nothing is provided for its list argument? Assume I have a sql query like this

SELECT * FROM TABLE where country in ('USA', 'UK')


This query works as expected that is it selects all rows where country column is either 'USA' or 'UK'. Now however, if I run this query

SELECT * FROM TABLE where country IN ()


its a syntax error. So I change it to run something like this

SELECT * FROM TABLE where country IN ('')


My goal here is to use jdbcTemplate of Spring framework to dynamically run this sql query. However I am not sure what my inputs are going to be. So I want to be able to pass the countries like 'USA' or 'UK' and get only rows related to those OR pass nothing and get ALL rows. So how do I get ALL rows if nothing is passed into IN clause?
So how do I get ALL rows if nothing is passed into IN clause?


You have to use dynamic sql.

e.g. Below is a simple example to get you started.

set nocount on
declare @sqltext nvarchar(max) = N''
declare @countryInput varchar(30)  

select @sqltext = N'
    select * -- good practice to use explicit column names 
        from dbo.Table
        where 1 =1 '
        + case when @countryInput is null or @countryInput ='' then ''
        else ' and country in (''USA'',''UK'')'
        end

        print @sqltext