Please look at this link to old site because I am not able to post in new site due to error "Sorry new user can not mention more that 2 users in post". Not sure what that means. Moderators can help on that I hope. Thank you
Please consider formating your sql - makes it so much easier to read (it was really a bracked hell to get thru).
As to optimizing your where section, not much to do:
WHERE (@CUSparam = 113 OR dbo.E_Estimates.Customer = @CUSparam ) AND ((@CHKparam = 0 AND dbo.E_Estimates.[Assy Q] = -1 AND dbo.E_Estimates.E_Status IN (1,2) ) OR (@CHKparam = -1 AND dbo.E_Estimates.[Assy Q] IN (-1,-2) )) AND ((@FLDparam = '[Part Number]' AND dbo.[Part Numbers].PartNumber LIKE '%' + @TXTparam + '%' ) OR (@FLDparam = '[Description]' AND dbo.[Part Numbers].[Description] LIKE '%' + @TXTparam + '%' ))
You should look at placing correct indexes on your tables, to gain performance.
THANK YOU BITSMED! Indexes are there. I will do better formatting when I post