[usp_CashOfferSELECTNew]

gistfile1.sql

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