Use where clause conditional in SQL

Question

I want to use where clause in TableName conditional Eg. When my variable @col1 is blank then I dont want to use where with @col1 and it should check for @col2 If @col2 is also blank then return all records. Or else check with their respective conditions/values inside variables.

declare @col1 nvarchar(50)=''
declare @col2 nvarchar(50)=''
select * from TableName
where
(isnull(@col1 ,'')!='' AND col1 =@col1 )
OR
(isnull(@col2 ,'')!='' AND col2 =@col2 )

This returns no record when @col1 or @col2 is blank.

Instead , when both are blank it should return all records, If any one of them contains any value then it should go for that condition.

Edit : Dynamic Sql is not allowed

[The “Kitchen Sink” Procedure](http://sqlsentry.tv/the-kitchen-sink-procedure/) — Lukasz Szozda, Jun 08 '18 at 06:47
@CSharper Do you mean Dynamic-SQL at all? You could write safe dynamic SQL - but you need to use parameters instead of hardcoding values. — Lukasz Szozda, Jun 08 '18 at 06:51
Possible duplicate of [How can I use optional parameters in a T-SQL stored procedure?](https://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure) — EzLo, Jun 08 '18 at 06:57
Why is dynamic SQL not allowed? Is this homework or something? — MJH, Jun 08 '18 at 07:35

George Menoutis · Accepted Answer · 2018-06-08T06:56:38.130

1

Try:

declare @col1 nvarchar(50)=''
declare @col2 nvarchar(50)=''
select * from TableName
where
(isnull(@col1 ,'')='' OR col1 =@col1 )
OR
(isnull(@col2 ,'')='' OR col2 =@col2 )
OPTION (RECOMPILE)

This is the way to handle dynamic conditions without dynamic SQL

edited Jun 08 '18 at 06:56

answered Jun 08 '18 at 06:51

George Menoutis

6,894
3
19
43

2

And the performance will be really poor, especially when more condition will appear. Plus `isnull(@col1 ,'')=''` should be `@col1 IS NULL` – Lukasz Szozda Jun 08 '18 at 06:53
1

ah, you're right, I forgot the plan hint. let me look for it – George Menoutis Jun 08 '18 at 06:54
he obviously wants '' to be treated as null – George Menoutis Jun 08 '18 at 07:00

score 1 · Answer 2 · answered Jun 08 '18 at 08:31

You can trick this using LIKE

declare @col1 nvarchar(50)=''
declare @col2 nvarchar(50)=''
select * from TableName
where
( col1 LIKE ISNULL(@col1, '%' )
AND
( col2 LIKE ISNULL(@col2, '%' )

when @variable is null, it will set to filter LIKE '%' which return all values

And i changed to logic into AND, because, with assumption, if both @variable have values, then shouldn't it be display data that qualify both values? Which leads to AND logic. This may need to be consider

score 1 · Answer 3 · answered Jun 08 '18 at 11:48

1

create dynamic query for this

DECLARE @COL1 NVARCHAR(50)=''

DECLARE @COL2 NVARCHAR(50)=''

DECLARE @SQL NVARCHAR(500)

SET @SQL='SELECT * FROM TABLENAME'

IF(@COL1!='')
BEGIN

SET @SQL=@SQL+' WHERE COL1='+@COL1+''

END EXEC @SQL

answered Jun 08 '18 at 11:48

pankaj singh

95
10

Use where clause conditional in SQL

3 Answers3