Condition in Where Clause - SQL Server

Question

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

Answer 1

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.

Answer 2

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.