Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses

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

The TABLESAMPLE clause, 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:

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 generate the error let's see the below example.

Example:

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 12
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 17
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 22
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

Solution :

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 )

To make it dynamic it can be done in Dynamic SQL statements.

-- 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 )

Similarly other example can be done like above.

The scale must be less than or equal to the precision

Msg 192, Level 15, State 1, Line 1
The scale must be less than or equal to the precision.

To avoid the error below points can be followed.

• The scale must be within the range of 0 and the value of the precision. This can easily be done by increasing the value of the precision to include the digits both before and after the decimal point
• In the case of the incorrect scale in the definition of a DECIMAL or NUMERIC data type in column of a table, simply increase the size of the precision to include the digits both before and after the decimal point.

Try to find out the error:

DECIMAL and NUMERIC are numeric data types that have fixed precision and scale. When maximum precision is used, which is 38, valid values are from -10^38 through 10^38 – 1. NUMERIC data type is functionally equivalent to DECIMAL data type. The syntax for declaring a local variable or a column as DECIMAL or NUMERIC data type is as follows:

DECIMAL ( p [, s] )
NUMERIC ( p [, s] )

Precision (p) is the maximum number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

The optional scale (s) is the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through the value of the precision (p). Scale can only be specified if precision is specified. The default scale is 0.

Given the definition of the precision and scale of a DECIMAL or NUMERIC data type, this error message will be encountered if the specified scale is greater than the precision when defining a local variable.

DECLARE @Pi    DECIMAL(1, 6) -- 3.141592

Msg 192, Level 15, State 1, Line 1

The scale must be less than or equal to the precision.

DECLARE @Latitude    DECIMAL(2, 6)
DECLARE @Longitue    DECIMAL(3, 6)

Msg 192, Level 15, State 1, Line 2

The scale must be less than or equal to the precision.

Msg 192, Level 15, State 1, Line 3

The scale must be less than or equal to the precision.

A different error message will be encountered when the scale is greater than the precision when defining a DECIMAL or NUMERIC column in a table:

CREATE TABLE [dbo].[Product] ( 
    [ProductID]      INT,
    [ProductName]    VARCHAR(100),
    [Width]          DECIMAL(4, 6),
    [Length]         DECIMAL(4, 6),
    [Height]         DECIMAL(4, 6)
)

Msg 183, Level 15, State 1, Line 8

The scale (6) for column 'Width' must be within the range 0 to 4.

To avoid this error, as the error message suggests, the scale must be within the range of 0 and the value of the precision. This can easily be done by increasing the value of the precision to include the digits both before and after the decimal point.
Here’s an updated version of the scripts earlier that fixes the issue:

DECLARE @Pi    DECIMAL(7, 6) -- 3.141592
DECLARE @Latitude    DECIMAL(8, 6) –- 2 Digits to the left and 6 digits to the right.
DECLARE @Longitue    DECIMAL(9, 6) –- 3 Digits to the left and 6 digits to the right.

In the case of the incorrect scale in the definition of a DECIMAL or NUMERIC data type in column of a table, simply increase the size of the precision to include the digits both before and after the decimal point.

CREATE TABLE [dbo].[Product] ( 
    [ProductID]      INT,
    [ProductName]    VARCHAR(100),
    [Width]          DECIMAL(10, 6),
    [Length]         DECIMAL(10, 6),
    [Height]         DECIMAL(10, 6)
)