IF OBJECT_ID('UP_GET_XXX_SEQNO') IS NOT NULL

IF OBJECT_ID('UP_GET_XXX_SEQNO') IS NOT NULL
BEGIN
    PRINT 'Dropping procedure UP_GET_XXX_SEQNO'
    DROP PROCEDURE DBO.[UP_GET_XXX_SEQNO]  
    IF @@ERROR = 0
        PRINT 'Procedure UP_GET_XXX_SEQNO dropped'
END
GO

CREATE PROCEDURE DBO.[UP_GET_XXX_SEQNO]
/***********************************************************
* Procedure description:
* Date:   8/8/2014 
* Author: Patrick
* Changes
* Date		Modified By			Comments
* 8/8/2014	 Patrick            Translate PLSQL TO TSQL
************************************************************/
(@ac_table AS INT)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ln_seqno     NUMERIC(10),
	        @ln_id        NUMERIC(10),
	        @ln_tabid     NUMERIC(10)
	/*BEGIN TRY*/
	BEGIN TRY
		/*SET VALUES*/	
		SELECT @ln_seqno = s.nextval,
		       @ln_id        = s.id,
		       @ln_tabid     = s.tabid
		FROM   SystemSequences s WITH (UPDLOCK) INNER   JOIN SqlDictionary d WITH (NOLOCK)
		            ON  d.tableid = s.tabid
		WHERE  s.id = -1
		       AND s.dataareaid = 'dat'
		       AND s.name = 'SEQNO'
		       AND d.fieldid = 0
		       AND d.name = @ac_table		
		IF @@ERROR > 0
		    RAISERROR ('Error raised in SystemSequences s WITH (UPDLOCK) INNER   JOIN SqlDictionary d WITH (NOLOCK)', 16, 1);/*[0-10]:CONTINUE;[11-19]:jump to catch*/
		/*MERGE*/	
		MERGE SystemSequences AS target_tbl
		USING (SELECT @ln_id AS [id],@ln_tabid AS [tabid], 'dat'  AS [dataareaid] ) AS source_tbl
		ON  target_tbl.id        =source_tbl.id         AND 
			target_tbl.tabid     =source_tbl.tabid      AND 
			target_tbl.dataareaid=source_tbl.dataareaid 
		WHEN MATCHED
			THEN  UPDATE SET target_tbl.nextval       = @ln_seqno + 1
        WHEN NOT MATCHED  
			THEN  INSERT (
				[ID],
				[NEXTVAL],
				[MINVAL],
				[MAXVAL],
				[CYCLE],
				[NAME],
				[TABID],
				[DATAAREAID],
				[RECVERSION],
				[RECID] 	
			)VALUES(
				-1,2,1,9.22337203685478E18,0,'SEQNO',
				(SELECT tableid FROM   SqlDictionary WHERE  fieldid = 0 AND NAME = @ac_table),
				'dat',1,-1
			);/*END MERGE*/
		IF @@ERROR > 0
		    RAISERROR ('Error raised in MERGE SystemSequences AS target_tbl', 16, 1);/*[0-10]:CONTINUE;[11-19]:jump to catch*/
		SET @ln_seqno=1
		RETURN @ln_seqno
	END TRY
	/*END TRY*/
	BEGIN CATCH
		/*DECLARE*/
		DECLARE @ErrorMessage        NVARCHAR(4000),
		        @ErrorSeverity       NVARCHAR(5),
		        @ErrorState          NVARCHAR(5),
		        @ERROR_NUMBER        NVARCHAR(5),
		        @ERROR_LINE          NVARCHAR(5),
		        @ERROR_PROCEDURE     NVARCHAR(100) 
		/*SET VALUES*/	
		SELECT @ErrorMessage = ERROR_MESSAGE(),
		       @ErrorSeverity       = ERROR_SEVERITY(),
		       @ErrorState          = ERROR_STATE(),
		       @ERROR_NUMBER        = ERROR_NUMBER(),
		       @ERROR_LINE          = ERROR_LINE(),
		       @ERROR_PROCEDURE     = CASE ISNULL(ERROR_PROCEDURE(), '')
		                               WHEN '' THEN ''
		                               ELSE 
		                                    'Error occur when running procedure: [' 
		                                    + ERROR_PROCEDURE() + '];'
		                          END;
		/*FORMATING MSG*/	
		SET @ErrorMessage = @ERROR_PROCEDURE + CHAR(10)
		    + 'Msg:' + @ErrorMessage + ' Line:' + @ERROR_LINE + ' Number:' + @ERROR_NUMBER
		    + CHAR(10)
		    + 'Date:' + CONVERT(NVARCHAR(30), GETDATE(), 120) 
		/*RAISERROR*/ 
		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH;
	RETURN 0
END
GO

IF @@ERROR = 0
    PRINT 'Procedure UP_GET_XXX_SEQNO created'
GO