CODEWITHSUNDEEP
sqlsql-serverfunction
user2055729
user2055729

Reputation: 199

Multiple SQL or Case statements in Scalar function

I am struggling with a SQL function for a report I am writing. The function polls an audit table for the original value of a particular field (SecondarySchoolCode). If it finds a value in the audit table for the current row it should return the value, if there is no value for the current row then the original parameter I supplied to the function should be returned instead. I have tried using a Case statement but it is not returning the parameter back if there is no match in the audit table. Any suggestions on how to accomplish this?

ALTER FUNCTION [dbo].[fn_AuditOriginalHSAttendingCode]
(
@StudentID VARCHAR(255),
@SecondarySchoolCode VARCHAR(255),
@ColumnName VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @Result AS VARCHAR(255);


RETURN (SELECT TOP (1) 
CASE WHEN @ColumnName <> 'SecondarySchoolCode' 
THEN  @SecondarySchoolCode 
ELSE dbo.GDSAuditDetail.ValueBeforeChange END
FROM     dbo.GDSAuditDetail 
INNER JOIN dbo.StudentSchool 
INNER JOIN dbo.Student ON dbo.StudentSchool.StudentId = dbo.Student.ID 
INNER JOIN dbo.SecondarySchool ON dbo.StudentSchool.SecondarySchoolId = dbo.SecondarySchool.ID 
INNER JOIN dbo.GDSAudit ON dbo.Student.ID = dbo.GDSAudit.EntityId ON dbo.GDSAuditDetail.GDSAuditId = dbo.GDSAudit.ID 
WHERE  (dbo.Student.ID = @studentID) and dbo.GDSAuditDetail.GDSColumn='SecondarySchoolCode'
ORDER BY dbo.GDSAudit.InsertedDate ASC)

The call to the function looks like this:

dbo.fn_AuditOriginalHSAttendingCode(dbo.Student.ID
                                  , dbo.SecondarySchool.SecondarySchoolCode
                                  , dbo.GDSAuditDetail.GDSColumn)

Upvotes: 0

Views: 133

Answers (3)

Ben Jaspers
Ben Jaspers

Reputation: 1546

I'm not sure what all of your relationships are, but based on your stated requirements and your current query, you are joining to some tables you don't need. The reason you don't get anything back when the audit doesn't exist is because there are no rows from which to select a TOP 1. This should always return at least one row for an existing Student.

SELECT TOP (1) ISNULL(dbo.GDSAuditDetail.ValueBeforeChange, @SecondarySchoolCode)
FROM dbo.Student 
LEFT JOIN dbo.GDSAudit 
    ON dbo.Student.ID = dbo.GDSAudit.EntityId 
LEFT JOIN dbo.GDSAuditDetail 
    ON dbo.GDSAuditDetail.GDSAuditId = dbo.GDSAudit.ID 
       AND dbo.GDSAuditDetail.GDSColumn='SecondarySchoolCode'
WHERE (dbo.Student.ID = @studentID) 
ORDER BY dbo.GDSAudit.InsertedDate ASC

Upvotes: 0

user2055729
user2055729

Reputation: 199

Biscuits provided the easiest to implement solution:

I mean, something like ISNULL(dbo.fn_AuditOriginalHSAttendingCode(...), 'SecondarySchoolCode') – Biscuits 59 mins ago

Upvotes: 1

M.Ali
M.Ali

Reputation: 69554

Your joins are all over the place a somewhat fixed version would look something like this... 

ALTER FUNCTION [dbo].[fn_AuditOriginalHSAttendingCode]
(
@StudentID VARCHAR(255),
@SecondarySchoolCode VARCHAR(255),
@ColumnName VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @Result AS VARCHAR(255);

    RETURN(SELECT TOP (1)  
                    CASE 
                         WHEN @ColumnName <> 'SecondarySchoolCode' 
                         THEN  @SecondarySchoolCode 
                         ELSE AD.ValueBeforeChange 
                    END
    FROM       dbo.GDSAuditDetail  AD
    INNER JOIN dbo.GDSAudit        A  ON AD.GDSAuditId        = A.ID 
    INNER JOIN dbo.Student         S  ON S.ID                 = A.EntityId 
    INNER JOIN dbo.StudentSchool   SS ON SS.StudentId         = S.ID 
    INNER JOIN dbo.SecondarySchool SE ON SS.SecondarySchoolId = SE.ID 
    WHERE  S.ID = @studentID
      AND  AD.GDSColumn = 'SecondarySchoolCode'
    ORDER BY AD.InsertedDate ASC)

END

Upvotes: 0

Related Questions