Building a Stored Procedure Need Next Id

Depends: does this procedure insert individual rows, or does it need to insert a number of rows at once?

For the former, I believe that the stored procedure [dbo].[AP_GET_NEXTID_function] is the officially sanctioned method to acquire an id while incrementing the T_NEXT_ID table.

If you want to update a number of rows at once you can also use this with an increment, but will need to sort out distributing the ids for insertion yourself (I have some code for that if you'd like).

I'm looking to insert multiple rows at once. Basically, I'm copying info from one place to another because it needs to live in two places. Would love to see the code you use. Thanks!

I do this a fair amount: typically I create rows in a temporary table before inserting them into the table in question, which sounds like a good option if you're copying info from one place to another.  Generally what you should do is:

1. Compute the number of rows you need (probably by building them out in the temporary table first).  Generally my temp table is some sort of copy of the live table I'll be inserting into.  I put -1 into the id column, whatever it is named.
2. Use this number with AP_GET_NEXTID_function.  This gets you the first number in your new range, and "reserves" the numbers you need before any other concurrent process might start doing its own inserts.
3. Then update your temp table ids with something like this
   1. declare @id int = (whatever you got back from AP_GET_NEXTID_function)
   2. update #temp_table set @id = id = @id + 1 where id = -1
4. And now insert into the table from your temp table.

Thanks!

I've tested this out and it works great but when I drop the table and try it again it hasn't updated T_NEXT_ID. I'm assuming I need to add a line to update that before I drop my table? Does that sound right? Or is there some other function I'm supposed to execute?

What table are you dropping?  Also, is it possible you are rolling back a transaction before querying T_NEXT_ID?  Here's an example adding 10 to the source_no for TX_APPEAL_MEDIA_TYPE:

declare @type char(2) = 'SL', @nextid int, @increment int = 10

select * from T_NEXT_ID where type = 'SL'

select @nextid as nextid_before

exec [dbo].[AP_GET_NEXTID_function] @type = @type, @nextid = @nextid output, @increment = @increment

select @nextid as nextid_after

select * from T_NEXT_ID where type = 'SL'

I'm using a temp table to stage things and plan to drop the table in the sproc. How are you figuring out how many rows are going to be in your temp table to tell it what the increment should be? In my instance, the number will be different every time this sproc is run. 

Right.  Actually, these days I typically use a table variable.  Before I would often take the table I was going to insert into, do a "select * into #temp_table from actual_table" to quickly get my columns, but as of a couple of sql versions ago that includes copying over constraints on the audit columns, and so now I just spell out the columns I'll actually be working with, and a table variable is a bit easier to work with.  I then build out the new rows I want using the table variable, and then I can just count the resulting rows in the table variable to get the id count that I need, so something like:

declare @increment = 0

select @increment = (select COUNT(*) from @table_variable)

Right.  Actually, these days I typically use a table variable.  Before I would often take the table I was going to insert into, do a "select * into #temp_table from actual_table" to quickly get my columns, but as of a couple of sql versions ago that includes copying over constraints on the audit columns, and so now I just spell out the columns I'll actually be working with, and a table variable is a bit easier to work with.  I then build out the new rows I want using the table variable, and then I can just count the resulting rows in the table variable to get the id count that I need, so something like:

declare @increment = 0

select @increment = (select COUNT(*) from @table_variable)

Ah, that totally makes sense! So if I have something like the following I should be all set, right?

declare 
@next_id int,
@cs_count int
SELECT @cs_count = Count(*) FROM #access
exec @next_id =  [dbo].ap_get_nextid_function @type = 'CT', @increment = @cs_count
update #access set @next_id = id_key = @next_id + 1 where id_key = -1

Yes, that looks right to me.

Thanks again! I think I owe you a couple of drinks next time we are in the same city with all the help you've provided! 

Alas for virtual beer.  And no problem, just take a moment to sympathize: that script is an idealized process that I've only really sorted out in the past couple of years, meaning I have a decade's worth of procedures that should really work that way, but are instead wildly more awkward and at risk of collision...

Gawain Lavers you say that like that is not true of all of us. 

For me the key difference is the persistence of temp tables outside of the local context.  I do wind up having a number of applications where I want to share data between different stored procedures running in the same session.