Various where types

DECLARE @TEMP TABLE
                    (
                                        Name VARCHAR(50),
                                        Sex  CHAR(1)    ,
                                        Age  INT
                    )

INSERT INTO @Temp
SELECT 'Babu', 'M', 31 UNION ALL
SELECT 'Krishna', 'M', 31 UNION ALL
SELECT 'Kanagu', 'M', 31 UNION ALL
SELECT 'rathinam', 'M', 31 UNION ALL
SELECT 'Kumarasamy', 'M', 31 UNION ALL
SELECT 'Suseela', 'F', 31 UNION ALL
SELECT 'Venkatesh', 'M', 31

DECLARE @Name VARCHAR(50),
        @Sex  CHAR(1),
        @Age  INT,
        @Type VARCHAR(50)

Using boolean logic

SELECT * FROM @Temp
WHERE 1 = 1
AND (@Name IS NULL OR Name = @Name)
AND (@Sex IS NULL OR Sex = @Sex)
AND (@Age IS NULL OR Age = @Age)

SET @Name = 'BABU'

SELECT * FROM @Temp
WHERE 1 = 1
AND (@Name IS NULL OR Name = @Name)
AND (@Sex IS NULL OR Sex = @Sex)
AND (@Age IS NULL OR Age = @Age)

SET @Name = 'BABU'
SET @Sex = 'BABU'

SELECT * FROM @Temp
WHERE 1 = 1
AND (@Name IS NULL OR Name = @Name)
AND (@Sex IS NULL OR Sex = @Sex)
AND (@Age IS NULL OR Age = @Age)

Using boolean logic

SET @Type = 'NAME'

SELECT * FROM @Temp
WHERE (@Type = 'NAME' AND Name = @Name)
OR (@Type = 'SEX' AND Sex = @Sex)
OR (@Type = 'AGE' AND Age = @Age)

Using the Case expression

SET @Name = NULL
SET @Sex = NULL
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = CASE WHEN @Name IS NOT NULL THEN @Name ELSE Name END
AND Sex = CASE WHEN @Sex IS NOT NULL THEN @Sex ELSE Sex END
AND Age = CASE WHEN @Age IS NOT NULL THEN @Age ELSE Age END

SET @Name = 'BABU'
SET @Sex = 'M'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = CASE WHEN @Name IS NOT NULL THEN @Name ELSE Name END
AND Sex = CASE WHEN @Sex IS NOT NULL THEN @Sex ELSE Sex END
AND Age = CASE WHEN @Age IS NOT NULL THEN @Age ELSE Age END

SET @Name = 'BABU'
SET @Sex = 'BABU'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = CASE WHEN @Name IS NOT NULL THEN @Name ELSE Name END
AND Sex = CASE WHEN @Sex IS NOT NULL THEN @Sex ELSE Sex END
AND Age = CASE WHEN @Age IS NOT NULL THEN @Age ELSE Age END

Using the ISNULL and NULLIF

SET @Name = NULL
SET @Sex = NULL
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = ISNULL(NULLIF(@Name, NULL), Name)
AND Sex = ISNULL(NULLIF(@Sex, NULL), Sex)
AND Age = ISNULL(NULLIF(@Age, NULL), Age)

SET @Name = 'BABU'
SET @Sex = 'M'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = ISNULL(NULLIF(@Name, NULL), Name)
AND Sex = ISNULL(NULLIF(@Sex, NULL), Sex)
AND Age = ISNULL(NULLIF(@Age, NULL), Age)

SET @Name = 'BABU'
SET @Sex = 'BABU'
SET @Age = NULL
SELECT * FROM @Temp
WHERE 1 = 1
AND Name = ISNULL(NULLIF(@Name, NULL), Name)
AND Sex = ISNULL(NULLIF(@Sex, NULL), Sex)
AND Age = ISNULL(NULLIF(@Age, NULL), Age)

Execution plans