IF OBJECT_ID('UP_GET_XXX_SEQNO') IS NOT NULL

IF OBJECT_ID('UP_GET_XXX_SEQNO') IS NOT NULLBEGIN    PRINT 'Dropping procedure UP_GET_XXX_SEQNO'    DROP PROCEDURE DBO.[UP_GET_XXX_SEQNO]      IF @@ERROR = 0        PRINT 'Procedure UP_GET_XXX_SEQNO dropped'ENDGOCREATE PROCEDURE DBO.[UP_GET_XXX_SEQNO]/************************************************************ Procedure description:* Date:   8/8/2014 * Author: Patrick* Changes* DateModified ByComments* 8/8/2014 Patrick            Translate PLSQL TO TSQL************************************************************/(@ac_table AS INT)ASBEGINSET 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.tabidFROM   SystemSequences s WITH (UPDLOCK) INNER   JOIN SqlDictionary d WITH (NOLOCK)            ON  d.tableid = s.tabidWHERE  s.id = -1       AND s.dataareaid = 'dat'       AND s.name = 'SEQNO'       AND d.fieldid = 0       AND d.name = @ac_tableIF @@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_tblUSING (SELECT @ln_id AS [id],@ln_tabid AS [tabid], 'dat'  AS [dataareaid] ) AS source_tblON  target_tbl.id        =source_tbl.id         AND target_tbl.tabid     =source_tbl.tabid      AND target_tbl.dataareaid=source_tbl.dataareaid WHEN MATCHEDTHEN  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=1RETURN @ln_seqnoEND 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 0ENDGOIF @@ERROR = 0    PRINT 'Procedure UP_GET_XXX_SEQNO created'GO