SPR_Sequence - Returning the next Capita sequence to use for insert statements

For sharing any stored procedures
Post Reply
User avatar
robinwilson16
Site Admin
Posts: 185
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16

SPR_Sequence - Returning the next Capita sequence to use for insert statements

Post by robinwilson16 » Mon Feb 20, 2017 6:14 pm

Procedure to set and retrieve the next sequence value:

Code: Select all

CREATE PROCEDURE [dbo].[SPR_Sequence](@Seq numeric(16, 0) OUTPUT)
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE ulive.dbo.caps_id 
	SET 
		nextval = nextval + 1;

	SELECT 
		@Seq = CAST ( FORMAT ( ID.nextval, '727000000000000' ) AS numeric(16, 0) )
	FROM ulive.dbo.caps_id ID;
END
Usage (example inserts missing appointment records against staff where missing):

Code: Select all

DECLARE @Seq numeric(16, 0)
	
DECLARE @a_staff numeric(16, 0)
DECLARE @a_reference varchar(20)
DECLARE @a_name varchar(255)
DECLARE @a_plannedhours numeric(20, 8)

DECLARE cur CURSOR LOCAL FOR
	SELECT
		a_staff = STF.s_id,
		a_reference = 'LECTURER',
		a_name = 'Lecturer',
		a_plannedhours = 0
	FROM ulive.dbo.capd_staff STF
	LEFT JOIN ulive.dbo.capd_appointment AP
		ON AP.a_staff = STF.s_id
	WHERE
		AP.a_id IS NULL
	ORDER BY
		STF.s_staffreference

OPEN cur

FETCH NEXT FROM cur INTO 
	@a_staff, 
	@a_reference, 
	@a_name, 
	@a_plannedhours

while @@FETCH_STATUS = 0 BEGIN

	EXEC GC.dbo.SPR_Sequence @Seq = @Seq OUTPUT

	INSERT INTO ulive.dbo.capd_appointment (
		a_id,
		a_staff,
		a_reference,
		a_name,
		a_plannedhours
	)
	SELECT
		a_id = @Seq,
		a_staff = @a_staff, 
		a_reference = @a_reference, 
		a_name = @a_name, 
		a_plannedhours = @a_plannedhours

	FETCH NEXT FROM cur INTO 
		@a_staff, 
		@a_reference, 
		@a_name, 
		@a_plannedhours
END

CLOSE cur
DEALLOCATE cur

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests