Hi,

I have a scalar function (example below):

ALTER FUNCTION [dbo].[GetParentPath] 
(
 -- Add the parameters for the function here
 @dataid int, @PrevPath varchar(2000)
)
RETURNS varchar(4000)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result varchar(4000)
 DECLARE @prntName Varchar(250)
 DECLARE @prntID integer

 -- Add the T-SQL statements to compute the return value here
 -- SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
 select @prntName = d1.name, @prntID = d1.dataid  from dtree d1, dtree d2 where d2.dataid = @dataid and d1.dataid = d2.parentid
-- set @result = 
 if @prntName is not null and @prntName <> ''  
 begin
--  set @Result = '@prntName is not null and @prntName <> ""'
  if @PrevPath <> '' begin
   set @Result = dbo.getParentPath(@prntID, @prntName + ':' + @PrevPath)
  end else begin
   set @Result = dbo.getParentPath(@prntID, @prntName)
  end
 end else begin
--  set @Result = '@prntName is null or @prntName <> ""'
  set @Result = @PrevPath
 end
 -- Return the result of the function
 RETURN @Result
END

I have a TypedList search I am running. I want to use the Function I have in SQL Server and return the value in my resultset. How do I go about doing this? The code for the rest is here:

public DataTable getDuplicateFoldersPrefix(string Prefix, string FileNumber)
        {
            DataTable dtResults = new DataTable();
            
            IPredicateExpression filter = new PredicateExpression();
            IPredicateExpression filter1 = new PredicateExpression();
            IPredicateExpression filter2 = new PredicateExpression();
            IPredicateExpression filter3 = new PredicateExpression();
            IPredicateExpression filter4 = new PredicateExpression();

            DuplicateFoldersReportTypedList tlDuplicate = new DuplicateFoldersReportTypedList();

            filter1.Add(new FieldCompareSetPredicate(DtreeFields.Name, VDuplicateFoldersFields.FolderName, SetOperator.In, null));

            filter.Add(filter1);

            filter2.Add(new FieldCompareValuePredicate(DtreeFields.SubType, ComparisonOperator.Equal, cIntFolderSubType));
            filter.AddWithAnd(filter2);

            char[] splitterPrefix = { ',' };
            string[] prefixes = Prefix.Split(splitterPrefix);
            for (int i = 0; i < prefixes.Length; i++)
            {
                if (i == 0)
                {
                    filter3.Add(new FieldCompareValuePredicate(PrefixRmobjectsFields.Prefix, ComparisonOperator.Equal, prefixes[i]));
                }
                else
                {
                    filter3.AddWithOr(new FieldCompareValuePredicate(PrefixRmobjectsFields.Prefix, ComparisonOperator.Equal, prefixes[i]));
                }
            }
            if (String.IsNullOrEmpty(Prefix) == false) filter.AddWithAnd(filter3);

            if (String.IsNullOrEmpty(FileNumber) == false)
            {
                if (FileNumber.Contains("%") == true) filter4.Add(new FieldLikePredicate(RimsNodeClassificationFields.RimsRsi, FileNumber));
                else filter4.Add(new FieldCompareValuePredicate(RimsNodeClassificationFields.RimsRsi,ComparisonOperator.Equal, FileNumber));
                filter.AddWithAnd(filter4);
            }
            
            tlDuplicate.Fill(0, null, true, filter);

            dtResults = (DataTable)tlDuplicate;
            
        
            return dtResults;
        }

Here is an example. Just replace the _ScalarQueryExpression _with a DBFunctionCall..