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.

Sounds like you just want to pass a table name and query that table. This would require Dynamic SQL, I believe is what you want. — SS_DBA, Oct 04 '17 at 13:24
I cant find any examples of this, a link or explanation would be appreciated. Essentially Im writing R code thats going to call SQL stored procedures across different tables. I don't want to create the same SP for n number of tables — Olivia, Oct 04 '17 at 13:30

score 5 · Accepted Answer · answered Oct 04 '17 at 13:32

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

score 3 · Answer 2 · answered Oct 04 '17 at 13:20

3

You have to pass parameter of type DataType. So, create variable of that type and pass it into stored procedure like

declare @table1 DataType
INSERT INTO @table1(datetime, testVar) values (..., ...)
Exec test.testing @datetime = '2017-01-01', @t = @table1

answered Oct 04 '17 at 13:20

Roman Marusyk

23,328
24
73
116

isnt insert into going to change my table though? These arent temp tables, they are fixed tables with data – Olivia Oct 04 '17 at 13:22
1

No, if you want pass table as parameter you must use this – Roman Marusyk Oct 04 '17 at 13:23
Think of a table variable as a temp table and it will make sense hopefully. I upvoted as this is the correct answer to the question, although it appears to be an XY problem to begin with. – Jacob H Oct 04 '17 at 13:24
this doesnt work, incorrect syntax near DataType. I used table1 in my example but I should have put table.1 (and table.2 etc). sorry – Olivia Oct 04 '17 at 13:27
@Olivia Here `@table1` is just name of variable. It can be `@somavariable` or whatever you want. After name of variable you have to specify the name of type. In this case (from your question) it is `DataType`. So, you create a variable, add data into variable and pass this variable as a parameter into stored procedure – Roman Marusyk Oct 04 '17 at 13:41

SQL stored procedure - table as parameter

2 Answers2