Page 1 of 1

SPR_SequenceCustom - Returning the next Capita sequence to use for insert statements where tables have custom sequences

Posted: Mon Feb 20, 2017 6:17 pm
by robinwilson16
Procedure to set and retrieve the next sequence value (not yet used this one!):

Code: Select all

ALTER PROCEDURE [dbo].[SPR_SequenceCustom]
	@reference AS VARCHAR(50)
AS
BEGIN
	DECLARE @Seq AS VARCHAR(50) = '0'

	UPDATE caps_sequences WITH (TABLOCK) 
	SET
		seq_value = seq_value + 1  
	WHERE
		seq_ref = @reference;
	
	SELECT 
		S.seq_id,
		S.seq_format,
		S.seq_value 
	FROM caps_sequences S
	WHERE 
		seq_ref = @reference

	-- TO DO: Need to return the new sequence in the correct format
END
So far I have not found a use for this sequence. You probably just need the standard sequence generator here: viewtopic.php?f=23&t=100