Tablesample Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses

Question

I have this stored procedure

ALTER PROCEDURE [dbo].[usp_HistoryDataSensor] 
    @EquipmentID int, 
    @tanggalfrom datetime, 
    @tanggalto datetime, 
    @shiftfrom int, 
    @shiftto int, 
    @sensorid int, 
    @percent int
AS
BEGIN
    DECLARE @datefrom datetime,
            @dateto datetime,
            @tPercent int

    SET @tPercent = @percent

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

    -- SELAIN SENSOR SPEED
    IF @sensorid <> -99
    BEGIN
        SELECT
            ss.dtCreatedAt AS [DateTime],
            ss.flSensorValues AS [Value]
        FROM 
            tabShiftSensor AS ss
        TABLESAMPLE (@tPercent PERCENT) WITH (NOLOCK) 
        WHERE 
            ss.EquipmentID = @EquipmentID
            AND ss.SensorDefID = @sensorid
            AND ss.dtCreatedAt BETWEEN @datefrom AND @dateto
        ORDER BY 
            ss.dtCreatedAt
    END
    ELSE -- SENSOR SPEED
    BEGIN
        SELECT
            ss.dtCreatedAt AS [DateTime],
            ss.flSpeed AS [Value]
        FROM 
            tabShiftSensor AS ss
            TABLESAMPLE (@tPercent PERCENT) WITH (NOLOCK) 
        WHERE 
            ss.EquipmentID = @EquipmentID
            AND ss.dtCreatedAt BETWEEN @datefrom AND @dateto
        ORDER BY 
            ss.dtCreatedAt
    END
END

When I execute the stored procedure, I get an error:

Procedure usp_HistoryDataSensor, Line 28 [Batch Start Line 0]
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

How to put variable percent into the tablesample, so I can set the percent of table sample?

Thanks

Answer 1

You need to use dynamic SQL to inject the percent. This will also simplify the IF...ELSE....

Note that the rest of the parameters should be passed through properly, not injected.

CREATE OR ALTER PROCEDURE [dbo].[usp_HistoryDataSensor] 
    @EquipmentID int, 
    @tanggalfrom datetime, 
    @tanggalto datetime, 
    @shiftfrom int, 
    @shiftto int, 
    @sensorid int = -99, 
    @percent int
AS

SET NOCOUNT ON;

DECLARE @datefrom datetime,
        @dateto datetime

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

DECLARE @sql nvarchar(max) = N'
SELECT
  ss.dtCreatedAt AS DateTime,
  ss.' + IIF(@sensorid <> -99, N'flSensorValues', N'flSpeed') + N' AS Value
FROM 
    tabShiftSensor AS ss
    TABLESAMPLE (' + CAST(@percent AS nvarchar(12)) + ' PERCENT)
WHERE 
    ss.EquipmentID = @EquipmentID' + IIF(@sensorid <> -99, N'
    AND ss.SensorDefID = @sensorid', N'') + N'
    AND ss.dtCreatedAt BETWEEN @datefrom AND @dateto
ORDER BY 
    ss.dtCreatedAt;
';

EXEC sp_executesql @sql,
  N'@datefrom datetime,
    @dateto datetime,
    @sensorid int,
    @EquipmentID int',
  @datefrom = @datefrom,
  @dateto = @dateto,
  @sensorid = @sensorid,
  @EquipmentID = @EquipmentID;