I have an old dynamic SQL query as below where the conditions in the where clause is appended dynamically based on the search text.
Example 1 : Search string 'AMX AC-DIN-CS3 Bracket'
SELECT * FROM Tx_Product
Where Fk_CompanyId=1
and (ModelNumber like '%AMX%' or Manufacturer like '%AMX%' or Category like '%AMX%' or [Description] like '%AMX%')
and (ModelNumber like '%AC-DIN-CS3%' or Manufacturer like '%AC-DIN-CS3%' or Category like '%AC-DIN-CS3%' or [Description] like '%AC-DIN-CS3%')
and (ModelNumber like '%Bracket%' or Manufacturer like '%Bracket%' or Category like '%Bracket%' or [Description] like '%Bracket%')
Here there are 3 And Clauses as there are 3 parts in the search string (separated by space(AMX,AC-DIN-CSS3 and Bracket).
Example 2 : Search string 'AMX AC-DIN-CS3'
SELECT * FROM Tx_Product
Where Fk_CompanyId=1
and (ModelNumber like '%AMX%' or Manufacturer like '%AMX%' or Category like '%AMX%' or [Description] like '%AMX%')
and (ModelNumber like '%AC-DIN-CS3%' or Manufacturer like '%AC-DIN-CS3%' or Category like '%AC-DIN-CS3%' or [Description] like '%AC-DIN-CS3%')
Here there are 2 And Clauses as there are 2 parts in the search string (AMX, AC-DIN-CS3).
I want to get rid of the Dynamic sql and create a single query that will get me the same result as above based on the search string but i am getting different result.
Equivalent of Example 1 But With extra results
DECLARE @SearchString NVARCHAR(MAX) = 'AMX AC-DIN-CS3 Bracket'
SELECT DISTINCT Prod.* FROM Tx_Product Prod
CROSS APPLY (SELECT DISTINCT part FROM [dbo].[SplitString] (@SearchString,'')) AS SearchParts
Where Fk_CompanyId = 1
AND (ModelNumber LIKE SearchParts.part OR Prod.Manufacturer LIKE SearchParts.part
OR Prod.Category LIKE SearchParts.part OR Prod.[Description] LIKE SearchParts.part)
Here i get the extra records like ( all the Products with manufacturer 'AMX' and all the products with ModelNumber 'AC-DIN-CS3').
Question: I want help in building the single query (Equv. of Example 1, that will give me the same result as of Example 1) based on the search string. So, this new Equiv query with tweaks will satisfy for both the example.
I have update the schema in SQL Fidddle As well.
Thanks in Advance for help.
Note: [dbo].[SplitString]
returns the distint parts of the string as a table. Here for 'AMX-AC-DIN-CS3'
it will return
'AMX'
'AC-DIN-CS3'