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.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With