T-SQL set a rotating Flag (True/False) in records using Stored Proc

Question

I can do this using multiple commands in C# for the app I'm creating, but prefer a stored proc to eliminate issues with latency/locks, etc. (hopefully):

I have a table of 10 extensions (important fields): SortOrder, Extension, IsUsed

First record will be set to IsUsed = true

When calling the stored proc, I need the IsUsed of the NEXT record in sort order to be set to true,the current record that is true set to false. When I hit the last record, rotate back to the first record.

Use Case: I need to rotate through a bank of usable numbers. Multiple people use the app, so cannot reuse. a number within the last 4 minutes (Bank of 10 will suffice, but we can extend if necessary). When the user requests a number, they get the next avail. I can build the table however needed, so any and all options to achieve use case are welcome. I need to set the flag to true on the 1st record when stored proc is called. All other records should be false.

I have seen this, which is of interest, but doesn't quite answer:

Get "next" row from SQL Server database and flag it in single transaction

Welcome to Stack Overflow, Tim. What does your logic currently look like? It would be useful to include the code you're working with. — Jeremy Caney, Feb 01 '20 at 00:11
Aside, I imagine the issue with this is going to be how concise and efficient you want the code to be. I can think of a number of simple ways of doing this with a few queries and conditions in the sproc, but if you're trying to get it down to a single query it's going to require a bit of creativity. — Jeremy Caney, Feb 01 '20 at 00:14
Can a user hold on to a single value long enough that you need to skip it in the next rotation since it is still "in use"? — HABO, Feb 01 '20 at 02:32
@EricBrandt the number returned is a string as the extension is a phone number which could be 0442. The list of 10 numbers are configured in the phone system, but I need to rotate them for use by 50 reps. — Tim Fischer, Feb 01 '20 at 04:53
@JeremyCaney The code isn't written yet - but with entity framework, I can query for the "true" record, set it to false while getting it's "SortOrder", then setting the "SortOrder +1" to true. About three hits to the database from an IIS Server/Entity Framework. I'm thinking that if SQL can so all that in a single stored proc call, I will eliminate a possible timing issue with multiple people hitting table at same time. if that makes sense. — Tim Fischer, Feb 01 '20 at 04:55
@HABO Can't do that - and can't just use the reps own extension in this instance. I need to use a bank of 10 that have a special purpose. This will programatically transfer a phone call to the new extension returned as a string. (eventually) — Tim Fischer, Feb 01 '20 at 04:57

Eric Brandt · Answer 1 · 2020-02-03T17:33:17.043

If all that you're using this for is to return a number to identify a session, I'd suggest scrapping the whole table idea and letting SQL Server do the work for you.

You can create a SEQUENCE object that will cycle and return the next value for you, without needing to write any code or maintain any tables.

CREATE SEQUENCE dbo.Extension 
    AS integer
    START WITH 5  
    INCREMENT BY 5
    MINVALUE 5 
    MAXVALUE 50
    CYCLE;

This will return the number 5 the first time it's called, up to the number 50 on call number 10, and then start back over. You can adjust the numbers in the code to more or less do whatever you would like, though.

Get the next value like this:

SELECT NEXT VALUE FOR dbo.Extension;

And when/if you need to extend the range:

ALTER SEQUENCE dbo.Extension
     MAXVALUE 100;

Play around with the idea on the Rextester demo.

Edit: In light of the comments above and below, I'd still stick with a SEQUENCE, I think.

Every time your code calls the table for an extension, use a query along the lines of this:

SELECT 
  Extension
FROM 
  ExtTable
WHERE
  SortOrder = NEXT VALUE FOR dbo.Extension;

Functionally, this should do what you're after, again with no code to write or maintain.

There are 10 static phone extensions that I need to rotate, so the "next available" must come from that static list. 50 Customer Service reps, click a button that calls a web service that returns the next number in the list to use to transfer a call. So I don't think the above would work. — Tim Fischer, Feb 01 '20 at 04:52

T-SQL set a rotating Flag (True/False) in records using Stored Proc

1 Answers1