Stored Procedure to select max number of rows in 5 minutes

Question

I have an existing stored procedure that is being used to talk to another service. This other service can handle a max of 200 requests every 5 minutes, so the stored procedure is set to a maximum of 200 rows.

    SELECT TOP 200
        column1 as 'MESSAGE_A'
        , column2 as 'MESSAGE_B'
        , column3 as 'MESSAGE_C'
        , CAST(request as xml)
    FROM [table]
    ORDER BY [priority] ASC, column1 ASC
    FOR XML PATH ('result_xml'), TYPE, ROOT ('requests')

The problem is that, while the procedure runs automatically every 5 minutes it can also be run manually (because clients).

In the event that a manual execution tips the requests over 200, the third party gets borked and the process crashes.

How can I add a check so that it will select a maximum of 200 rows in a 5 minute time frame?

if it is possible to track, how much time service has been called then keep log of service called by date. this way you can prevent calling service if it exceeds 200 limit untill 5 mintues. — Deepak Sharma, May 17 '17 at 04:47

score 0 · Answer 1 · answered May 17 '17 at 04:57

For the same thing we have done (We use SSIS package to process the data automatically every 5 minutes, some time need to manually run that package) via adding a column isprocessed (bydefault false).

Either manually or automated, it set to true. so next time whenever we take records, remove the isprocessed=true data.

score 0 · Answer 2 · edited May 23 '17 at 12:26

I need to verify the syntax, but your stored procedure would look something like this:

CREATE Procedure GetBatch AS 

DECLARE @Rows INT

SELECT @Rows = 200 - Count(*) FROM [table] WHERE IsProcessing = 0


UPDATE TOP (@Rows) [table]
SET IsProcessing = 1
OUTPUT 
        INSERTED.column1 as 'MESSAGE_A'
        , INSERTED.column2 as 'MESSAGE_B'
        , INSERTED.column3 as 'MESSAGE_C'
        , CAST(INSERTED.request as xml)
    ORDER BY [priority] ASC, column1 ASC
    FOR XML PATH ('result_xml'), TYPE, ROOT ('requests')
WHERE IsProcessing = 0

A neat trick in SQL Server is that you can combine a SELECT and an UPDATE statement using the OUTPUT clause.

Note that you want to run this as a SERIALIZABLE transaction. Otherwise you may still grab too many rows.

MORE INFO: how can I Update top 100 records in sql server

Stored Procedure to select max number of rows in 5 minutes

2 Answers2