Better way for multiple optional search in sql Store Procedure

Better way for multiple optional search in sql Store Procedure

i assigned multiple optional search to my junior resources a few weeks ago recently i gone through the code standards I'm bit surprised every one is different :) . they gone through row-filtering,Dynamic sql,where case in sql,Table search,IF IS NULL in sql ...
Now i really need to pick a better and clean stuff.will go through one by one with draw back and advantage (my view)

row-filtering ,Table search :::Both are Simple and Easy achieved, by on page weight will be so high, not good.Entire data will be taken to page and filter @ their.

Dynamic sql ::: Fear of vulnerability,sql injection is just easy as nothing.More hard code are also needed sometimes.

WHERE CASE and IF IS NULL in sql::: pretty cool stuff for this criteria but lack of readability 

in such a case instead of WHERE CASE,IF IS NULL go with Boolean operator is simple more readable too...

A sample approach

DECLARE @AgcyName NVARCHAR(50),@state NVARCHAR(50)
SELECT @AgcyName = NULL
SELECT @state = 'a'
SELECT TOP 1000 [AgencyId]
              , [AgencyName]
              , [Address2]
              , [State]
FROM
    [dbo].[Agencies]
WHERE
    (@AgcyName IS NULL
    OR AgencyName LIKE '%' + @AgcyName + '%')
    AND (@state IS NULL
    OR [State] LIKE '%' + @state + '%')

 























Try to make always ur code more secure fast and readable...

 

If u had any trouble just ask, Happy to help u :) 
Stay Tune... 
Have a nice day... 'N happy Coding :)