SQL challenge ?

[Jenny84]

Once I found the product say in case one or two I don’t want to execute the query… How do I do that?

SELECT * FROM [dbo].[SupplierCatalogue]
(1 = CASE WHEN @pi_vProductCode IS NULL
THEN 1
ELSE
CASE WHEN [PRODTCDE] LIKE @pi_vProductCode + '%'
THEN 1
ELSE
CASE WHEN [DESCRPTN] = @pi_vProductCode
THEN 1
ELSE
CASE WHEN [DESCRPTN] LIKE @pi_vProductCode + '%'
THEN 1
ELSE
CASE WHEN (SELECT COUNT(SplitValue) FROM dbo.Split(@pi_vProductCode,' ') WHERE [DESCRPTN] LIKE '%' + SplitValue + '%'

AND LEN(SplitValue) > 1 ) > 0
THEN 1
ELSE
0
END
END
END
END
END)
ORDER BY [GOVPREXC] ASC

[Jenny84]

Rather than having all the "case" options, I thought why not just use a large query with multiple "where" options, return more than 1 row (and lots of columns) and deal with it in the front end. However, I cannot take that approach bcoz it involve lots of modification in front end. Any other way ?