We read every piece of feedback, and take your input very seriously.
To see all available qualifiers, see our documentation.
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
CREATE PROCEDURE [dbo].[COMMON_GET_ORDER_NO] @returnOrderNo nvarchar(13) OUTPUT AS BEGIN -- 開始 -- トライ開始 BEGIN TRY SET LOCK_TIMEOUT 3000 -- トランザクション開始 BEGIN TRAN -- 現在の番号 DECLARE @seqNo SEQ -- 採番用日付 DECLARE @genDate DATE_OF -- 業務日付 DECLARE @businessDate DATE_OF -- 業務日付取得 SET @businessDate = GETDATE() -- カーソル定義 DECLARE getOrderNo_cursor CURSOR DYNAMIC FOR SELECT SEQ, GEN_DATE FROM TBL_AD_TRN_GET_ORDER_NO WITH (ROWLOCK XLOCK HOLDLOCK) -- 初期化処理 SET NOCOUNT ON -- カーソルオープン OPEN getOrderNo_cursor -- データ取得 FETCH NEXT FROM getOrderNo_cursor INTO @seqNo, @genDate IF @@FETCH_STATUS = 0 BEGIN -- GEN_DATEが今日の場合は、SEQを+1する IF CONVERT(nvarchar, @genDate, 111) = CONVERT(nvarchar, @businessDate, 111) BEGIN SET @returnOrderNo = RIGHT(CONVERT(nvarchar, @genDate, 112), 6) + RIGHT('000000' + CONVERT(nvarchar, @seqNo), 6) SET @seqNo = @seqNo + 1 UPDATE TBL_AD_TRN_GET_ORDER_NO SET SEQ = @seqNo END -- GEN_DATEが今日でなければ、SEQを初期化する ELSE BEGIN SET @seqNo = 1 SET @returnOrderNo = RIGHT(CONVERT(nvarchar, @businessDate, 112), 6) + RIGHT('000000' + CONVERT(nvarchar, @seqNo), 6) SET @seqNo = @seqNo + 1 UPDATE TBL_AD_TRN_GET_ORDER_NO SET SEQ = @seqNo, GEN_DATE = @businessDate END END ELSE -- 取れなかった場合は、新規にINSERTする BEGIN SET @seqNo = 1 SET @returnOrderNo = RIGHT(CONVERT(nvarchar, @businessDate, 112), 6) + RIGHT('000000' + CONVERT(nvarchar, @seqNo), 6) SET @seqNo = @seqNo + 1 INSERT INTO TBL_AD_TRN_GET_ORDER_NO (GEN_NAME, SEQ, GEN_DATE) VALUES('', @seqNo, @businessDate) END COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRAN END -- メッセージ設定 DECLARE @ErrorMessage NVARCHAR(4000) DECLARE @ErrorNumber INT DECLARE @ErrorSeverity INT DECLARE @ErrorState INT DECLARE @ErrorLine INT DECLARE @ErrorProcedure NVARCHAR(200) SET @ErrorNumber = ERROR_NUMBER() SET @ErrorSeverity = ERROR_SEVERITY() SET @ErrorState = ERROR_STATE() SET @ErrorLine = ERROR_LINE() SET @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') SET @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE(); EXEC dbo.COMMON_OUTPUT_LOG 'JPBETA,共通,-1,注文番号採番処理 異常終了', 'I' -- スロー RAISERROR ( @ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, -- parameter: original error number. @ErrorSeverity, -- parameter: original error severity. @ErrorState, -- parameter: original error state. @ErrorProcedure, -- parameter: original error procedure name. @ErrorLine -- parameter: original error line number. ); END CATCH -- カーソルのクローズ CLOSE getOrderNo_cursor DEALLOCATE getOrderNo_cursor END GO
The text was updated successfully, but these errors were encountered:
No branches or pull requests
The text was updated successfully, but these errors were encountered: