CODEWITHSUNDEEP
sqlsql-server
Sohel Rana
Sohel Rana

Reputation: 191

SQL filter with different colum base on condition

i want to filter table based on condition. like-

   CREATE TABLE [dbo].[tblUserInfo](
   [Id] [int] NOT NULL,
   [TypeName] [int] NOT NULL,
   [Value] [decimal](18, 2) NULL,
   [Name] [nvarchar](250) NULL,
   ) 

 DECLARE @SearchText VARCHAR(250)='aa'

 SELECT *FROM tblUserInfo

@SearchText has text then filter by TypeName column, @SearchText is null then filter by Name column.

just for example.

How could i achieve that? Thanks in advance.

Upvotes: 0

Views: 57

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37337

Your where clause should look like this:

where (@SearchText is null and Name = '.....') --filter by Name
   or (@SearchText is not null and TypeName = '.....') --filter by TypeName

Upvotes: 3

Sohel Rana
Sohel Rana

Reputation: 191

DECLARE @SearchText VARCHAR(250)='aa'

SELECT *FROM tblUserInfo 
WHERE CASE WHEN @SearchText IS NULL THEN Name ELSE 'a' END = CASE WHEN @SearchText IS NULL THEN 'hehe' ELSE 'a' END
AND 
(
    CASE WHEN @SearchText IS NOT NULL THEN TypeName ELSE 1 END = CASE WHEN @SearchText IS NOT NULL THEN 2 ELSE 1 END
    OR 
    CASE WHEN @SearchText IS NOT NULL THEN TypeName ELSE 1 END = CASE WHEN @SearchText IS NOT NULL THEN 3 ELSE 1 END
 )

I tried like this, is there any better solution?

Upvotes: 0

Related Questions