SQL Server Error Messages - Msg 497

Error Message

Server: Msg 497, Level 15, State 1, Line 1
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

Causes

The TABLESAMPLE clause, introduced in SQL Server 2005, limits the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows. The syntax for the TABLESAMPLE clause is as follows:

TABLESAMPLE ( sample_number [PERCENT | ROWS] ) [ REPEATABLE (repeat_seed) ]

One limitation of the TABLESAMPLE clause is that both the sample_number and repeat_seed parameters cannot be local variables; otherwise this error message is generated.

To illustrate, here are sample SELECT statements that will generate this error:

CREATE TABLE [dbo].[CallDetailRecord] ( 
    [CallDetailRecordID]        INT NOT NULL IDENTITY(1, 1),
    [SourceNumber]              VARCHAR(30),
    [DestinationNumber]         VARCHAR(30),
    [CallTime]                  DATETIME,
    [CallDuration]              INT
)

-- Using a local variable for the sample number of rows
DECLARE @RowCount INT
SET @RowCount = 100
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( @RowCount ROWS )

Msg 497, Level 15, State 1, Line 3
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

-- Using a local variable for sample percentage
DECLARE @PercentSample      DECIMAL(5, 2)
SET @PercentSample = 75.0
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( @PercentSample PERCENT )

Msg 497, Level 15, State 1, Line 3
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

-- Using a local variable for the repeat seed
DECLARE @RepeatSeed     INT
SET @RepeatSeed = 124
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 50 PERCENT ) REPEATABLE ( @RepeatSeed )

Msg 497, Level 15, State 1, Line 3
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

Solution / Work Around

One way to avoid this error is not to make use of local variables when specifying the sample number of rows, the sample percentage or the repeat seed. Using the examples above, the following SELECT statement will generate the desired output:

SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 100 ROWS )
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 75.0 PERCENT )
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 50 PERCENT ) REPEATABLE ( 124 )

If the number of sample rows or the sample percentage or even the repeat seed is really needed to be passed as a parameter or in a local variable, one way to get the desired result is with the use of dynamic SQL statements. The following scripts will generate the output where the number of sample rows, the sample percentage or the repeat seed is passed as a local variable:

-- Using a local variable for the sample number of rows
DECLARE @RowCount   INT
DECLARE @SQLStmt    VARCHAR(100)
SET @RowCount = 100
SET @SQLStmt = 'SELECT * FROM [dbo].[CallDetailRecord] 
                TABLESAMPLE ( ' + CAST(@RowCount AS VARCHAR(10)) + ' ROWS )'
EXECUTE ( @SQLStmt )

-- Using a local variable for sample percentage
DECLARE @PercentSample    DECIMAL(5, 2)
DECLARE @SQLStmt          VARCHAR(100)
SET @PercentSample = 75.0
SET @SQLStmt = 'SELECT * FROM [dbo].[CallDetailRecord] 
                TABLESAMPLE ( ' + CAST(@PercentSample AS VARCHAR(10)) + ' PERCENT )'
EXECUTE ( @SQLStmt )

-- Using a local variable for the repeat seed
DECLARE @RepeatSeed    INT
DECLARE @SQLStmt       VARCHAR(100)
SET @RepeatSeed = 124
SET @SQLStmt = 'SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 50 PERCENT ) 
                REPEATABLE (' + CAST(@RepeatSeed AS VARCHAR(10)) + ')'
EXECUTE ( @SQLStmt )