CODEWITHSUNDEEP
sql-server
naren
naren

Reputation: 1

How to add column dynamically in where clause

I want to include column in where clause depending on the condition. e.g select * From emp where id=7, and if(code is not null) then code=8;

how can i do this in sql server

Upvotes: 0

Views: 1555

Answers (7)

DkAngelito
DkAngelito

Reputation: 1187

Well,

I don't know if i understood your question, but i guess that you want to include the value of the code column in the results.

If i'm right it can be done in the select part instead of the where clause. i. e.

Select ..., case when code is not null then 8 else code end as code from emp where id = 7

The other interpretation is that you want to filter rows where code <> 8,that would be

Select * from emp where id = 7 and (code is null OR code = 8)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453940

If code is a column rather than a variable the query in your question would be rewritten as follows.

SELECT * 
FROM emp 
WHERE id=7 AND (code IS NULL OR code=8)

Upvotes: 1

JAEP
JAEP

Reputation: 383

You can use COALESCE function.

Upvotes: 0

ZeroKool
ZeroKool

Reputation: 20

here is the example: 

declare @SQL varchar(500)
declare @var1 int
set int = 1
set @SQL = 'Select * from ABC Where 1 = 1'

if(@var1 = 1)
set @SQL + @SQL ' And column1 = ' @var1

exec(@SQL)

Upvotes: 0

ZeroKool
ZeroKool

Reputation: 20

Also use this in case of Null value in @var1.

Select * from ABC where Column1 = isNull(@var1, Column1)

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

If I understand you correct, you could make use of COALESCE.

COALESCE()

Returns the first nonnull expression among its arguments.

SQL Statement

SELECT  *
FROM    emp
WHERE   id=7
        AND code = COALESCE(@code, code)

Upvotes: 2

Randy Minder
Randy Minder

Reputation: 48522

You'll probably have to create a query dynamically, as a string, and then use the Execute method to actually execute it. This approach has some potentially optimization issues, but it's commonly done. You might wan to Google T-SQL Dynamic Query, or something like that.

Upvotes: 0

Related Questions