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.

I'm not sure about MySQL, but with SQL Server and Oracle, you can pass multiple values to an `in` statement without needing the "split" function in the database. So the query would say `...where specific_value in (@parameter)` — StevenWhite, Dec 20 '16 at 22:07
What do you mean by doing all this in a dataset, using dynamic sql? — Ross Bush, Dec 20 '16 at 22:08
This is MSSQL, not MYSQL, that's my mistake in the tags above. The stored function takes a single string argument and does a bunch of string manipulations to it to create a list of possible answers that could match the specific_value. I can't use a stored function at all, so I need to move that ability to either as part of the dataset query or into the code of the report. — Lindylead, Dec 20 '16 at 22:28
If there aren't too many rows, bring everything into the report and do the filtering calculations there. If you need to filter the query for efficiency then you'll have to include it in the query. I'm not sure what you're looking for in terms of an "answer" other than "Yes, you can do it! Go for it." — StevenWhite, Dec 20 '16 at 22:43
You may use CROSS APPLY or CTE, and move the declarations to the beginning of the code. — Bartosz Siemasz, Dec 20 '16 at 23:24
Where does the value that needs splitting come from? If you use a multi select parameter in the SSRS report you can just use `where column in(@Paremeter)` and all values will be passed through as an `in` list. — iamdave, Dec 21 '16 at 11:21

score 0 · Answer 1 · edited Jan 15 '19 at 22:11

0

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)

edited Jan 15 '19 at 22:11

ETO

6,970
1
20
37

answered Jan 15 '19 at 21:06

Angel G. Valdivia H.

1

create dataset in SSRS with inline function

1 Answers1