CODEWITHSUNDEEP
sqlsql-serversql-server-2008sqlite
Ikram Khan
Ikram Khan

Reputation: 89

SQL Server, Filter by using selective columns

It looks simple but I need this. I have a table tbl(col1,col2,col3). I want to search the table in a procedure in a way as when I pass value for only col1, query should only consider the value of col1 in where clause. Similarly when I pass value for col1 and col2 it should only consider these two columns in where clause and ignore col3. I want to use only a single select query e.g 

'select * from tbl where col1=@col1 and col2=@col2 and col3=@col3'

My original proc is:

create proc GetProdData
@source nvarchar(20),
@subsource nvarchar(20)
as
begin
select * From ProductionData 
where Source=@source and SubSource=@subsource
end

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you have indexes, the more efficient way is to generate dynamic SQL.

Otherwise:

where (col1 = @col1 or @col1 is null) and
      (col2 = @col2 or @col2 is null) and
      (col3 = @col3 or @col3 is null)

Upvotes: 1

Related Questions