Skip to content

Instantly share code, notes, and snippets.

@Fodsuk
Created April 17, 2012 14:57
Show Gist options
  • Save Fodsuk/2406528 to your computer and use it in GitHub Desktop.
Save Fodsuk/2406528 to your computer and use it in GitHub Desktop.
[usp_CashOfferSELECTNew]
USE [NewBusiness]
GO
/****** Object: StoredProcedure [dbo].[usp_CashOfferSELECTNew] Script Date: 04/17/2012 14:53:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mike Rodda
-- Create date: Apil 2012
-- Description: Get Cash Offers for application
-- =============================================
ALTER PROCEDURE [dbo].[usp_CashOfferSELECTNew]
(
@WorkFlowID uniqueidentifier
)
AS
--Turn on forced termination on error
SET XACT_ABORT ON
--Beging the global try
BEGIN TRY
BEGIN TRANSACTION
DECLARE @ErrorString varchar(max)
DECLARE @CaseID int
--Check the parameters are being passed complete
IF (@WorkflowID IS NULL )
BEGIN
RAISERROR('No Workflow ID was passed please supply a case offers',16,1)
END
--Get the case for this WorkFlowID
SET @CaseID = (SELECT RowID
FROM NewBusiness.dbo.t_Case
WHERE WorkFlowID = @WorkflowID)
SELECT O.RowID,
O.[Description]
FROM t_Offer O
INNER JOIN t_CaseOffer CO ON CO.OfferID = O.RowID
INNER JOIN t_CaseProposal CP ON CO.CaseProposalID = CP.RowID
AND CP.CaseID = @CaseID
WHERE O.[Name] LIKE 'CASH%'
COMMIT TRANSACTION
END TRY
--Begin the global catch
BEGIN CATCH
--Return the error info
SET @ErrorString = 'Error In: ' + ERROR_PROCEDURE() + CHAR(13) + CHAR(10) +
'On Line: ' + convert(varchar,ERROR_LINE()) + CHAR(13) + CHAR(10) +
'Message: ' + ERROR_MESSAGE()
SELECT @ErrorString
RAISERROR(@ErrorString,16,1)
--If an error occured rollback any changes
ROLLBACK TRANSACTION
END CATCH
RETURN
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment