Need some help on how to create a Function.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need some help on how to create a Function.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-16 : 13:21:12

My DBA Lead wants to replace all the UPDATE CASE statements below with calling a Function and I am not
quite sure where to start.
Please see the samples and desired output.

Any help is appreciated.
SQL 2008.

-- Need to create a function and replace all the CASE statements with a Function

Ex:

UPDATE TABLE
SET NOIValidUpdateBy = Call a Function
,...

end

-----------------------------------------------------------------------------

-- Function not working yet. Need some helps.
-- Peso code.

create function dbo.fnIIF(@isSame bit, @trueValue varchar(100), @falseValue varchar(100))

As
if @isSame = 0
return @falseValue
else
return @trueValue
go

-----------------------------------------------------------------------------

--Testing...

EXECute v2.spDefaultDiligenceInserUpdate @LoanNum = '100001',
@LoginId = 'SmithD',
@NOIValid = 'n'
go

SELECT LoanNum
,NOIValid
,NOIValidUpdateBy
,NOIValidUpdateDt
FROM DefaultDiligence;
GO

------------------------------------------------------------------------------

--Existing sp.
IF OBJECT_ID('v2.spDefaultDiligenceInserUpdate', 'p') IS NOT NULL
DROP PROCedure v2.spDefaultDiligenceInserUpdate
GO

CREATE PROCedure [v2].[spDefaultDiligenceInserUpdate]
(
@LoanNum VARCHAR(10)
,@LoginId VARCHAR(60) = NULL
,@Classification VARCHAR(50) = NULL
,@AttorneyTrusteeName VARCHAR(60) = NULL
,@AttorneyTrusteeBilling VARCHAR(60) = NULL
,@FclEntity VARCHAR(60) = NULL
,@DateNOISent DATETIME = NULL
,@NOIDueDate DATETIME = NULL
,@NOIValid CHAR(1) = ''
,@CopyOfLastRecordAOMUploadedToLenstar CHAR(1) = ''
,@CurrentAOMNeeded CHAR(1) = ''
,@CurrentAOMNeededFromDate DATETIME = NULL
,@CurrentAOMNeededToDate DATETIME = NULL
,@InterveningAMONeeded CHAR(1) = ''
,@InterveningAMONeededFromDate DATETIME = NULL
,@InterveningAMONeededToDate DATETIME = NULL
,@VestingStrategy VARCHAR(50) = NULL
,@ContactedAttorneyTrusteeForFCStatus CHAR(1) = ''
,@AttorneyRequiredToUploadFCFileToLenstar CHAR(1) = ''
,@FCInGoodStanding CHAR(1) = ''
,@Litigation CHAR(1) = ''
,@TitleIssues CHAR(1) = ''
,@AttorneyNeedsMoreToProceed CHAR(1) = ''
,@OriginalDocuments CHAR(1) = ''
,@CurrentActivity VARCHAR(500) = NULL
,@NextStep VARCHAR(500) = NULL
,@IssuesAtHand VARCHAR(500) = NULL
,@InterveningAssignmentsNeeded CHAR(1) = ''
,@TSGTitleReportCopyUploadedToLenstar CHAR(1) = ''
,@AttorneyOrderedDateDown CHAR(1) = ''
,@ValidFirstLegal CHAR(1) = ''
,@RecentFCSaleDt DATETIME = NULL
,@NeedToProvideBID CHAR(1) = ''
,@CompletedByCheck CHAR(1) = ''
,@CompletedByUserId VARCHAR(60) = NULL
,@ManagerTeamLeaderByCheck CHAR(1) = ''
,@ManagerTeamLeaderUserId VARCHAR(60) = NULL
,@DirectorByCheck CHAR(1) = ''
,@DirectorUserId VARCHAR(60) = NULL
,@Comments VARCHAR(MAX) = NULL
)
AS
/***********************************************************************************************
** Modifications:
** ----------------------------------
** Date: Author: Reasons:
** ------------+-----------------------------------------------------------------------
**
**
***********************************************************************************************/
SET nocount ON
DECLARE @ErrorMessage VARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT

SET @NOIValid = ISNULL(@NOIValid, '')
SET @CopyOfLastRecordAOMUploadedToLenstar = ISNULL(@CopyOfLastRecordAOMUploadedToLenstar, '')
SET @CurrentAOMNeeded = ISNULL(@CurrentAOMNeeded, '')

SET @InterveningAMONeeded = ISNULL(@InterveningAMONeeded, '')
SET @ContactedAttorneyTrusteeForFCStatus = ISNULL(@ContactedAttorneyTrusteeForFCStatus, '')
SET @AttorneyRequiredToUploadFCFileToLenstar = ISNULL(@AttorneyRequiredToUploadFCFileToLenstar, '')
SET @FCInGoodStanding = ISNULL(@FCInGoodStanding, '')
SET @Litigation = ISNULL(@Litigation, '')
SET @AttorneyNeedsMoreToProceed = ISNULL(@AttorneyNeedsMoreToProceed, '')
SET @OriginalDocuments = ISNULL(@OriginalDocuments, '')
SET @InterveningAssignmentsNeeded = ISNULL(@InterveningAssignmentsNeeded, '')
SET @TSGTitleReportCopyUploadedToLenstar = ISNULL(@TSGTitleReportCopyUploadedToLenstar, '')
SET @AttorneyOrderedDateDown = ISNULL(@AttorneyOrderedDateDown, '')
SET @ValidFirstLegal = ISNULL(@ValidFirstLegal, '')
SET @NeedToProvideBID = ISNULL(@NeedToProvideBID, '')
SET @CompletedByCheck = ISNULL(@CompletedByCheck, '')
SET @ManagerTeamLeaderByCheck = ISNULL(@ManagerTeamLeaderByCheck, '')
SET @DirectorByCheck = ISNULL(@DirectorByCheck, '')

--------------------------------------------------------------------------------------------------------

BEGIN TRY

IF NOT EXISTS (SELECT 1 FROM dbo.DefaultDiligence WHERE LoanNum = @LoanNum)
BEGIN

INSERT dbo.DefaultDiligence (LoanNum, InsertBy, InsertDt, UpdateBy)
SELECT @LoanNum, @LoginId, GETDATE(), @LoginId

END

-- ContactedAttorneyTrusteeForFCStatusUpdateBy,

BEGIN

UPDATE dbo.DefaultDiligence
SET Classification = @Classification
,AttorneyTrusteeName = @AttorneyTrusteeName
,AttorneyTrusteeBilling = @AttorneyTrusteeBilling
,FclEntity = @FclEntity
,DateNOISent = @DateNOISent
,NOIDueDate = @NOIDueDate
,NOIValid = @NOIValid
,NOIValidUpdateBy = [yellow]CASE
WHEN NOIValid <> @NOIValid THEN @LoginId
WHEN NOIValid = @NOIValid THEN NOIValidUpdateBy
END
,NOIValidUpdateDt = CASE
WHEN NOIValid <> @NOIValid THEN GETDATE()
WHEN NOIValid = @NOIValid THEN NOIValidUpdateDt
END ,CopyOfLastRecordAOMUploadedToLenstar = @CopyOfLastRecordAOMUploadedToLenstar
,CurrentAOMNeeded = @CurrentAOMNeeded
,CurrentAOMNeededFromDate = @CurrentAOMNeededFromDate
,CurrentAOMNeededToDate = @CurrentAOMNeededToDate
,InterveningAMONeeded = @InterveningAMONeeded
,InterveningAMONeededFromDate = @InterveningAMONeededFromDate
,InterveningAMONeededToDate = @InterveningAMONeededToDate
,VestingStrategy = @VestingStrategy

,ContactedAttorneyTrusteeForFCStatus = @ContactedAttorneyTrusteeForFCStatus
,ContactedAttorneyTrusteeForFCStatusUpdateBy = CASE
WHEN ContactedAttorneyTrusteeForFCStatus <> @ContactedAttorneyTrusteeForFCStatus THEN @LoginId
WHEN ContactedAttorneyTrusteeForFCStatus = @ContactedAttorneyTrusteeForFCStatus THEN ContactedAttorneyTrusteeForFCStatusUpdateBy
END
,ContactedAttorneyTrusteeForFCStatusUpdateDt = CASE
WHEN ContactedAttorneyTrusteeForFCStatus <> @ContactedAttorneyTrusteeForFCStatus THEN GETDATE()
WHEN ContactedAttorneyTrusteeForFCStatus = @ContactedAttorneyTrusteeForFCStatus THEN ContactedAttorneyTrusteeForFCStatusUpdateDt
END ,AttorneyRequiredToUploadFCFileToLenstar = @AttorneyRequiredToUploadFCFileToLenstar
,AttorneyRequiredToUploadFCFileToLenstarUpdateBy = CASE
WHEN AttorneyRequiredToUploadFCFileToLenstar <> @AttorneyRequiredToUploadFCFileToLenstar THEN @LoginId
WHEN AttorneyRequiredToUploadFCFileToLenstar = @AttorneyRequiredToUploadFCFileToLenstar THEN AttorneyRequiredToUploadFCFileToLenstarUpdateBy
END
,AttorneyRequiredToUploadFCFileToLenstarUpdateDt = CASE
WHEN AttorneyRequiredToUploadFCFileToLenstar <> @AttorneyRequiredToUploadFCFileToLenstar THEN GETDATE()
WHEN AttorneyRequiredToUploadFCFileToLenstar = @AttorneyRequiredToUploadFCFileToLenstar THEN AttorneyRequiredToUploadFCFileToLenstarUpdateDt
END
,FCInGoodStanding = @FCInGoodStanding
,FCInGoodStandingUpdateBy = CASE
WHEN FCInGoodStanding <> @FCInGoodStanding THEN @LoginId
WHEN FCInGoodStanding = @FCInGoodStanding THEN FCInGoodStandingUpdateBy
END
,FCInGoodStandingUpdateDt = CASE
WHEN FCInGoodStanding <> @FCInGoodStanding THEN GETDATE()
WHEN FCInGoodStanding = @FCInGoodStanding THEN FCInGoodStandingUpdateDt
END
,Litigation = @Litigation
,LitigationUpdateBy = CASE
WHEN Litigation <> @Litigation THEN @LoginId
WHEN Litigation = @Litigation THEN LitigationUpdateBy
END
,LitigationUpdateDt = CASE
WHEN Litigation <> @Litigation THEN GETDATE()
WHEN Litigation = @Litigation THEN LitigationUpdateDt
END

,TitleIssues = @TitleIssues
,TitleIssuesUpdateBy = CASE
WHEN TitleIssues <> @TitleIssues THEN @LoginId
WHEN TitleIssues = @TitleIssues THEN TitleIssuesUpdateBy
END
,TitleIssuesUpdateDt = CASE
WHEN TitleIssues <> @TitleIssues THEN GETDATE()
WHEN TitleIssues = @TitleIssues THEN TitleIssuesUpdateDt
END

,AttorneyNeedsMoreToProceed = @AttorneyNeedsMoreToProceed
,AttorneyNeedsMoreToProceedUpdateBy = CASE
WHEN AttorneyNeedsMoreToProceed <> @AttorneyNeedsMoreToProceed THEN @LoginId
WHEN AttorneyNeedsMoreToProceed = @AttorneyNeedsMoreToProceed THEN AttorneyNeedsMoreToProceedUpdateBy
END
,AttorneyNeedsMoreToProceedUpdateDt = CASE
WHEN AttorneyNeedsMoreToProceed <> @AttorneyNeedsMoreToProceed THEN GETDATE()
WHEN AttorneyNeedsMoreToProceed = @AttorneyNeedsMoreToProceed THEN AttorneyNeedsMoreToProceedUpdateDt
END
,OriginalDocuments = @OriginalDocuments
,OriginalDocumentsUpdateBy = CASE
WHEN OriginalDocuments <> @OriginalDocuments THEN @LoginId
WHEN OriginalDocuments = @OriginalDocuments THEN OriginalDocumentsUpdateBy
END
,OriginalDocumentsUpdateDt = CASE
WHEN OriginalDocuments <> @OriginalDocuments THEN GETDATE()
WHEN OriginalDocuments = @OriginalDocuments THEN OriginalDocumentsUpdateDt
END
,CurrentActivity = @CurrentActivity
,NextStep = @NextStep
,IssuesAtHand = @IssuesAtHand
,InterveningAssignmentsNeeded = @InterveningAssignmentsNeeded
,InterveningAssignmentsUpdateBy = CASE
WHEN InterveningAssignmentsNeeded <> @InterveningAssignmentsNeeded THEN @LoginId
WHEN InterveningAssignmentsNeeded = @InterveningAssignmentsNeeded THEN InterveningAssignmentsUpdateBy
END
,InterveningAssignmentsUpdateDt = CASE
WHEN InterveningAssignmentsNeeded <> @InterveningAssignmentsNeeded THEN GETDATE()
WHEN InterveningAssignmentsNeeded = @InterveningAssignmentsNeeded THEN InterveningAssignmentsUpdateDt
END

,TSGTitleReportCopyUploadedToLenstar = @TSGTitleReportCopyUploadedToLenstar
,TSGTitleReportUpdateBy = CASE
WHEN TSGTitleReportCopyUploadedToLenstar <> @TSGTitleReportCopyUploadedToLenstar THEN @LoginId
WHEN TSGTitleReportCopyUploadedToLenstar = @TSGTitleReportCopyUploadedToLenstar THEN TSGTitleReportUpdateBy
END
,TSGTitleReportUpdateDt = CASE
WHEN TSGTitleReportCopyUploadedToLenstar <> @TSGTitleReportCopyUploadedToLenstar THEN GETDATE()
WHEN TSGTitleReportCopyUploadedToLenstar = @TSGTitleReportCopyUploadedToLenstar THEN TSGTitleReportUpdateDt
END

,AttorneyOrderedDateDown = @AttorneyOrderedDateDown
,AttorneyOrderedDateDownUpdateBy = CASE
WHEN AttorneyOrderedDateDown <> @AttorneyOrderedDateDown THEN @LoginId
WHEN AttorneyOrderedDateDown = @AttorneyOrderedDateDown THEN AttorneyOrderedDateDownUpdateBy
END
,AttorneyOrderedDateDownUpdateDt = CASE
WHEN AttorneyOrderedDateDown <> @AttorneyOrderedDateDown THEN GETDATE()
WHEN AttorneyOrderedDateDown = @AttorneyOrderedDateDown THEN AttorneyOrderedDateDownUpdateDt
END
,ValidFirstLegal = @ValidFirstLegal
,ValidFirstLegalUpdateBy = CASE
WHEN ValidFirstLegal <> @ValidFirstLegal THEN @LoginId
WHEN ValidFirstLegal = @ValidFirstLegal THEN ValidFirstLegalUpdateBy
END
,ValidFirstLegalUpdateDt = CASE
WHEN ValidFirstLegal <> @ValidFirstLegal THEN GETDATE()
WHEN ValidFirstLegal = @ValidFirstLegal THEN ValidFirstLegalUpdateDt
END
,RecentFCSaleDt = @RecentFCSaleDt

,NeedToProvideBID = @NeedToProvideBID
,NeedToProvideBIDUpdateBy = CASE
WHEN NeedToProvideBID <> @NeedToProvideBID THEN @LoginId
WHEN NeedToProvideBID = @NeedToProvideBID THEN NeedToProvideBIDUpdateBy
END
,NeedToProvideBIDUpdateDt = CASE
WHEN NeedToProvideBID <> @NeedToProvideBID THEN GETDATE()
WHEN NeedToProvideBID = @NeedToProvideBID THEN NeedToProvideBIDUpdateDt
END
,CompletedByCheck = @CompletedByCheck
,CompletedByUserId = @CompletedByUserId
,ManagerTeamLeaderByCheck = @ManagerTeamLeaderByCheck
,ManagerTeamLeaderUserId = @ManagerTeamLeaderUserId
,DirectorByCheck = @DirectorByCheck
,DirectorUserId = @DirectorUserId
,UpdateBy = @LoginId
,UpdateDt = GETDATE()
,Comments = @Comments
WHERE (LoanNum = @LoanNum)

END

END TRY

BEGIN CATCH

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

END CATCH
SET nocount OFF
GO

---------------------------------------------------------

IF OBJECT_ID('DefaultDiligence', 'u') IS NOT NULL
DROP TABLE dbo.DefaultDiligence
GO

CREATE TABLE dbo.DefaultDiligence
(
LoanNum varchar(10) NOT NULL,
Classification VARCHAR(50) NULL,
AttorneyTrusteeName varchar (60) NULL,
AttorneyTrusteeBilling VARCHAR(60) NULL,
FclEntity VARCHAR(60) NULL,
DateNOISent DATETIME NULL,
NOIDueDate datetime NULL,
NOIValid CHAR(1) NOT NULL DEFAULT '',
NOIValidUpdateBy VARCHAR(60) NULL,
NOIValidUpdateDt DATETIME NULL,
CopyOfLastRecordAOMUploadedToLenstar CHAR(1) NOT NULL DEFAULT '',
CurrentAOMNeeded CHAR(1) NOT NULL DEFAULT '',
CurrentAOMNeededFromDate datetime NULL,
CurrentAOMNeededToDate datetime NULL,
InterveningAMONeeded CHAR(1) NOT NULL DEFAULT '',
InterveningAMONeededFromDate datetime NULL,
InterveningAMONeededToDate datetime NULL,
VestingStrategy VARCHAR(50) NULL,
ContactedAttorneyTrusteeForFCStatus CHAR(1) NOT NULL DEFAULT '',
ContactedAttorneyTrusteeForFCStatusUpdateBy VARCHAR(60) NULL,
ContactedAttorneyTrusteeForFCStatusUpdateDt DATETIME NULL,
AttorneyRequiredToUploadFCFileToLenstar CHAR(1) NOT NULL DEFAULT '',
AttorneyRequiredToUploadFCFileToLenstarUpdateBy VARCHAR(60) NULL,
AttorneyRequiredToUploadFCFileToLenstarUpdateDt DATETIME NULL,
FCInGoodStanding CHAR(1) NOT NULL DEFAULT '',
FCInGoodStandingUpdateBy VARCHAR(60) NULL,
FCInGoodStandingUpdateDt DATETIME NULL,
Litigation CHAR(1) NOT NULL DEFAULT '',
LitigationUpdateBy VARCHAR(60) NULL,
LitigationUpdateDt DATETIME NULL,
TitleIssues CHAR(1) NOT NULL DEFAULT '',
TitleIssuesUpdateBy VARCHAR(60) NULL,
TitleIssuesUpdateDt DATETIME NULL,
AttorneyNeedsMoreToProceed CHAR(1) NOT NULL DEFAULT '',
AttorneyNeedsMoreToProceedUpdateBy VARCHAR(60) NULL,
AttorneyNeedsMoreToProceedUpdateDt DATETIME NULL,
OriginalDocuments CHAR(1) NOT NULL DEFAULT '',
OriginalDocumentsUpdateBy VARCHAR(60) NULL,
OriginalDocumentsUpdateDt DATETIME NULL,
CurrentActivity VARCHAR(500) NULL,
NextStep VARCHAR(500) NULL,
IssuesAtHand VARCHAR(500) NULL,
InterveningAssignmentsNeeded CHAR(1) NOT NULL DEFAULT '',
InterveningAssignmentsUpdateBy VARCHAR(60) NULL,
InterveningAssignmentsUpdateDt DATETIME NULL,
TSGTitleReportCopyUploadedToLenstar CHAR(1) NOT NULL DEFAULT '',
TSGTitleReportUpdateBy VARCHAR(60) NULL,
TSGTitleReportUpdateDt DATETIME NULL,
AttorneyOrderedDateDown CHAR(1) NOT NULL DEFAULT '',
AttorneyOrderedDateDownUpdateBy VARCHAR(60) NULL,
AttorneyOrderedDateDownUpdateDt DATETIME NULL,
ValidFirstLegal CHAR(1) NOT NULL DEFAULT '',
ValidFirstLegalUpdateBy VARCHAR(60) NULL,
ValidFirstLegalUpdateDt DATETIME NULL,
RecentFCSaleDt DATETIME NULL,
NeedToProvideBID CHAR(1) NOT NULL DEFAULT '',
NeedToProvideBIDUpdateBy VARCHAR(60) NULL,
NeedToProvideBIDUpdateDt DATETIME NULL,
CompletedByCheck CHAR(1) NOT NULL DEFAULT '',
CompletedByUserId VARCHAR(60) NULL,
ManagerTeamLeaderByCheck CHAR(1) NOT NULL DEFAULT '',
ManagerTeamLeaderUserId VARCHAR(60) NULL,
DirectorByCheck CHAR(1) NOT NULL DEFAULT '',
DirectorUserId VARCHAR(60) NULL,
InsertBy varchar(50) NULL,
InsertDt datetime NULL,
UpdateBy varchar(50) NULL,
UpdateDt datetime NULL,
Comments VARCHAR(MAX) NULL
)
GO

ALTER TABLE dbo.DefaultDiligence
ADD CONSTRAINT XPKDefaultDiligence PRIMARY KEY CLUSTERED (LoanNum)
GO

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-12-16 : 14:37:38

Create function fn_CompareItems(
@val1 varchar(50)
,@val2 varchar(50)
,@Trueval varchar(50)
,@Falseval varchar(50)
)
returns varchar(50)
as
begin
declare @myval as varchar(50)
select @Myval = case when val1 <> val2 THEN @FalseVal
else @TrueVal
END
return @myval
end
go

--You can use like so. I would not use a function here though,
Update...
SET Classification = @Classification
,AttorneyTrusteeName = @AttorneyTrusteeName
,AttorneyTrusteeBilling = @AttorneyTrusteeBilling
,FclEntity = @FclEntity
,DateNOISent = @DateNOISent
,NOIDueDate = @NOIDueDate
,NOIValid = @NOIValid
,NOIValidUpdateBy = fn_CompareItems(NOIValid ,@NOIValidid,@NOIValidid,@LoginID)
...

I would not suggest using a universal function here due to differant item types, I would use the case statement below. There is nothing gained from using a function here in my opinion.

,NOIValidUpdateBy = case when not @NOIValidid = NOIValid then @LoginID else NOIValidUpdateBy end

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-16 : 15:39:23

quote:
My DBA Lead wants to replace all the UPDATE CASE statements below with calling a Function

Your DBA must not care about performance, a UDF will perform badly here. Unless they can provide a solid reason (NOT "because I said so") I don't recommend you change it.

quote:
I would not suggest using a universal function here due to differant item types, I would use the case statement below. There is nothing gained from using a function here in my opinion.

Very wise words you should heed.

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-16 : 16:05:13

I try to tell him the function would not work well in this case but he insist to use it any way. He is my supervisor otherwise; I would ask him to take SQL 101 Function course.

Hi Vinnie881,

Thank you for the Function codes but when I run the test below, the results wrong.
Please help...

Thanks so much for your help.

EXECute v2.spDefaultDiligenceInserUpdate1 @LoanNum = '100001',
@LoginId = 'Nguyenl',
@NOIValid = 'n'
go

SELECT LoanNum
,NOIValid
,NOIValidUpdateBy
,NOIValidUpdateDt
FROM DefaultDiligence;
GO

--Result wrong.

LoanNum NOIValid NOIValidUpdateBy NOIValidUpdateDt
---------- -------- ------------------------------------------------------------ -----------------------
100001 n n 2010-12-16 12:58:24.957

IF OBJECT_ID('v2.spDefaultDiligenceInserUpdate1', 'p') IS NOT NULL
DROP PROCedure v2.spDefaultDiligenceInserUpdate1
GO

CREATE PROCedure [v2].[spDefaultDiligenceInserUpdate1]
(
@LoanNum VARCHAR(10)
,@LoginId VARCHAR(60) = NULL
,@Classification VARCHAR(50) = NULL
,@AttorneyTrusteeName VARCHAR(60) = NULL
,@AttorneyTrusteeBilling VARCHAR(60) = NULL
,@FclEntity VARCHAR(60) = NULL
,@DateNOISent DATETIME = NULL
,@NOIDueDate DATETIME = NULL
,@NOIValid CHAR(1) = ''
,@CopyOfLastRecordAOMUploadedToLenstar CHAR(1) = ''
,@CurrentAOMNeeded CHAR(1) = ''
,@CurrentAOMNeededFromDate DATETIME = NULL
,@CurrentAOMNeededToDate DATETIME = NULL
,@InterveningAMONeeded CHAR(1) = ''
,@InterveningAMONeededFromDate DATETIME = NULL
,@InterveningAMONeededToDate DATETIME = NULL
,@VestingStrategy VARCHAR(50) = NULL
,@ContactedAttorneyTrusteeForFCStatus CHAR(1) = ''
,@AttorneyRequiredToUploadFCFileToLenstar CHAR(1) = ''
,@FCInGoodStanding CHAR(1) = ''
,@Litigation CHAR(1) = ''
,@TitleIssues CHAR(1) = ''
,@AttorneyNeedsMoreToProceed CHAR(1) = ''
,@OriginalDocuments CHAR(1) = ''
,@CurrentActivity VARCHAR(500) = NULL
,@NextStep VARCHAR(500) = NULL
,@IssuesAtHand VARCHAR(500) = NULL
,@InterveningAssignmentsNeeded CHAR(1) = ''
,@TSGTitleReportCopyUploadedToLenstar CHAR(1) = ''
,@AttorneyOrderedDateDown CHAR(1) = ''
,@ValidFirstLegal CHAR(1) = ''
,@RecentFCSaleDt DATETIME = NULL
,@NeedToProvideBID CHAR(1) = ''
,@CompletedByCheck CHAR(1) = ''
,@CompletedByUserId VARCHAR(60) = NULL
,@ManagerTeamLeaderByCheck CHAR(1) = ''
,@ManagerTeamLeaderUserId VARCHAR(60) = NULL
,@DirectorByCheck CHAR(1) = ''
,@DirectorUserId VARCHAR(60) = NULL
,@Comments VARCHAR(MAX) = NULL
)
AS
/***********************************************************************************************
** Modifications:
** ----------------------------------
** Date: Author: Reasons:
** ------------+-----------------------------------------------------------------------
**
**
***********************************************************************************************/
SET nocount ON
DECLARE @ErrorMessage VARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT

SET @NOIValid = ISNULL(@NOIValid, '')
SET @CopyOfLastRecordAOMUploadedToLenstar = ISNULL(@CopyOfLastRecordAOMUploadedToLenstar, '')
SET @CurrentAOMNeeded = ISNULL(@CurrentAOMNeeded, '')

SET @InterveningAMONeeded = ISNULL(@InterveningAMONeeded, '')
SET @ContactedAttorneyTrusteeForFCStatus = ISNULL(@ContactedAttorneyTrusteeForFCStatus, '')
SET @AttorneyRequiredToUploadFCFileToLenstar = ISNULL(@AttorneyRequiredToUploadFCFileToLenstar, '')
SET @FCInGoodStanding = ISNULL(@FCInGoodStanding, '')
SET @Litigation = ISNULL(@Litigation, '')
SET @AttorneyNeedsMoreToProceed = ISNULL(@AttorneyNeedsMoreToProceed, '')
SET @OriginalDocuments = ISNULL(@OriginalDocuments, '')
SET @InterveningAssignmentsNeeded = ISNULL(@InterveningAssignmentsNeeded, '')
SET @TSGTitleReportCopyUploadedToLenstar = ISNULL(@TSGTitleReportCopyUploadedToLenstar, '')
SET @AttorneyOrderedDateDown = ISNULL(@AttorneyOrderedDateDown, '')
SET @ValidFirstLegal = ISNULL(@ValidFirstLegal, '')
SET @NeedToProvideBID = ISNULL(@NeedToProvideBID, '')
SET @CompletedByCheck = ISNULL(@CompletedByCheck, '')
SET @ManagerTeamLeaderByCheck = ISNULL(@ManagerTeamLeaderByCheck, '')
SET @DirectorByCheck = ISNULL(@DirectorByCheck, '')

--------------------------------------------------------------------------------------------------------

BEGIN TRY

IF NOT EXISTS (SELECT 1 FROM dbo.DefaultDiligence WHERE LoanNum = @LoanNum)
BEGIN

INSERT dbo.DefaultDiligence (LoanNum, InsertBy, InsertDt, UpdateBy)
SELECT @LoanNum, @LoginId, GETDATE(), @LoginId

END

-- ContactedAttorneyTrusteeForFCStatusUpdateBy,

BEGIN

UPDATE dbo.DefaultDiligence
SET Classification = @Classification
,AttorneyTrusteeName = @AttorneyTrusteeName
,AttorneyTrusteeBilling = @AttorneyTrusteeBilling
,FclEntity = @FclEntity
,DateNOISent = @DateNOISent
,NOIDueDate = @NOIDueDate
,NOIValid = @NOIValid
,NOIValidUpdateBy = dbo.fn_CompareItems(NOIValid, @NOIValid, @NOIValid, @LoginID)

--,NOIValidUpdateBy = CASE
-- WHEN @NOIValid <> NOIValid THEN @LoginID
-- ELSE NOIValidUpdateBy
-- END

,NOIValidUpdateDt = CASE
WHEN NOIValid <> @NOIValid THEN GETDATE()
WHEN NOIValid = @NOIValid THEN NOIValidUpdateDt
END
,CopyOfLastRecordAOMUploadedToLenstar = @CopyOfLastRecordAOMUploadedToLenstar
,CurrentAOMNeeded = @CurrentAOMNeeded
,CurrentAOMNeededFromDate = @CurrentAOMNeededFromDate
,CurrentAOMNeededToDate = @CurrentAOMNeededToDate
,InterveningAMONeeded = @InterveningAMONeeded
,InterveningAMONeededFromDate = @InterveningAMONeededFromDate
,InterveningAMONeededToDate = @InterveningAMONeededToDate
,VestingStrategy = @VestingStrategy

,ContactedAttorneyTrusteeForFCStatus = @ContactedAttorneyTrusteeForFCStatus
,ContactedAttorneyTrusteeForFCStatusUpdateBy = CASE
WHEN ContactedAttorneyTrusteeForFCStatus <> @ContactedAttorneyTrusteeForFCStatus THEN @LoginId
WHEN ContactedAttorneyTrusteeForFCStatus = @ContactedAttorneyTrusteeForFCStatus THEN ContactedAttorneyTrusteeForFCStatusUpdateBy
END
,ContactedAttorneyTrusteeForFCStatusUpdateDt = CASE
WHEN ContactedAttorneyTrusteeForFCStatus <> @ContactedAttorneyTrusteeForFCStatus THEN GETDATE()
WHEN ContactedAttorneyTrusteeForFCStatus = @ContactedAttorneyTrusteeForFCStatus THEN ContactedAttorneyTrusteeForFCStatusUpdateDt
END
,AttorneyRequiredToUploadFCFileToLenstar = @AttorneyRequiredToUploadFCFileToLenstar
,AttorneyRequiredToUploadFCFileToLenstarUpdateBy = CASE
WHEN AttorneyRequiredToUploadFCFileToLenstar <> @AttorneyRequiredToUploadFCFileToLenstar THEN @LoginId
WHEN AttorneyRequiredToUploadFCFileToLenstar = @AttorneyRequiredToUploadFCFileToLenstar THEN AttorneyRequiredToUploadFCFileToLenstarUpdateBy
END
,AttorneyRequiredToUploadFCFileToLenstarUpdateDt = CASE
WHEN AttorneyRequiredToUploadFCFileToLenstar <> @AttorneyRequiredToUploadFCFileToLenstar THEN GETDATE()
WHEN AttorneyRequiredToUploadFCFileToLenstar = @AttorneyRequiredToUploadFCFileToLenstar THEN AttorneyRequiredToUploadFCFileToLenstarUpdateDt
END
,FCInGoodStanding = @FCInGoodStanding
,FCInGoodStandingUpdateBy = CASE
WHEN FCInGoodStanding <> @FCInGoodStanding THEN @LoginId
WHEN FCInGoodStanding = @FCInGoodStanding THEN FCInGoodStandingUpdateBy
END
,FCInGoodStandingUpdateDt = CASE
WHEN FCInGoodStanding <> @FCInGoodStanding THEN GETDATE()
WHEN FCInGoodStanding = @FCInGoodStanding THEN FCInGoodStandingUpdateDt
END
,Litigation = @Litigation
,LitigationUpdateBy = CASE
WHEN Litigation <> @Litigation THEN @LoginId
WHEN Litigation = @Litigation THEN LitigationUpdateBy
END
,LitigationUpdateDt = CASE
WHEN Litigation <> @Litigation THEN GETDATE()
WHEN Litigation = @Litigation THEN LitigationUpdateDt
END

,TitleIssues = @TitleIssues
,TitleIssuesUpdateBy = CASE
WHEN TitleIssues <> @TitleIssues THEN @LoginId
WHEN TitleIssues = @TitleIssues THEN TitleIssuesUpdateBy
END
,TitleIssuesUpdateDt = CASE
WHEN TitleIssues <> @TitleIssues THEN GETDATE()
WHEN TitleIssues = @TitleIssues THEN TitleIssuesUpdateDt
END

,AttorneyNeedsMoreToProceed = @AttorneyNeedsMoreToProceed
,AttorneyNeedsMoreToProceedUpdateBy = CASE
WHEN AttorneyNeedsMoreToProceed <> @AttorneyNeedsMoreToProceed THEN @LoginId
WHEN AttorneyNeedsMoreToProceed = @AttorneyNeedsMoreToProceed THEN AttorneyNeedsMoreToProceedUpdateBy
END
,AttorneyNeedsMoreToProceedUpdateDt = CASE
WHEN AttorneyNeedsMoreToProceed <> @AttorneyNeedsMoreToProceed THEN GETDATE()
WHEN AttorneyNeedsMoreToProceed = @AttorneyNeedsMoreToProceed THEN AttorneyNeedsMoreToProceedUpdateDt
END
,OriginalDocuments = @OriginalDocuments
,OriginalDocumentsUpdateBy = CASE
WHEN OriginalDocuments <> @OriginalDocuments THEN @LoginId
WHEN OriginalDocuments = @OriginalDocuments THEN OriginalDocumentsUpdateBy
END
,OriginalDocumentsUpdateDt = CASE
WHEN OriginalDocuments <> @OriginalDocuments THEN GETDATE()
WHEN OriginalDocuments = @OriginalDocuments THEN OriginalDocumentsUpdateDt
END
,CurrentActivity = @CurrentActivity
,NextStep = @NextStep
,IssuesAtHand = @IssuesAtHand
,InterveningAssignmentsNeeded = @InterveningAssignmentsNeeded
,InterveningAssignmentsUpdateBy = CASE
WHEN InterveningAssignmentsNeeded <> @InterveningAssignmentsNeeded THEN @LoginId
WHEN InterveningAssignmentsNeeded = @InterveningAssignmentsNeeded THEN InterveningAssignmentsUpdateBy
END
,InterveningAssignmentsUpdateDt = CASE
WHEN InterveningAssignmentsNeeded <> @InterveningAssignmentsNeeded THEN GETDATE()
WHEN InterveningAssignmentsNeeded = @InterveningAssignmentsNeeded THEN InterveningAssignmentsUpdateDt
END

,TSGTitleReportCopyUploadedToLenstar = @TSGTitleReportCopyUploadedToLenstar
,TSGTitleReportUpdateBy = CASE
WHEN TSGTitleReportCopyUploadedToLenstar <> @TSGTitleReportCopyUploadedToLenstar THEN @LoginId
WHEN TSGTitleReportCopyUploadedToLenstar = @TSGTitleReportCopyUploadedToLenstar THEN TSGTitleReportUpdateBy
END
,TSGTitleReportUpdateDt = CASE
WHEN TSGTitleReportCopyUploadedToLenstar <> @TSGTitleReportCopyUploadedToLenstar THEN GETDATE()
WHEN TSGTitleReportCopyUploadedToLenstar = @TSGTitleReportCopyUploadedToLenstar THEN TSGTitleReportUpdateDt
END

,AttorneyOrderedDateDown = @AttorneyOrderedDateDown
,AttorneyOrderedDateDownUpdateBy = CASE
WHEN AttorneyOrderedDateDown <> @AttorneyOrderedDateDown THEN @LoginId
WHEN AttorneyOrderedDateDown = @AttorneyOrderedDateDown THEN AttorneyOrderedDateDownUpdateBy
END
,AttorneyOrderedDateDownUpdateDt = CASE
WHEN AttorneyOrderedDateDown <> @AttorneyOrderedDateDown THEN GETDATE()
WHEN AttorneyOrderedDateDown = @AttorneyOrderedDateDown THEN AttorneyOrderedDateDownUpdateDt
END
,ValidFirstLegal = @ValidFirstLegal
,ValidFirstLegalUpdateBy = CASE
WHEN ValidFirstLegal <> @ValidFirstLegal THEN @LoginId
WHEN ValidFirstLegal = @ValidFirstLegal THEN ValidFirstLegalUpdateBy
END
,ValidFirstLegalUpdateDt = CASE
WHEN ValidFirstLegal <> @ValidFirstLegal THEN GETDATE()
WHEN ValidFirstLegal = @ValidFirstLegal THEN ValidFirstLegalUpdateDt
END
,RecentFCSaleDt = @RecentFCSaleDt

,NeedToProvideBID = @NeedToProvideBID
,NeedToProvideBIDUpdateBy = CASE
WHEN NeedToProvideBID <> @NeedToProvideBID THEN @LoginId
WHEN NeedToProvideBID = @NeedToProvideBID THEN NeedToProvideBIDUpdateBy
END
,NeedToProvideBIDUpdateDt = CASE
WHEN NeedToProvideBID <> @NeedToProvideBID THEN GETDATE()
WHEN NeedToProvideBID = @NeedToProvideBID THEN NeedToProvideBIDUpdateDt
END
,CompletedByCheck = @CompletedByCheck
,CompletedByUserId = @CompletedByUserId
,ManagerTeamLeaderByCheck = @ManagerTeamLeaderByCheck
,ManagerTeamLeaderUserId = @ManagerTeamLeaderUserId
,DirectorByCheck = @DirectorByCheck
,DirectorUserId = @DirectorUserId
,UpdateBy = @LoginId
,UpdateDt = GETDATE()
,Comments = @Comments
WHERE (LoanNum = @LoanNum)

END

END TRY

BEGIN CATCH

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

END CATCH
SET nocount OFF
GO

--end
---------------------------------------------

quote:
Originally posted by Vinnie881

Create function fn_CompareItems(
@val1 varchar(50)
,@val2 varchar(50)
,@Trueval varchar(50)
,@Falseval varchar(50)
)
returns varchar(50)
as
begin
declare @myval as varchar(50)
select @Myval = case when val1 <> val2 THEN @FalseVal
else @TrueVal
END
return @myval
end
go

--You can use like so. I would not use a function here though,
Update...
SET Classification = @Classification
,AttorneyTrusteeName = @AttorneyTrusteeName
,AttorneyTrusteeBilling = @AttorneyTrusteeBilling
,FclEntity = @FclEntity
,DateNOISent = @DateNOISent
,NOIDueDate = @NOIDueDate
,NOIValid = @NOIValid
,NOIValidUpdateBy = fn_CompareItems(NOIValid ,@NOIValidid,@NOIValidid,@LoginID)
...

I would not suggest using a universal function here due to differant item types, I would use the case statement below. There is nothing gained from using a function here in my opinion.

,NOIValidUpdateBy = case when not @NOIValidid = NOIValidUpdateBy then @LoginID else NOIValidUpdateBy end

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-16 : 16:59:54

Yeah, replacing a CASE function with a user defined function... pretty pointless. You could simplify things ever so slightly as the second THEN is not needed:

CASE
    WHEN NOIValid = @NOIValid THEN NOIValidUpdateBy
    ELSE @LoginId
END

--Instead of
CASE
    WHEN NOIValid <> @NOIValid THEN @LoginId
    WHEN NOIValid = @NOIValid THEN NOIValidUpdateBy
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources