July 19, 2023 at 3:54 pm
I have a table and some incoming JSON as follows:
DECLARE @DeviceSerialNumber NVARCHAR(100),
@ChannelIDs NVARCHAR(50)
DECLARE @json NVARCHAR(MAX)
SET @json = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}'
SET @DeviceSerialNumber = JSON_VALUE(@JSON, '$.SerialNumber')
SET @ChannelIDs = REPLACE
(
(Replace (JSON_QUERY(@JSON, '$.ChannelIDs'), '[',''))
,']'
,'')
select @DeviceSerialNumber, @ChannelIDs
this give me the values
Serial Number = 940860 and ChannelIDs = '11,12,17,14,13'
I want to use both of these to query a table as follows
select * from [#DeviceChannel]
where SerialNumber = @DeviceSerialNumber AND EXISTS ChannelID IN (Select @ChannelIDs)
This wont work because channelID is an INT and my @ChannelsIDS is a comma seperated list
Without having to resort to transposing @ChannelIDs into a temp table, is there any way
that i can do this in a single statement placing the @ChannelIDs in the where clause?
DROP TABLE IF EXISTS [#DeviceChannel]
CREATE TABLE [#DeviceChannel](
[SerialNumber] [nvarchar](255) NOT NULL,
[ChannelID] [int] NOT NULL,
[DeviceID] [uniqueidentifier] NOT NULL,
[GroupID] [uniqueidentifier] NULL,
[ProductCode] [nvarchar](255) NULL,
[SoftwareVersion] [nvarchar](255) NULL,
[DeviceChannelID] [uniqueidentifier] NOT NULL,
[ChannelType] [nvarchar](255) NULL,
[DerivedChannelID] [uniqueidentifier] NULL,
[ChannelName] [nvarchar](255) NULL,
[IsInternal] [bit] NULL,
[Dimension] [nvarchar](255) NULL,
[Symbol] [nvarchar](255) NULL,
[Upper] [real] NULL,
[Lower] [real] NULL,
[Precision] [int] NULL,
[ConversionTypeName] [nvarchar](100) NOT NULL,
[ChannelTypeID] [uniqueidentifier] NOT NULL,
[ConversionTypeID] [smallint] NOT NULL
) ON [PRIMARY]
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 5, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'e3cbaa56-a6ec-4e42-9666-0072feced4fb', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 9, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'43306add-ba30-49e5-a378-00fd744a7302', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 1, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'43f62eb4-fc97-453d-81f6-01569c19c620', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 2, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'270fa1d3-7f3b-4afa-a546-0281f58a81da', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 1, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'90ed770a-c3ef-4fd2-91d9-0bf7a29eb8e9', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 9, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'48637629-438c-41aa-b370-102981c2bac4', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 18, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'ea8642f3-e981-4007-81b5-120951fd9ab5', N'Service', NULL, N'Vdd (Unloaded)', 1, N'voltage', N'V', NULL, NULL, NULL, N'test', N'f1bb73c1-99c5-4ebd-a259-02c126c470c7', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 7, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'8998fe59-412e-49a2-befb-1ff851d443ed', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 11, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'a13e0820-6013-470e-9de3-2055e05ab39e', N'Sensor', NULL, N'Probe D', 1, N'temperature', N'°C', 125, -40, 1, N'test', N'272cf5f0-3d2e-457b-97f4-c2e529d40090', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 0, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'ff4cc8ba-c810-4ff8-a8a5-3405d77e5145', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 16, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'028d948a-52f2-4de3-8691-365693787373', N'Sensor', NULL, N'Probe B Humidity', 1, N'humidity', N'%RH', 110, -10, 1, N'test', N'272cf5f0-3d2e-457b-97f4-c2e529d40090', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 6, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'f6a8310c-5726-46fb-bccf-3ccc45b933e0', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 10, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'1f1cf17f-1105-48d8-a0e7-3cfd76bfb3f8', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 6, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'7b7d38fa-17a2-4298-ac01-3edd2a8e1570', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 4, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'f7b0052d-a7ac-4fdf-ab66-3f7511b83afc', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 17, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'ae94317a-97ca-42b2-99b6-42a84b6ab718', N'Ambient', NULL, N'Ambient Temperature', 1, N'temperature', N'°C', NULL, NULL, NULL, N'test', N'fc468c6f-22b4-41ce-8c5a-84c4e6e7d6fe', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 7, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'4e99336a-8845-434a-9331-4302f06bc598', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 11, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'eb3f6ef1-bacf-40f4-aefc-47c0e5b40c7e', N'Sensor', NULL, N'Temperature', 1, N'temperature', N'°C', 85, -25, 1, N'test', N'272cf5f0-3d2e-457b-97f4-c2e529d40090', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 4, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'5265ba10-5971-43a6-aaa8-4b7909fa6249', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 10, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'7bee2ded-8059-4f65-a9c5-4e7ff2e1cc23', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 3, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'348d3aa1-fcbd-4e8f-a001-514cd6762061', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 9, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'343d9aa9-bd84-463f-a157-56fe044ad988', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 8, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'cac0e75c-25ad-4727-8d38-57879d1e0601', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 15, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'2185341a-9516-4125-950b-5b5ea1f4b341', N'Service', NULL, N'Vbatt_chk', 1, N'voltage', N'V', NULL, NULL, NULL, N'test', N'f1bb73c1-99c5-4ebd-a259-02c126c470c7', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 12, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'e985b4e2-356a-4874-aecf-5da6be1e60e5', N'Sensor', NULL, N'Probe C', 1, N'temperature', N'°C', 125, -40, 1, N'test', N'272cf5f0-3d2e-457b-97f4-c2e529d40090', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 16, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'b819d55b-de13-4051-9fa4-612fcfd90c5b', N'Service', NULL, N'Vdd (Unloaded)', 1, N'voltage', N'V', NULL, NULL, NULL, N'test', N'f1bb73c1-99c5-4ebd-a259-02c126c470c7', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 5, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'715d6b1a-9ac3-423f-a4b0-6601f33eb4a4', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 12, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'11be8ca9-4580-4313-a97d-691ee35028ed', N'Service', NULL, N'Vbatt_chk', 1, N'voltage', N'V', NULL, NULL, NULL, N'test', N'f1bb73c1-99c5-4ebd-a259-02c126c470c7', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 17, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'6265ab07-f024-42e4-8b67-696b45427f61', N'Virtual', NULL, N'Dewpoint', 1, N'temperature', N'°C', 110, -10, 0, N'test', N'bc4b5d35-7fe4-422c-afbc-0decd5d76415', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 14, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'2a3d2748-2985-45c7-ac6a-6b7d7b16e735', N'Service', NULL, N'Vdd (Loaded)', 1, N'voltage', N'V', NULL, NULL, NULL, N'test', N'f1bb73c1-99c5-4ebd-a259-02c126c470c7', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 17, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'6a418d9b-e783-4c2c-a781-6cc33824598a', N'Ambient', NULL, N'Ambient Temperature', 1, N'temperature', N'°C', NULL, NULL, NULL, N'test', N'fc468c6f-22b4-41ce-8c5a-84c4e6e7d6fe', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 11, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'7ee53172-8ca1-42f1-8164-6cfb62591931', N'Sensor', NULL, N'Probe B Temperature', 1, N'temperature', N'°C', 85, -25, 1, N'test', N'272cf5f0-3d2e-457b-97f4-c2e529d40090', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 15, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'2a594273-6963-4e14-97f4-7bfff9c7d584', N'Sensor', NULL, N'Probe A Humidity', 1, N'humidity', N'%RH', 110, -10, 1, N'test', N'272cf5f0-3d2e-457b-97f4-c2e529d40090', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 2, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'7cb1886f-ddab-4d34-b873-8417e12cc6fa', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 8, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'a0c40a75-d5ee-4163-8976-8831756b2385', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 18, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'dbd5bace-b932-471a-89d6-8b6affd77abb', N'Service', NULL, N'Vdd (Unloaded)', 1, N'voltage', N'V', NULL, NULL, NULL, N'test', N'f1bb73c1-99c5-4ebd-a259-02c126c470c7', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 4, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'8b3b710b-d711-4e16-976b-92e81569b800', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 3, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'c05e7372-962d-45b5-9527-92e91b58bb9c', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 13, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'a3f03bed-0bf4-40fe-83e2-9494fd8df88c', N'Service', NULL, N'Vbatt_chk', 1, N'voltage', N'V', NULL, NULL, NULL, N'test', N'f1bb73c1-99c5-4ebd-a259-02c126c470c7', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 15, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'3520ca41-14ed-4a82-8c8c-9db6708304b2', N'Ambient', NULL, N'Ambient Temperature', 1, N'temperature', N'°C', NULL, NULL, NULL, N'test', N'fc468c6f-22b4-41ce-8c5a-84c4e6e7d6fe', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 14, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'490c10e5-99ce-4519-883d-9fe37d038681', N'Sensor', NULL, N'Humidity', 1, N'humidity', N'%RH', 110, -10, 1, N'test', N'272cf5f0-3d2e-457b-97f4-c2e529d40090', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 0, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'fc2f5bbe-13f3-49a6-97aa-aa0f74154f25', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 0, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'371a445a-2720-405b-b2fb-ae3882c5f516', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 7, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'bc9e8345-0cb2-47b2-ad43-b06216a61ba7', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 10, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'84a2b22a-a768-47b9-b888-b44cab570217', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 16, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'ccfd3ca9-0d8b-4e92-9ae2-b86f47c7d1f7', N'Service', NULL, N'Vdd (Loaded)', 1, N'voltage', N'V', NULL, NULL, NULL, N'test', N'f1bb73c1-99c5-4ebd-a259-02c126c470c7', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 2, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'f27bd9c8-257a-4a57-893b-b88cc0fc9d2e', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 1, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'088db714-f2ae-404f-a4d7-b9b74f80fd00', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 14, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'62be11bf-e3a8-416f-ae84-ba87e177d82b', N'Sensor', NULL, N'Probe A', 1, N'temperature', N'°C', 125, -40, 1, N'test', N'272cf5f0-3d2e-457b-97f4-c2e529d40090', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 5, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'fa945899-bc00-47e4-b36b-c41d6f1dde67', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 8, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'3572146f-5793-43af-866f-c8e85aedd9f3', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 19, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'4a742d25-a4a4-4be7-9412-cd89978468a6', N'Virtual', NULL, N'Probe A Dewpoint', 1, N'temperature', N'°C', 110, -10, 0, N'test', N'bc4b5d35-7fe4-422c-afbc-0decd5d76415', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 6, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'fe65b411-4846-4024-9b91-cfc9ba730891', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 13, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'0caec807-e88d-46c4-99aa-d5e4b34ac6df', N'Sensor', NULL, N'Probe B', 1, N'temperature', N'°C', 125, -40, 1, N'test', N'272cf5f0-3d2e-457b-97f4-c2e529d40090', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'939029', 13, N'aa91c620-c416-459c-a04d-120e1341c8ce', NULL, N'ACSR-3600-A', N'1.2.928', N'aa198e25-9f3c-40b1-b234-d6dd5fa6c6e9', N'Service', NULL, N'Vdd (Loaded)', 1, N'voltage', N'V', NULL, NULL, NULL, N'test', N'f1bb73c1-99c5-4ebd-a259-02c126c470c7', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 12, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'f2bd0a84-9770-45dc-bf2c-e4642510e9cf', N'Sensor', NULL, N'Probe A Temperature', 1, N'temperature', N'°C', 85, -25, 1, N'test', N'272cf5f0-3d2e-457b-97f4-c2e529d40090', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'940860', 3, N'ad7b1ee3-21ad-4e2e-a7e0-b82f61bc2537', NULL, N'TGRF-4024-A', N'1.2.928', N'7714d37a-0d53-439c-a8ef-ebead622c249', N'Test', NULL, NULL, 1, N'', N'', NULL, NULL, NULL, N'test', N'f5524938-2e38-4324-8089-33542159be2b', 0)
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelID], [DeviceID], [GroupID], [ProductCode], [SoftwareVersion], [DeviceChannelID], [ChannelType], [DerivedChannelID], [ChannelName], [IsInternal], [Dimension], [Symbol], [Upper], [Lower], [Precision], [ConversionTypeName], [ChannelTypeID], [ConversionTypeID]) VALUES (N'926371', 20, N'cc3ad4e2-a420-4070-b791-2b3c24faf88e', NULL, N'TGRF-4602-A', N'1.2.928', N'7783afb3-7587-4958-8656-ecd1f5dcd0e2', N'Virtual', NULL, N'Probe B Dewpoint', 1, N'temperature', N'°C', 110, -10, 0, N'test', N'bc4b5d35-7fe4-422c-afbc-0decd5d76415', 0)
GO
SET NOCOUNT OFF
DECLARE @DeviceSerialNumberNVARCHAR(100),
@ChannelIDs NVARCHAR(50)
DECLARE @Json NVARCHAR(MAX)
SET @Json = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}'
SET @DeviceSerialNumber = JSON_VALUE(@JSON, '$.SerialNumber')
SET @ChannelIDs = REPLACE
(
(Replace (JSON_QUERY(@JSON, '$.ChannelIDs'), '[',''))
,']'
,'')
select @DeviceSerialNumber, @ChannelIDs
select * from [#DeviceChannel]
where SerialNumber = @DeviceSerialNumber AND EXISTS ChannelID IN (Select @ChannelIDs)
July 19, 2023 at 4:23 pm
you either split them into rows in a table or you use dynamic sql . both of these assume you care with good performance.
not looking at performance there is a way - but as such an example can make people use it without considering the implications I would prefer not to put it here - but it involves converting your column to a string, use a bit of concatenation and use a like clause against your variable. This would be patter matching ',123,124,333,' is like '%,124,%' as an example
July 19, 2023 at 5:02 pm
Something like this?
WITH CIds
AS (SELECT ChannelID = CAST (value AS INT)
FROM STRING_SPLIT(@ChannelIDs, ','))
SELECT dc.*
FROM #DeviceChannel dc
JOIN CIds
ON CIds.ChannelID = dc.ChannelID
WHERE dc.SerialNumber = @DeviceSerialNumber;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 19, 2023 at 5:03 pm
[EDIT]: DON'T USE THIS METHOD. I left the post here to show you that I'm actually quite the rookie at JSON. There's no need for the complexity of the TRIM() and SPLIT_STRING() functions. I'm adding a post below this to demonstrate.
This will do nicely without any conversions or wildcard filters and is very similar to what Phil wrote above except the string splitting is being done in the WHERE clause instead of a CTE. The one below also eliminates the need for the other two variables and is a good candidate for becoming a high performance iTVF. Both should be fast, especially in the presence of the proper index.
--===== This could be a parameter that you pass to an iTVF (Inline Table Valued Function)
DECLARE @pJSON NVARCHAR(MAX) = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}'
;
--===== This makes use of the advanced TRIM function first available in 2017
-- to simplify character replacement.
SELECT dc.*
FROM #DeviceChannel dc
WHERE dc.SerialNumber = JSON_VALUE(@pJSON,'$.SerialNumber')
AND dc.ChannelID IN (SELECT value FROM STRING_SPLIT(TRIM('[] ' FROM JSON_QUERY(@pJSON,'$.ChannelIDs')),','))
;
Think of an iTVF as a "parameterized" view, which is very useful for a whole lot of things and allows you to easily use the abstraction of calling it in a CROSS APPLY without having to repeat the code.
And, thank you very much for the way you properly posted sample data and the examples leading up to the solution you were looking for. THAT'S definitely the way to get good coded answers. Well done!
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2023 at 5:38 pm
p.s. I seem to remember a way to do this directly with JSON and not have to do the STRING_SPLIT() and TRIM() things. I'm looking for it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2023 at 5:52 pm
p.s. I seem to remember a way to do this directly with JSON and not have to do the STRING_SPLIT() and TRIM() things. I'm looking for it.
SET NOCOUNT OFF
DECLARE @DeviceSerialNumber NVARCHAR(100),
@ChannelIDs NVARCHAR(50)
DECLARE @Json NVARCHAR(MAX)
SET @Json = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}'
SET @DeviceSerialNumber = JSON_VALUE(@JSON, '$.SerialNumber')
SET @ChannelIDs = REPLACE
(
(Replace (JSON_QUERY(@JSON, '$.ChannelIDs'), '[',''))
,']'
,'')
SET @ChannelIDs = REPLACE(REPLACE(REPLACE((json_query(@JSON, '$.ChannelIDs')), '[', ''), ']', ''), ' ', ', ')
SET @ChannelIDs = '[' + @ChannelIDs + ']'
select @DeviceSerialNumber, @ChannelIDs
SELECT value
FROM OPENJSON(@ChannelIDs)
SELECT *
FROM [#DeviceChannel]
WHERE SerialNumber = @DeviceSerialNumber
AND ChannelID IN (SELECT value
FROM OPENJSON(@ChannelIDs)
);
July 19, 2023 at 5:55 pm
Here's the updated post that actually takes advantage of JSON. No need for TRIM() or STRING_SPLIT() functions because the source is in a proper JSON ARRAY format. Again, this eliminates the need for the pre-calculation found in the other two variables, further lending itself to being converted to an iTVF.
--===== This could be a parameter that you pass to an iTVF (Inline Table Valued Function)
DECLARE @pJSON NVARCHAR(MAX) = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}'
;
--===== This code takes advantage of the fact that the ChannelIDs are stored in a JSON ARRAY.
-- Note the added conversion to INT (Thanks to Jeffrey Williams for the tip!)
SELECT *
FROM #DeviceChannel dc
WHERE dc.SerialNumber = JSON_VALUE(@pJSON,'$.SerialNumber')
AND dc.ChannelID IN (SELECT CONVERT(INT,value) FROM OPENJSON(JSON_QUERY(@pJSON,'$.ChannelIDs')))
;
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2023 at 5:58 pm
posted code
Heh... you posted while I was writing the code. Thanks, Jonathan.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2023 at 7:45 pm
You may want to validate the return value from OPENJSON - I believe it returns an NVARCHAR and that could have a significant impact on performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 19, 2023 at 8:07 pm
You may want to validate the return value from OPENJSON - I believe it returns an NVARCHAR and that could have a significant impact on performance.
Damn! Awesome catch, Jeffrey! I modified my code above to include the conversion to INT. Thank you!
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2023 at 8:41 pm
Jeffrey Williams wrote:You may want to validate the return value from OPENJSON - I believe it returns an NVARCHAR and that could have a significant impact on performance.
Damn! Awesome catch, Jeffrey! I modified my code above to include the conversion to INT. Thank you!
I believe there is a way to specify the schema using WITH for the OPENJSON method. You can then define the column name, data type and element which will convert the data for you.
Note: I am not well versed in JSON or using the JSON features (yet).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 19, 2023 at 9:14 pm
Here is an example - modifying Jeff Moden's code:
--===== This could be a parameter that you pass to an iTVF (Inline Table Valued Function)
DECLARE @pJSON NVARCHAR(MAX) = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}'
;
--===== This code takes advantage of the fact that the ChannelIDs are stored in a JSON ARRAY.
-- Note the added conversion to INT (Thanks to Jeffrey Williams for the tip!)
SELECT *
FROM #DeviceChannel dc
WHERE dc.SerialNumber = JSON_VALUE(@pJSON,'$.SerialNumber')
AND dc.ChannelID IN (SELECT ChannelID FROM OPENJSON(JSON_QUERY(@pJSON,'$.ChannelIDs')) WITH (ChannelID int '$'))
;
There is a further problem here - the JSON_VALUE returns an NVARCHAR(4000). If SerialNumber is not defined as an NVARCHAR - an implicit conversion would be needed and that could cause an issue in the SeekPlan for the query (in other words - may not be able to use an index).
Interesting enough - the implicit conversion converts the column value to nvarchar(255) from varchar(255). It seems to generate the plan and converts the column to the equivalent nvarchar. If the column is defined as varchar(80) - it would be implicitly converted to nvarchar(80) and then compared to the nvarchar(4000) value returned from JSON_VALUE.
Also of interest - if SerialNumber is defined as an integer then there is no implicit conversion.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 19, 2023 at 9:19 pm
All good reasons for why I have a serious dislike for XML, JSON, and a couple of other things when it comers to SQL.
Thanks for the info, Jeffrey. And, I'm right there with you... I'm not good a JSON... and hope to never be. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 20, 2023 at 1:20 pm
Here's the updated post that actually takes advantage of JSON. No need for TRIM() or STRING_SPLIT() functions because the source is in a proper JSON ARRAY format. Again, this eliminates the need for the pre-calculation found in the other two variables, further lending itself to being converted to an iTVF.
--===== This could be a parameter that you pass to an iTVF (Inline Table Valued Function)
DECLARE @pJSON NVARCHAR(MAX) = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}'
;
--===== This code takes advantage of the fact that the ChannelIDs are stored in a JSON ARRAY.
-- Note the added conversion to INT (Thanks to Jeffrey Williams for the tip!)
SELECT *
FROM #DeviceChannel dc
WHERE dc.SerialNumber = JSON_VALUE(@pJSON,'$.SerialNumber')
AND dc.ChannelID IN (SELECT CONVERT(INT,value) FROM OPENJSON(JSON_QUERY(@pJSON,'$.ChannelIDs')))
;
Hi jeff
this worked for me well. Thanks very much everyone for your help on this.
July 20, 2023 at 10:22 pm
Jeff Moden wrote:Here's the updated post that actually takes advantage of JSON. No need for TRIM() or STRING_SPLIT() functions because the source is in a proper JSON ARRAY format. Again, this eliminates the need for the pre-calculation found in the other two variables, further lending itself to being converted to an iTVF.
--===== This could be a parameter that you pass to an iTVF (Inline Table Valued Function)
DECLARE @pJSON NVARCHAR(MAX) = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}'
;
--===== This code takes advantage of the fact that the ChannelIDs are stored in a JSON ARRAY.
-- Note the added conversion to INT (Thanks to Jeffrey Williams for the tip!)
SELECT *
FROM #DeviceChannel dc
WHERE dc.SerialNumber = JSON_VALUE(@pJSON,'$.SerialNumber')
AND dc.ChannelID IN (SELECT CONVERT(INT,value) FROM OPENJSON(JSON_QUERY(@pJSON,'$.ChannelIDs')))
;Hi jeff
this worked for me well. Thanks very much everyone for your help on this.
Thank you for the feedback. Just remember, though... if that SerialNumber column isn't actually an NVARCHAR(), you should do a conversion on it so that you don't get index scans and get seeks instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply