ASP 中 SQL 語句教程：SELECT、WHERE、LIKE、BETWEEN、聯集與多行組合完整指南

傳統 ASP (VBScript) 中構建動態 SQL 字串是核心技能。本文從基礎 SELECT 到條件篩選、模糊比對、範圍查詢、多條件邏輯與多行字串組合技巧，提供完整可複製範本。

1. 基礎 SELECT

Dim sql
sql = "SELECT * FROM Products"                           ' 全欄位
sql = "SELECT ProductName, UnitPrice FROM Products"      ' 指定欄位

2. WHERE 條件篩選

比較運算子

sql = "SELECT * FROM Products WHERE UnitPrice >= 199.95"
sql = "SELECT * FROM Products WHERE UnitPrice <> 19.95"
sql = "SELECT * FROM Products WHERE Discontinued = 0"    ' 數值型布林
sql = "SELECT * FROM Products WHERE CategoryID = 4"      ' 數值
sql = "SELECT * FROM Products WHERE ProductName = 'Chai'" -- 字串需單引號

LIKE 模糊比對

sql = "SELECT * FROM Products WHERE ProductName LIKE 'W%'"
sql = "SELECT * FROM Products WHERE SKU LIKE '1___5'"

NOT LIKE 反向比對

sql = "SELECT * FROM Products WHERE SKU NOT LIKE '1___5'"

BETWEEN 範圍查詢 (包含端點)

sql = "SELECT * FROM Products WHERE ProductID BETWEEN 1 AND 10"
-- 等價於
sql = "SELECT * FROM Products WHERE ProductID >= 1 AND ProductID <= 10"

3. 多條件邏輯組合 (AND / OR / NOT)

sql = "SELECT FirstName, LastName, Email FROM Customers " & _
      "WHERE Email IS NOT NULL " & _
      "AND (Purchases = 1 OR Purchases = 2) " & _
      "AND LastName LIKE 'A%'"

• AND 優先級高於 OR，務必用括號明確分組。
• 字串值在 VBScript 字串中需雙重引號："... WHERE Name = '" & var & "'"。

4. 多行字串組合技巧 — 可讀性與維護性

sql = "SELECT FirstName, LastName, Email, Phone "
sql = sql & "FROM Customers "
sql = sql & "WHERE FirstName LIKE 'A%' "
sql = sql & "AND Email IS NOT NULL "
sql = sql & "ORDER BY LastName, FirstName"

關鍵細節

• 每段結尾/開頭保留空格，避免 CustomersWHERE 這類語法錯誤。
• 複雜查詢建議改用 預存程序，ASP 僅呼叫 EXEC sp_GetCustomers 'A%'，杜絕動態 SQL 風險。

5. 參數化查詢 — 安全必要條件

Dim cmd, rs
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM Products WHERE CategoryID = ? AND UnitPrice > ?"
cmd.CommandType = 1 ' adCmdText
cmd.Parameters.Append cmd.CreateParameter("@CatID", 3, 1, , 4)      ' adInteger
cmd.Parameters.Append cmd.CreateParameter("@MinPrice", 6, 1, , 100) ' adCurrency
Set rs = cmd.Execute

• 絕不拼接使用者輸入，所有動態值皆用參數。
• 參數順序對應 SQL 中 ? 順序 (OLE DB 位置參數)。

6. 完整範例：商品搜尋頁

<%
Dim conn, cmd, rs, keyword, catID, minPrice, maxPrice, sql
keyword   = Trim(Request("keyword"))
catID     = Request("catID")
minPrice  = Request("minPrice")
maxPrice  = Request("maxPrice")

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("ConnStr")

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandType = 1

sql = "SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products WHERE 1=1 "
If keyword <> "" Then
    sql = sql & "AND ProductName LIKE ? "
    cmd.Parameters.Append cmd.CreateParameter("@kw", 202, 1, 50, "%" & keyword & "%")
End If
If catID <> "" Then
    sql = sql & "AND CategoryID = ? "
    cmd.Parameters.Append cmd.CreateParameter("@cat", 3, 1, , CLng(catID))
End If
If minPrice <> "" Then
    sql = sql & "AND UnitPrice >= ? "
    cmd.Parameters.Append cmd.CreateParameter("@min", 6, 1, , CDbl(minPrice))
End If
If maxPrice <> "" Then
    sql = sql & "AND UnitPrice <= ? "
    cmd.Parameters.Append cmd.CreateParameter("@max", 6, 1, , CDbl(maxPrice))
End If
sql = sql & "ORDER BY ProductName"

cmd.CommandText = sql
Set rs = cmd.Execute
%>
<table>
<tr><th>名稱</th><th>價格</th><th>庫存</th></tr>
<%
Do While Not rs.EOF
%>
<tr><td><%=rs("ProductName")%></td><td><%=rs("UnitPrice")%></td><td><%=rs("UnitsInStock")%></td></tr>
<%
    rs.MoveNext
Loop
rs.Close
conn.Close
%>
</table>

總結

1. 掌握 SELECT/WHERE/LIKE/BETWEEN/AND/OR/NOT 基礎語法。
2. 多行字串組合保留空格，邏輯分組加括號。
3. 永遠使用參數化查詢，拒絕字串拼接。
4. 複雜邏輯移至預存程序，ASP 只負責呼叫與呈現。

扎實的 SQL 構建能力，是 ASP 開發者通往安全、高效、可維護應用的基石。