Scribd
Upload
8 views3 pages

SQL Procedure for Document Date Update

This stored procedure updates the clear date for documents based on certain criteria. It takes a transaction GUID as a parameter. It checks the document's clear days and whether the bank is owned, and increments the clear days if not owned and the subtotal is under $300. It then updates the clear date field on matching documents in the database and returns the current date.

Uploaded by

Dai Castellano
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
8 views3 pages

SQL Procedure for Document Date Update

This stored procedure updates the clear date for documents based on certain criteria. It takes a transaction GUID as a parameter. It checks the document's clear days and whether the bank is owned, and increments the clear days if not owned and the subtotal is under $300. It then updates the clear date field on matching documents in the database and returns the current date.

Uploaded by

Dai Castellano
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

USE [alliance] GO /****** Object: StoredProcedure [acc].

[pr_DocumentsClearDateUpdate] Date: 06/13/2012 10:37:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Script

-- exec [acc].[pr_DocumentsClearDateUpdate] '12EB1FE2-43F7-4AF1-BB58191332D8DD29,0B135EB0-A90C-4E4F-8FFB-5942C9575603' ALTER PROCEDURE [acc].[pr_DocumentsClearDateUpdate] @txnGUIDs uniqueidentifier AS BEGIN TRY BEGIN TRAN DECLARE @clearDays int DECLARE @isOwn bit

SET @clearDays = (select clearDays from [Link] where txnGUID = @txnGUIDs) SET @isOwn = (SELECT [Link] FROM [Link] d INNER JOIN BanksRouting br ON [Link] = [Link] INNER JOIN Bans b ON [Link] = [Link]) WHERE [Link] = @txnGUIDs)

IF((select subtotal from [Link] where txnGUID = @txnGUIDs) < 300) BEGIN IF (@isOwn = 0) BEGIN @clearDays = @clearDays + 1 END END ELSE
UPDATE d SET [Link] = @clearDays FROM [acc].[Documents] d join dbo.fnGUIDList2tbl(@txnGUIDs) g on [Link] = [Link] join [Link] as p on [Link] = [Link] join [Link] as ac on [Link] = [Link] join [Link] as br on [Link] = [Link] join [Link] as b on [Link] = [Link] WHERE [Link] = 'Draft Check' or [Link] = 'Check' COMMIT TRAN END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

ROLLBACK TRAN

END CATCH

SELECT GETDATE() as Today

You might also like