T-SQL查询参数使用方法咨询，附现有业务查询代码示例

核心逻辑 #

将原SQL中WHERE条件里硬编码的常量值'HR_O28'替换为参数占位符，执行查询时再动态传入参数值，既可以避免SQL注入风险，也能灵活适配不同的查询条件。

不同数据库的SQL改造示例 #

不同数据库的参数占位符规则不同，对应改造后的WHERE条件如下：

• MySQL：fa.ssfam_code = ?，完整SQL如下：

SELECT 
    fa.fam_label_!$ AS name,
    fa.dom_id AS id_lvl_1,
    fa.dom_code AS commodity_level_1,
    fa.fam_id AS id_lvl_2,
    fa.fam_code AS commodity_level_2,
    fa.sfam_id AS id_lvl_3,
    fa.sfam_code AS commdotiy_level_3,
    fa.ssfam_id AS id_lvl_4,
    fa.ssfam_code AS commodity_level_4
FROM 
    x_fam_all AS fa 
WHERE 
    fa.ssfam_code = ?

• PostgreSQL：fa.ssfam_code = $1
• SQL Server：fa.ssfam_code = @ssfam_code
• Oracle：fa.ssfam_code = :ssfam_code

常见使用场景操作方式 #

开发代码调用示例 #

• Python（pymysql为例）

import pymysql

# 建立数据库连接
conn = pymysql.connect(host='数据库地址', user='用户名', password='密码', database='库名')
cursor = conn.cursor()

sql = """
SELECT 
    fa.fam_label_!$ AS name,
    fa.dom_id AS id_lvl_1,
    fa.dom_code AS commodity_level_1,
    fa.fam_id AS id_lvl_2,
    fa.fam_code AS commodity_level_2,
    fa.sfam_id AS id_lvl_3,
    fa.sfam_code AS commdotiy_level_3,
    fa.ssfam_id AS id_lvl_4,
    fa.ssfam_code AS commodity_level_4
FROM 
    x_fam_all AS fa 
WHERE 
    fa.ssfam_code = %s
"""
# 按顺序传入参数即可，多个参数依次放在元组中
cursor.execute(sql, ('HR_O28',))
result = cursor.fetchall()

• Java（JDBC为例）

String sql = "SELECT fa.fam_label_!$ AS name, fa.dom_id AS id_lvl_1, fa.dom_code AS commodity_level_1, fa.fam_id AS id_lvl_2, fa.fam_code AS commodity_level_2, fa.sfam_id AS id_lvl_3, fa.sfam_code AS commdotiy_level_3, fa.ssfam_id AS id_lvl_4, fa.ssfam_code AS commodity_level_4 FROM x_fam_all AS fa WHERE fa.ssfam_code = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
// 给第一个占位符赋值，参数类型和字段类型匹配即可
pstmt.setString(1, "HR_O28");
ResultSet rs = pstmt.executeQuery();

数据分析工具使用 #

如果是在Tableau、FineBI等BI工具，或者Navicat等数据库管理工具中使用，直接在查询编辑界面创建对应类型的参数，将fa.ssfam_code的过滤值绑定到创建好的参数即可。

注意事项 #

• 禁止用字符串拼接的方式组装参数值，会存在SQL注入风险
• 传入参数的类型需要和ssfam_code字段的数据库类型匹配，避免隐式转换导致索引失效、查询变慢的问题

内容的提问来源于stack exchange，提问作者Patryk