create dataset in SSRS with inline function

Question

I have a SQL based report that I am migrating from Crystal Reports to SSRS. The old method uses a stored procedure that calls a stored function. The intent in the new method is to embed all of the report logic in the SSRS report. The reason behind this is that the stored procedure and function are not part of the target database. We don't want to have an extra database that just holds a few stored functions and procedures.

The basic structure now is:

select (a bunch of fields)
from (a bunch of joins)
where (conditions)
    and specific_value in (select value
                           from stored_function(inputs)
                          )

The stored function does some delimiter processing. I need to run this in SSRS only, without storing anything in the database. I can convert the stored procedure into a dataset in SSRS, but I can't figure out how to handle the stored_function.

-----edit ----- Here is the code in questions:

CREATE FUNCTION [dbo].[fn_split] (
    @SourceString sql_variant,
    @Delimiter nvarchar(10) = N',')
    RETURNS @Values TABLE(Position smallint IDENTITY, cValue varchar(2000) , ncValue nvarchar(2000))
AS
BEGIN
    DECLARE @NormalString varchar(2000), @NationalString nvarchar(2000),
        @NormalDelimiter varchar(10), @NationalDelimiter nvarchar(10),
        @IsNationalChar bit, @Position int,
        @NormalValue varchar(2000), @NationalValue nvarchar(2000)
    SET @Delimiter = COALESCE(@Delimiter, N',')
    SET @IsNationalChar = CASE
                WHEN SQL_VARIANT_PROPERTY(@SourceString,'BaseType') IN ('char','varchar')
                THEN 0
                WHEN SQL_VARIANT_PROPERTY(@SourceString,'BaseType') IN ('nchar','nvarchar')
                THEN 1
                  END
    IF @IsNationalChar IS NULL RETURN
    IF @IsNationalChar = 0
    BEGIN
        SET @NormalDelimiter = @Delimiter
        SET @NormalString = CAST(@SourceString AS varchar(2000))
        IF LEFT(@NormalString,LEN(@NormalDelimiter)) = @NormalDelimiter
            SET @NormalString = SUBSTRING(@NormalString,LEN(@NormalDelimiter) + 1, 2000)
        IF RIGHT(@NormalString,LEN(@NormalDelimiter)) <> @NormalDelimiter
            SET @NormalString = @NormalString + @NormalDelimiter
        WHILE(1 = 1)
        BEGIN
            SET @Position = CHARINDEX(@NormalDelimiter,@NormalString) - 1
            IF @Position <= 0 BREAK
            SET @NormalValue = LEFT(@NormalString,@Position)
            SET @NormalString = STUFF(@NormalString,1,@Position + LEN(@NormalDelimiter),'')
            INSERT INTO @Values(cValue) VALUES(@NormalValue)
        END
    END
    ELSE IF @IsNationalChar = 1
    BEGIN
        SET @NationalDelimiter = @Delimiter
        SET @NationalString = CAST(@SourceString AS varchar(2000))
        IF LEFT(@NationalString,LEN(@NationalDelimiter)) = @NationalDelimiter
            SET @NationalString = SUBSTRING(@NationalString,LEN(@NationalDelimiter) + 1,2000)
        IF RIGHT(@NationalString,LEN(@NationalDelimiter)) <> @NationalDelimiter
            SET @NationalString = @NationalString + @NationalDelimiter
        WHILE(1 = 1)
        BEGIN
            SET @Position = CHARINDEX(@NationalDelimiter,@NationalString) - 1
            IF @Position <= 0 BREAK
            SET @NationalValue = LEFT(@NationalString,@Position)
            SET @NationalString = STUFF(@NationalString,1,@Position + LEN(@NationalDelimiter),'')
            INSERT INTO @Values (ncValue) VALUES(@NationalValue)
        END
    END
    RETURN
END

That function gets called at the end of the query (fn_split).

select (a bunch of fields)
from (a bunch of joins)
where (conditions)
    and specific_value in (select value
                           from stored_function(input_value)
                          )

I'm trying to recreate this without using a Database Object (function or procedure). I'm okay with code in the SSRS report.

Answer 1

Just an idea. You could use 2 datasets :

select (a bunch of fields)
from (a bunch of joins)
where (conditions)
and specific_value in @Algo

and

select value 
from stored_function(inputs)