Building a Stored Procedure Need Next Id

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