@result holds all the records from MasterBrandList, so why not just replace @result with MasterBrandList in dynamic part. The Like in the dynamic SQL will cause table scans as well. Lastly, why is searchvalue being applied to Brandname and BrandCode? Either they know the name (likely) or the code (hopefully internal PK and not likely), but to scan both is probably not a good idea.