Table_Name   ID (where ID is bigint datatype)
PO-Summary   42

ALTER PROCEDURE GetSequenceNumber
	-- Add the parameters for the stored procedure here
	@TransType AS VARCHAR(50),
	@return as varchar(10) output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @SequenceNumber AS VARCHAR(10)
	SET NOCOUNT ON;

    SELECT @SequenceNumber=RIGHT(REPLICATE('0', 8) + CAST(ID AS VARCHAR(8)), 8)
    FROM tbl_ID_Counter WHERE TableName=@TransType
    
    SET @return=@SequenceNumber + 1
    
    RETURN @return
END
GO

declare @id int
declare @return int
set @return=42
set @id=@return+1
select RIGHT(REPLICATE('0', 8),8)+ CAST(@ID AS VARCHAR(8))

declare @SequenceNumber varchar(10)
SELECT @SequenceNumber=RIGHT(REPLICATE('0', 8) + '42', 8)
select @SequenceNumber
select @SequenceNumber + 1

ALTER PROCEDURE GetSequenceNumber
	-- Add the parameters for the stored procedure here
	@TransType 	AS VARCHAR(50),
	@return 	AS VARCHAR(10) output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @SequenceNumber table
	(
		ID	int
	)

	SET NOCOUNT ON

    	UPDATE	c
	SET	ID	= ID + 1
	OUTPUT	INSERTED.ID 
	INTO	@SequenceNumber (ID)
    	FROM 	tbl_ID_Counter c
	WHERE 	TableName = @TransType
    
    	SELECT	@return = right(replicate('0', 10) + convert(varchar(10), ID), 10)
	FROM	@SequenceNumber
END
GO

-- To Execute it
declare	@return	varchar(10)
exec 	GetSequenceNumber @TransType = 'A', @return = @return OUTPUT
select	@return