Sql – Condition in Where Clause – SQL Server

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

Best Answer

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.

Related Topic