'Pass a list of dates to a stored procedure variable from a table
I have a data table with a value and date column. I am selecting the value and date which is closest to a given date in the stored procedure shown below. The variable @givendate is only currently using one date, I would like to pass in a list of given dates from another table and ask the stored procedure to loop over each one like an array.
How can I do this in SQL Server?
Thanks
CREATE PROCEDURE dbo.DateMatch
@givendate datetime
AS
BEGIN [enter image description here][1]
SELECT TOP 1 s.*
FROM [MyDataTable] s
WHERE record = 1
ORDER BY ABS(DATEDIFF(ss, s.[Date Time], @givendate))
END
EXEC dbo.DateMatch @givendate = '2020-17-04 17:58:00.000';
record | value | datadate
1 | 2.1 | 03-06-2021 11:29
1 | 2.6 | 03-06-2021 11:31
1 | 3.0 | 03-06-2021 11:41
1 | 2.7 | 03-06-2021 11:56
1 | 5.8 | 03-06-2021 11:59
1 | 4.9 | 03-06-2021 12:13
1 | 4.4 | 03-06-2021 12:21
1 | 4.7 | 03-06-2021 12:29
1 | 7.0 | 03-06-2021 12:32
2 | 6.9 | 08-09-2021 06:48
2 | 6.8 | 08-09-2021 06:51
--datematch table
record basedate | matchdateinc |
1 |03-06-2021 10:58 | 03-06-2021 11:28 |
1 |03-06-2021 10:58 | 03-06-2021 11:58 |
1 |03-06-2021 10:58 | 03-06-2021 12:28 |
1 |03-06-2021 10:58 | 03-06-2021 12:58 |
1 |03-06-2021 10:58 | 03-06-2021 13:28 |
1 |03-06-2021 10:58 | 03-06-2021 13:58 |
1 |03-06-2021 10:58 | 03-06-2021 14:28 |
1 |03-06-2021 10:58 | 03-06-2021 14:58 |
1 |03-06-2021 10:58 | 03-06-2021 15:28 |
2 |08-09-2021 06:20 | 08-09-2021 06:50 |
2 |08-09-2021 06:20 | 08-09-2021 07:20 |
--results table
record | matchdateinc | value |
1 | 03-06-2021 11:28 | 2.1 |
1 | 03-06-2021 11:58 | 5.8 |
1 | 03-06-2021 12:28 | 4.7 |
2 | 08-09-2021 06:50 | 6.8 |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
