sql user defined function to retrieve same value

Question

this is my asp.net method

 public static DataSet ProgressReport(int FirmID)
    {
        if (HttpContext.Current.Session["CNYearID"] != null && HttpContext.Current.Session["CNYearID"].ToString() != "")
        {
            firmcomplianceyear = Convert.ToInt32(HttpContext.Current.Session["CNYearID"]);
        }
        else if (HttpContext.Current.Session["CurrentFCY"] != null && HttpContext.Current.Session["CurrentFCY"].ToString() != "")
        {
            firmcomplianceyear = Convert.ToInt32(HttpContext.Current.Session["CurrentFCY"]);
        }



        if (HttpContext.Current.Session["NewCompYID"] != null && HttpContext.Current.Session["NewCompYID"].ToString() != "")
        {
            compliance_year = Convert.ToInt32(HttpContext.Current.Session["NewCompYID"]);
        }
        else if (HttpContext.Current.Session["CompYearID"] != null && HttpContext.Current.Session["CompYearID"].ToString() != "")
        {
            compliance_year = Convert.ToInt32(HttpContext.Current.Session["CompYearID"]);

        }
        String str = String.Format(@"select 
        F.firmname, 
        fr.firmusername as FirmUserName,
        S.sectionnumber, 
        d.deptname as Department, 
        CONVERT(VARCHAR(10),R.InsertDate, 101) AS Date,
        st.statusname,
        Convert(decimal(10,2),(st.StatusPercentComplete/cast (100 as float))) as Percentage,                
        case when R.PolicyConfirmation = '0' then 'NO'
        when R.PolicyConfirmation = '1' then 'YES'
        end as PolicyConfirmation,
        case when R.GeneralConfirmation='0' then 'NO'
        when R.GeneralConfirmation='1' then 'YES' end  as GeneralConfirmation,
        comments = dbo.udfGetCommentListRajendra(r.firmcompliancerequirementid),
        documents = dbo.udfGetDocumentList( F.firmid,S.sectionid),

---------here im using the below udf function where i m stucked----

        Location=dbo.udfGetLocationList_1( F.firmid,S.sectionid,fr.firmuserid)
        from requirementcertification r
        join firm  f on F.firmid=R.firmid
        join Section s on S.sectionid= R.sectionid
        join FirmComplianceRequirement fcr on Fcr.FirmComplianceRequirementID = R.FirmComplianceRequirementID
        Join FirmComplianceYear FCY on FCY.FirmComplianceYearID=FCR.firmcomplianceyearid
        join Department d on d.deptid=fcr.assignedto
        join [Status] St on st.statusID = R.statusid
        join firmuser fr on fr.firmuserid = r.userid

         where r.userid in(select firmuserid from firmfirmusermapping where firmid='{0}') AND (D.FirmID=0 OR D.FirmID= '{0}') AND D.IsActive=1 AND D.IsDelete=0 And  FCY.ComplianceYearID='{1}' 
        order by R.insertdate desc", FirmID, compliance_year);
        DataSet ds = DAL.SelectRecords(str.ToString());
        return ds;
    }

.

USE [Mydata]
GO


Create  function [dbo].[udfGetCommentList]
(
@firmcompliancerequirementid int
)
RETURNS varchar(max)
as
BEGIN

DECLARE @listStr VARCHAR(MAX)
--SELECT @listStr = COALESCE(@listStr+',' ,'') + requirementcomment
--FROM requirementcomment  
--where firmcompliancerequirementid = @firmcompliancerequirementid 

Select distinct @listStr= RequirementComment from requirementcomment 
where firmcompliancerequirementid=@firmcompliancerequirementid 
order by RequirementCommentID desc
return @listStr
END


GO

the output of this :

Name        Section#    Department   Date        Status                 %   Comments
vishalVVVV  23.503     Compliance   04/28/2015  Pending Documentation   25% Rejection comment for the second time
vishalVVVV  23.503  Finance 04/28/2015  Pending Documentation   25%           one more comment on 2nd requirement

i want same value of column comments in the second row also... but i m getting previous value

i need

Comment

---

Rejection comment for the second time

Rejection comment for the second time

i m getting :(

---

Rejection comment for the second time

one more comment on 2nd requirement

Answer 1

You have this line in your SQL:

comments = dbo.udfGetCommentListRajendra(r.firmcompliancerequirementid),

It stands to reason that if r.firmcompliancerequirementid from the first record is different from r.firmcompliancerequirementid from the second record, you're going to get a different return value.

If they're the same, and you're getting inconsistent results from dbo.udfGetCommentListRajendra, it may be that you have multiple values for RequirementCommentID in table requirementcomment and you're not getting the same record popping to the top every time.

But if they're different, but you want the result to be as if they were the same ... that's going to be complex to pull off in a single query.

I would therefore ask for what purpose you want the data in this format. Is this to appear on a web page, a report, or something similar, intended for user viewing?

If so, remember that you can always alter what the output looks like in the screen or report. It might make sense to return the data as you are doing now, but modify your report so that the first comment line is repeated as you head down the page.