CODEWITHSUNDEEP
sql-server
Sreedhar
Sreedhar

Reputation: 30025

Condition in Where Clause - SQL Server

How can I filter columns in where condition based on valued passed.

Ex: 

declare @number nvarchar(200)
set @number = '2ddafa3'

Here @number could be int or uniqueIdentifer

so in where clause how can i achieve something as below.

select * from IntegrationIDTransactions
where case ISNUMERIC(@number) 
            When 1  Then [TransactioniD] = @number
            else [TransactionDomainGuid] = @number

Thanks

Upvotes: 1

Views: 4077

Answers (2)

Andomar
Andomar

Reputation: 238086

This should work:

where @number = 
    case ISNUMERIC(@number) 
        when 1 then cast([TransactioniD] as varchar(200))
        else cast([TransactionDomainGuid] as varchar(200))
    end

Edited to add the cast. The cast has to be inside the CASE; SQL server requires that all paths of the case produce the same variable type.

Upvotes: 4

Remus Rusanu
Remus Rusanu

Reputation: 294277

You should avoid such constructs and instead have two queries, one for WHERE TransactionId and one for WHERE TransactionDomainGuid: 

if ISNUMERIC(@number)
  select * from IntegrationIDTransactions
  where [TransactioniD] = @number
else 
  select * from IntegrationIDTransactions
  else [TransactionDomainGuid] = @number

Even better, have the client do the IF and call the appropriate query. Writing queries like the one in your example is a performance killer, the result is unsargable and creates usually the worst execution plan for both cases.

Upvotes: 2

Related Questions