SQL Server Array on Declare statements via query

mmvthczy 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(132)

I am trying to do something like

DECLARE @ID int = CHANGEME,
        @StartDate  datetime = '2023-09-02 04:00',
        @FinishDate datetime = '2023-09-03 04:00'

But this only lets me run this report per id, I have a list of IDs from another table that i can pull by a query

select id
form table
where tablename like '$tryme$'

So is there a way that I can run the report per ID again and again until we've run the list finished?

I saw that this is the way to write multiple/array in the statement

DECLARE @SITEIDS TABLE(siteid int)
Insert into @SITEIDS
values ('R00013'), ('R00028')

but this looks like it's hard coded ids.

I'm trying to get a report with

DECLARE @ID int = 1,
DECLARE @ID int = 12,
DECLARE @ID int = 123,

sql-server

来源：https://stackoverflow.com/questions/77149330/array-on-declare-statements-via-query

2条答案

按热度按时间

xvw2m8pv1#

Something like the following should allow you to do exactly what you are asking.

DECLARE @id int;

SELECT @id = MIN(id) FROM table where tablename like '$tryme$'

WHILE @id IS NOT NULL
BEGIN
   -- Code to run your report here 

   SELECT @id = MIN(id) FROM table WHERE tablename like '$tryme$' AND id > @id
END

You put the MIN(id) into your @id variable, process the report for that value, then grab the next MIN(id) that's greater than the one you just processed, and keep looping until no more id value is found.

赞(0）回复(0）举报 2023-10-15

cgh8pdjw2#

DECLARE @StartDate datetime = '2023-09-02 04:00'
DECLARE @FinishDate datetime = '2023-09-03 04:00'

CREATE TABLE #TempIDs (ID int)
INSERT INTO #TempIDs (ID)
    SELECT id FROM YourTable WHERE tablename LIKE '$tryme$'

DECLARE @CurrentID int
SELECT TOP 1 @CurrentID = ID FROM #TempIDs

-- Loop through the IDs and run the report for each ID
WHILE @CurrentID IS NOT NULL
BEGIN
    -- Execute your report here using @CurrentID


    -- Delete the processed ID from the temporary table
    DELETE FROM #TempIDs WHERE ID = @CurrentID
    -- Get the next ID
    SELECT TOP 1 @CurrentID = ID FROM #TempIDs
END

DROP TABLE #TempIDs

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Array on Declare statements via query

2条答案

相关问题

热门标签

最新问答