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.

Hi, take a look at this. im using next_id to auto create sources. 

--select * from T_CAMPAIGN where description like 'DCM%'
--campaign_no	default_fund	description
--1688			NULL			DCM Leads FY22
--select * from T_APPEAL where description like 'DCM%'
--appeal_no	campaign_no	description
--2593		1688		DCM Leads FY22
--select * from TX_APPEAL_MEDIA_TYPE where appeal_no = 2556

declare @createSources char(1)

declare @appeal_no int
declare @source_no int
declare @source_name varchar(50)
declare @segment_id int
declare @source_Start_Dt datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) -- begining of today


set @appeal_no = 2593 -- 2593		1688		DCM Leads FY22

set @createSources = 'N'


create table #new_Sources(id int identity(1,1), source_name varchar(50), source_name_len int, segment_id int, param_1 varchar(99), param_2 varchar(99))
insert into #new_Sources(source_name, source_name_len, segment_id, param_1, param_2)
select
'DCM_' + left(isnull(b.Param_Name,'') + case when isnull(b.Param_Name,'') > '' and isnull(c.Param_Name,'') > '' then '/' else '' end + isnull(c.Param_Name,''),46),
len('DCM_' + isnull(b.Param_Name,'') + case when isnull(b.Param_Name,'') > '' and isnull(c.Param_Name,'') > '' then '/' else '' end + isnull(c.Param_Name,'')),
a.ID,
b.Param_Name,
c.Param_Name
from
[LTR_DCM_LEAD_SEGMENTS] a
left outer join [LTR_DCM_LEAD_PARAMETERS] b on a.param_1 = b.id
left outer join [LTR_DCM_LEAD_PARAMETERS] c on a.param_2 = c.id
where
a.inactive = 'N'

select * from #new_Sources order by segment_id

select 'Duplicate new source name'
select * from #new_Sources where source_name in(select source_name from #new_Sources group by source_name having count(id) > 1) order by source_name

if exists(select * from #new_Sources where source_name_len > 50)
begin
	print 'Source_Name length Issue'
	return
end



if @createSources = 'Y'
BEGIN

	declare @done char(1)
	declare @placeholder int
	select  @done = 'N', @placeholder = 0
	While   @done = 'N'
	  Begin
		Select @placeholder =  min(id)from #new_Sources where id > @placeholder
	
			If @placeholder is null 
				Select @done = 'Y'
			Else
			BEGIN TRY
				BEGIN TRANSACTION

					select @source_name = left(source_name,50), @segment_id = segment_id from #new_Sources where id = @placeholder
					if not exists(select * from TX_APPEAL_MEDIA_TYPE where appeal_no = @appeal_no and source_name = @source_name)
					begin
					-- create new source
						exec @source_no = [dbo].ap_get_nextid_function @type = 'SL'
						insert into TX_APPEAL_MEDIA_TYPE 
						(appeal_no,	media_type,source_no,source_name,acq_dt,source_type,source_group,inactive,num_success_cust,start_dt)
						select
						@appeal_no,32,@source_no,@source_name,@source_Start_Dt,'B',82,'N',0,@source_Start_Dt

					-- update segment with source
						update LTR_DCM_LEAD_SEGMENTS set Source_no = @source_no where id = @segment_id

					end	

				COMMIT TRANSACTION;
			END TRY
			BEGIN CATCH
				ROLLBACK TRANSACTION;
				print 'bad shit happened'
			END CATCH
	
	 End	
END

--delete from TX_APPEAL_MEDIA_TYPE where source_no in (23338,23342)

-- source_name varchar(50)

-- select * from T_NEXT_ID where type = 'SL'
/*
	type	description	next_id	parent_table	parent_col	module
	SL 	Source List	23339	TX_APPEAL_MEDIA_TYPE	source_no	F
*/

drop table #new_Sources

Thanks!

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)

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...

This has been super informative.  I usually cheat with individual records and use insert Max(ID)+1