The TABLESAMPLE clause can only be used with local tables

Msg 494, Level 16, State 1, Line 1
The TABLESAMPLE clause can only be used with local tables.

The TABLESAMPLE clause cannot be applied to derived tables, tables from linked servers, and tables derived from table-valued functions, rowset functions, or OPENXML. TABLESAMPLE cannot also be specified in the definition of a view or an inline table-valued function.

If the TABLESAMPLE clause is applied to a table-valued function, such as the following SELECT statement, then this error message will be generated:

CREATE FUNCTION [dbo].[ufn_GetCustomersByZIPCode] ( @ZIPCode VARCHAR(5))
RETURNS TABLE
AS
RETURN (SELECT * FROM [dbo].[Customers]
        WHERE [ZIPCode] = @ZIPCode)
GO

SELECT * FROM [dbo].[ufn_GetCustomersByZIPCode] A TABLESAMPLE (100 ROWS)

Msg 494, Level 16, State 1, Line 8
The TABLESAMPLE clause can only be used with local tables.

If the purpose of using the TABLESAMPLE clause is to generate a random set of rows from a table, table-valued function or view, then instead of using the TABLESAMPLE clause, the TOP clause together with the ORDER BY NEWID() clause can be used in its place.

In the case of the inline table-valued function shown earlier, the following SELECT statement can be used in its place which will generate 100 random rows from the rows returned by the inline table-valued function:

SELECT TOP 100 *
FROM [dbo].[ufn_GetCustomersByZIPCode] ( '12345' ) A
ORDER BY NEWID()

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.