Like Gail, I don't think this expression is going to have much impact on performance. Maintainability is another story - updating n queries to account for product changes leaves you wide open to errors. Put the code into an iTVF so you have a common code block for all of the queries which return 'gender' (I'd call it 'fitting' personally because 'youth' isn't a gender). Something like this:

CREATE FUNCTION IF_BrandItemFitting

(

@BrandName VARCHAR(20),

@ItemName VARCHAR(20)

)

RETURNS TABLE

AS

RETURN

(

SELECT [Fitting] = CASE

WHEN @BrandName IN ( 'brand1', 'brand2', 'brand3' )

AND LEFT(@ItemName, 1) = 'L'

THEN 'Ladies'

WHEN @BrandName IN ( 'brand1', 'brand2', 'brand3' )

AND LEFT(@ItemName, 1) = 'Y'

THEN 'Youth'

WHEN @BrandName IN ( 'brand1', 'brand2', 'brand3' )

--AND (LEFT(@ItemName, 1) <> 'Y' -- not strictly needed - see first 2 cases

--OR LEFT(@ItemName, 1) <> 'L')

THEN 'Adult'

WHEN @BrandName = 'brand4'

THEN 'Adult'

WHEN @BrandName = 'brand5'

AND RIGHT(@ItemName,1) IN ( 'B', 'Y' )

THEN 'Youth'

WHEN @BrandName = 'brand6'

AND RIGHT(@ItemName,1) = 'B'

THEN 'Youth'

WHEN @BrandName = 'brand6'

AND RIGHT(@ItemName,1) = 'L'

THEN 'Ladies'

ELSE NULL -- NULL, not ''

END

)

GO

Use it like this:

SELECT

IT.ItemName,

bf.fitting

FROM tblBrand br

INNER JOIN tblItem it

ON it.BrandId = br.BrandId

CROSS APPLY dbo.IF_BrandItemFitting (br.BrandName, it.ItemName) bf