SQL stored procedure - table as parameter

Question

I have a database with different tables (all the same structure) where I'd like to run a stored procedure having a parameter that defines which table to query.

I can't seem to figure it out:

CREATE SCHEMA test;
GO

First I created a schema

CREATE TYPE DataType as TABLE (
    [datetime] [datetime] NULL,
    [testVar] [bigint] NULL)
   GO

Then I created the table type

USE [TestDataFiles]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [test].[testing]
(
    -- Add the parameters for the stored procedure here
    @datetime datetime,
    @t DataType READONLY

)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON


select top(10) * 
from @t
where [datetime] > @datetime                

END
GO

Then I created the stored procedure.

Exec test.testing @t = 'table.1', @datetime = '2017-01-01'

However when I call it I get the following error:

Msg 206, Level 16, State 2, Procedure test, Line 0 [Batch Start Line 0] Operand type clash: varchar is incompatible with DataType

Same happens with:

Exec test.testing @t = [table.1], @datetime = '2017-01-01'

I have seen an example where in the procedure between the begin and select you put something like:

INSERT INTO table.1
( datetime, testVar)

But table.1 (or table.2 etc as I have a list of tables) has data and I don't want to change it.

Unless I'm meant to create a dummy table like I did the TYPE?

The examples I've found online havent been useful.

Answer 1

To do that you will need to use dynamic SQL

The basic procedure is to build up a string that will hold the statement you will execute, then execute it

declare @SQL nvarchar(1000)
declare @t as nvarchar (1000)
set @t = 'MyTable'
set @Sql = 'Select * from ' + @t
exec sp_executesql @sql