TSQL WHERE IN 技巧

有一個陣列內資料可能是Array(A1,A2,A3......),
想用這些資料做為條件用於SQL查詢

set rs=server.createobject("adodb.recordset")
sql = "select * from ProData where A1 = Array(0) Or A1 = Array(1) Or A1 = Array(2) ........"
SQL應該怎麼敘述呢?
有以下兩個方法:
1.

<%
Dim myArray(2)
myArray(0) = "Johnson"
myArray(1) = "Tom"
myArray(2) = "Marry"
Dim sqlMain
sqlMain = "select * from ProData where 1 = 1 "

for each item in myArray

sqlMain = sqlMain  & " and A = '"  & item & "'"
next
response.write(sqlMain)
%>

執行結果:


select * from ProData where 1 = 1 and A = 'Johnson' and A = 'Tom' and A = 'Marry'


2.

<% 
'數字型別 
num = Array(1,3,9,15) 
sql = "select * from table where ID in (" & join(num,",") & ")" 
response.write sql 

response.write "<br>" 

'文字型別 
num2 = Array("A001","A009","A015") 
sql2 = "select * from table where ID in ('" & join(num2,"','") & "')" 
response.write sql2 
%>

執行結果:


select * from table where ID in (1,3,9,15)

select * from table where ID in ('A001','A009','A015')