SQL Server存储过程：如何在TABLESAMPLE中使用变量百分比？

SQL Server的TABLESAMPLE语法不允许直接使用变量作为百分比参数，必须传入常量值。要实现动态设置数据采样比例，最可靠的方式是使用动态SQL，通过拼接包含具体百分比值的查询语句来执行。

以下是修改后的存储过程，使用sp_executesql安全执行动态构建的查询，同时规避SQL注入风险：

ALTER PROCEDURE [dbo].[usp_HistoryDataSensor] 
    @EquipmentID int, 
    @tanggalfrom datetime, 
    @tanggalto datetime, 
    @shiftfrom int, 
    @shiftto int, 
    @sensorid int, 
    @percent int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @datefrom datetime,
            @dateto datetime,
            @tPercent int,
            @sql NVARCHAR(MAX),
            @params NVARCHAR(MAX)

    -- 确保百分比在有效范围（0-100）内，避免无效值报错
    SET @tPercent = CASE WHEN @percent BETWEEN 0 AND 100 THEN @percent ELSE 100 END;

    SELECT @datefrom = (SELECT StartTime FROM dbo.ufn_ShiftDateTime(@tanggalfrom, @shiftfrom)),
           @dateto = (SELECT EndTime FROM dbo.ufn_ShiftDateTime(@tanggalto, @shiftto))

    -- 定义参数映射，用于sp_executesql的参数化传递
    SET @params = N'@EquipmentID int, @sensorid int, @datefrom datetime, @dateto datetime';

    IF @sensorid <> -99
    BEGIN
        -- 构建非速度传感器的查询语句
        SET @sql = N'
            SELECT
                ss.dtCreatedAt AS [DateTime],
                ss.flSensorValues AS [Value]
            FROM 
                tabShiftSensor AS ss
            TABLESAMPLE (' + CAST(@tPercent AS NVARCHAR(3)) + ' PERCENT) WITH (NOLOCK) 
            WHERE 
                ss.EquipmentID = @EquipmentID
                AND ss.SensorDefID = @sensorid
                AND ss.dtCreatedAt BETWEEN @datefrom AND @dateto
            ORDER BY 
                ss.dtCreatedAt';
    END
    ELSE
    BEGIN
        -- 构建速度传感器的查询语句
        SET @sql = N'
            SELECT
                ss.dtCreatedAt AS [DateTime],
                ss.flSpeed AS [Value]
            FROM 
                tabShiftSensor AS ss
            TABLESAMPLE (' + CAST(@tPercent AS NVARCHAR(3)) + ' PERCENT) WITH (NOLOCK) 
            WHERE 
                ss.EquipmentID = @EquipmentID
                AND ss.dtCreatedAt BETWEEN @datefrom AND @dateto
            ORDER BY 
                ss.dtCreatedAt';
    END

    -- 执行动态SQL
    EXEC sp_executesql @sql, @params, 
        @EquipmentID = @EquipmentID, 
        @sensorid = @sensorid, 
        @datefrom = @datefrom, 
        @dateto = @dateto;
END

关键说明： #

• 先对@percent做范围校验，确保采样比例在0-100之间，避免无效值导致的语法错误。
• 使用sp_executesql而非直接拼接所有变量，将@EquipmentID、@sensorid等参数通过参数化方式传递，有效防止SQL注入。
• 仅将@tPercent转换为字符串后拼接到TABLESAMPLE子句中，因为该参数必须是常量，且经过范围校验后安全可控。

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