showing 0 result while searching multi keyword string in table column using asp classic

i am searching multi keyword string from two table's column using UNION. it show result if table's column contain all string keywords otherwise it show 0 result.

How to check search string with "or" operator in statement so it will show result for matched keyword from search string and rest will be ignored as it doesn't match.

please help me to resolve this issue.

one more doubt i have that is: will it show result sorting by most matching keywords? mean show the closest matches first?

my code is as below given:

<%
Dim SearchWord, arrKeyWords, Cnt, tsearch, i
SearchWord = trim(request("searcha"))
SearchWord = replace(SearchWord, " and ", " ")
SearchWord = replace(SearchWord, " in ", " ")
SearchWord = replace(SearchWord, " or ", " ")
arrKeyWords = Split(SearchWord ," ") 
%>
<%
If IsArray(arrKeyWords) Then
For i = 0 To (UBound(arrKeyWords)-1)

Dim objRSsg, objCmdsg, strsg
Set objCmdsg  = Server.CreateObject("ADODB.Command")
Set objRSsg  = Server.CreateObject("ADODB.Recordset")

dim countItem, numPerPage, page, totalpages, totalRecs
countItem = 0
numPerPage = 50

objRSsg.cursorlocation = 3
if request("page") = "" then
page=1
else
page=CLng(request("page"))
end if
strsg = "select rProd_name, r_id, (case when rProd_name like '%' +  ? + '%' then 1 else 0 end) as [priority] from reseller_prod where rProd_name LIKE '%' + ? + '%' union select Prod_name, '' as r_id, (case when Prod_name like '%' +  ? + '%' then 2 else 0 end) as [priority] from product where Prod_name LIKE '%' +  ? + '%' order by [priority] desc"

With objCmdsg
.ActiveConnection = MM_connDUdirectory_STRING
.CommandText = strsg
.CommandType = adCmdText
.Parameters.Append(.CreateParameter("@sa1", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa2", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa3", adVarChar, adParamInput, 1000))
.Parameters.Append(.CreateParameter("@sa4", adVarChar, adParamInput, 1000))

.Parameters("@sa1").Value = arrKeyWords(i)
.Parameters("@sa2").Value = arrKeyWords(i)
.Parameters("@sa3").Value = arrKeyWords(i)
.Parameters("@sa4").Value = arrKeyWords(i) 

end with 
objRSsg.Open objCmdsg, , 1, 2
next
end if
....
....
%>