March 28, 2023 at 1:31 pm
I want to use a table variable in a merge statement using Dynamic SQL
I know that you cant reference table variables in Dynamic SQL as they are out of scope.
So instead ive elected to use a Table type but having problems.
My code as follows:
/****** Object: UserDefinedTableType [dbo].[ChannelReadingType] Script Date: 28/03/2023 14:18:44 ******/
CREATE TYPE [dbo].[ChannelReadingType] AS TABLE(
[ReadingDateTime] [datetime2](7) NULL,
[RawReading] [int] NULL,
[SIReading] [real] NULL
)
GO
CREATE TABLE [dbo].[t](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SerialNumber] [nvarchar](60) NULL,
[ChannelID] [int] NULL,
[Si] [decimal](10, 2) NULL,
[Raw] [decimal](10, 2) NULL,
[Conversion] [int] NULL,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
DECLARE@t [dbo].[ChannelReadingType];
DECLARE@ChannelReadingTableName NVARCHAR(500) = '[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F]'
DECLARE@ChannelID INT = 11
DECLARE@SQL NVARCHAR(4000)
-- insert test data into [dbo].[t]
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24073, N'939029', 14, CAST(53.60 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T13:11:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24074, N'939029', 14, CAST(53.01 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T13:21:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24075, N'939029', 14, CAST(52.42 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T13:31:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24076, N'939029', 14, CAST(53.79 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T13:41:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24077, N'939029', 14, CAST(50.74 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T13:51:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24078, N'939029', 14, CAST(54.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T14:01:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24079, N'939029', 14, CAST(53.23 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T14:11:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24080, N'939029', 14, CAST(52.74 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T14:21:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24081, N'939029', 14, CAST(53.99 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T14:31:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24082, N'939029', 14, CAST(50.80 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T14:41:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24083, N'939029', 14, CAST(54.02 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T14:51:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24084, N'939029', 14, CAST(52.42 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T15:01:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24085, N'939029', 14, CAST(53.68 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T15:11:31.000' AS DateTime))
GO
INSERT [dbo].[t] ([ID], [SerialNumber], [ChannelID], [Si], [Raw], [Conversion], [TimeStamp]) VALUES (24086, N'939029', 14, CAST(53.74 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-09-13T15:21:31.000' AS DateTime))
GO
SET @sql = 'MERGE ' + @ChannelReadingTableName + ' T '+ CHAR(13) + CHAR(10) +
' USING
(
SELECT
Sias SIReading,
[Raw]as RawReading,
[TimeStamp]as ReadingDateTime
FROM
@tt
WHERE
ChannelID = ' + '''' + CAST(@ChannelID as nvarchar(10)) + '''' + CHAR(13) + CHAR(10) +
--) AS ChannelReading ' + CHAR(13) + CHAR(10) +
' ) S on T.ReadingDateTime = S.ReadingDateTime ' + CHAR(13) + CHAR(10) +
' WHEN NOT MATCHED BY TARGET '+ CHAR(13) + CHAR(10) +
' THEN '+ CHAR(13) + CHAR(10) +
' INSERT (ReadingDateTime,SIReading, RawReading)'+ CHAR(13) + CHAR(10) +
' VALUES (S.ReadingDateTime,S.SIReading, S.RawReading); '+ CHAR(13) + CHAR(10) +
' ' + CHAR(13) +
' SET @Count= @@ROWCOUNT '
DECLARE @ParmDefinition NVARCHAR(500);
SET @ParmDefinition = N'@Count int OUTPUT';
PRINT @SQL
-- EXECUTE sp_executesql @SQL, N' @tt myTable READONLY', @t;
EXECUTE sp_executesql @SQL,@ParmDefinition, N' @tt [dbo].[ChannelReadingType] READONLY', @t,@Count = @rowcount OUTPUT;
When i run this i get the following error
Msg 1087, Level 15, State 2, Line 9
Must declare the table variable "@tt"
But i have declared the table variable here
EXECUTE sp_executesql @SQL,@ParmDefinition, N' @tt dbo.ChannelReadingType READONLY', @t,@Count = @rowcount OUTPUT;
what am I doing wrong?
March 28, 2023 at 1:44 pm
You can't parameterize a from clause. You could parameterize the WHERE clauses.
You could concatenate the table type into dynamic sql as described here: https://odetocode.com/articles/365.aspx
March 28, 2023 at 1:51 pm
You can't parameterize a from clause. You could parameterize the WHERE clauses.
You could concatenate the table type into dynamic sql as described her: https://odetocode.com/articles/365.aspx
Sorry I dont understand , what do you mean by concatenate the table type into dynamic sql?
March 28, 2023 at 1:57 pm
You can reference a temp table in dynamic SQL. Could you create a temp table and load it instead?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 28, 2023 at 2:24 pm
Can be done your logic is just a little off.
The "ParamDefinition" must be a continuous string of all params for the dynamic SQL
DECLARE @t [dbo].[ChannelReadingType];
DECLARE @ChannelReadingTableName NVARCHAR(500) = '[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F]'
DECLARE @ChannelID INT = 11
DECLARE @SQL NVARCHAR(4000)
DECLARE @CountOUT INT
SET @sql = 'MERGE ' + @ChannelReadingTableName + ' T '+ CHAR(13) + CHAR(10) +
' USING
(
SELECT
Sias SIReading,
[Raw]as RawReading,
[TimeStamp]as ReadingDateTime
FROM
@tt
WHERE
ChannelID = ' + '''' + CAST(@ChannelID as nvarchar(10)) + '''' + CHAR(13) + CHAR(10) +
--) AS ChannelReading ' + CHAR(13) + CHAR(10) +
' ) S on T.ReadingDateTime = S.ReadingDateTime ' + CHAR(13) + CHAR(10) +
' WHEN NOT MATCHED BY TARGET '+ CHAR(13) + CHAR(10) +
' THEN '+ CHAR(13) + CHAR(10) +
' INSERT (ReadingDateTime,SIReading, RawReading)'+ CHAR(13) + CHAR(10) +
' VALUES (S.ReadingDateTime,S.SIReading, S.RawReading); '+ CHAR(13) + CHAR(10) +
' ' + CHAR(13) +
' SET @Count= @@ROWCOUNT '
DECLARE @ParmDefinition NVARCHAR(500);
SET @ParmDefinition = N'@Count int OUTPUT, @tt [dbo].[ChannelReadingType] READONLY';
PRINT @SQL
-- EXECUTE sp_executesql @SQL, N' @tt myTable READONLY', @t;
EXECUTE sp_executesql @SQL,@ParmDefinition, @tt = @t,@Count = @CountOUT OUTPUT;
Can't test it fully as the "merge" table isn't provided as a create but that should do what you want it to do
March 28, 2023 at 3:15 pm
Can be done your logic is just a little off.
The "ParamDefinition" must be a continuous string of all params for the dynamic SQL
DECLARE @t [dbo].[ChannelReadingType];
DECLARE @ChannelReadingTableName NVARCHAR(500) = '[ChannelReading_939029_11_F0EA4CCA-3522-4175-AA53-83563F91235F]'
DECLARE @ChannelID INT = 11
DECLARE @SQL NVARCHAR(4000)
DECLARE @CountOUT INT
SET @sql = 'MERGE ' + @ChannelReadingTableName + ' T '+ CHAR(13) + CHAR(10) +
' USING
(
SELECT
Sias SIReading,
[Raw]as RawReading,
[TimeStamp]as ReadingDateTime
FROM
@tt
WHERE
ChannelID = ' + '''' + CAST(@ChannelID as nvarchar(10)) + '''' + CHAR(13) + CHAR(10) +
--) AS ChannelReading ' + CHAR(13) + CHAR(10) +
' ) S on T.ReadingDateTime = S.ReadingDateTime ' + CHAR(13) + CHAR(10) +
' WHEN NOT MATCHED BY TARGET '+ CHAR(13) + CHAR(10) +
' THEN '+ CHAR(13) + CHAR(10) +
' INSERT (ReadingDateTime,SIReading, RawReading)'+ CHAR(13) + CHAR(10) +
' VALUES (S.ReadingDateTime,S.SIReading, S.RawReading); '+ CHAR(13) + CHAR(10) +
' ' + CHAR(13) +
' SET @Count= @@ROWCOUNT '
DECLARE @ParmDefinition NVARCHAR(500);
SET @ParmDefinition = N'@Count int OUTPUT, @tt [dbo].[ChannelReadingType] READONLY';
PRINT @SQL
-- EXECUTE sp_executesql @SQL, N' @tt myTable READONLY', @t;
EXECUTE sp_executesql @SQL,@ParmDefinition, @tt = @t,@Count = @CountOUT OUTPUT;Can't test it fully as the "merge" table isn't provided as a create but that should do what you want it to do
Perfect I see what was missing now and added that in
Works very well. thank you very much for your help Ant
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply