t-sql to call a split parameter function

  • In t-sql 2012, the followinng sql works fine when I declare @reportID.

    IF @reportID <> 0

    BEGIN

    SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue

    UNION

    SELECT 'Students report 2', 2

    UNION

    SELECT 'Students report 3', 3

    UNION

    SELECT 'Students report 4', 4

    UNION

    SELECT 'Students report 5', 5

    ORDER BY selectRptName

    END

    However when I use the sql above in an ssrs 2012 report, the query does not

    work since the @reportID parameter can have 0, 1, or up to 200 values.

    Thus I am thinking of calling the following following function to split out the parameter values:

    FUNCTION [dbo].[fn_splitString]

    (

    @listString VARCHAR(MAX)

    )

    RETURNS TABLE WITH SCHEMABINDING

    AS

    RETURN

    (

    SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id

    FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l

    CROSS JOIN dbo.sequenceNumbers sn

    WHERE sn.Num < LEN(l.listString)

    AND SUBSTRING(l.listString, sn.Num, 1) = ','

    )

    GO

    Can you show me sql code on how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString

    function?

  • Let me see if I understand correctly. You're receiving a comma-delimited parameter and if you have any value different than 0, you'll return a simple result set with no relation to your parameters? Or are you changing the code and I got completely lost with the result of the changes?

    Have you tried the DelimitedSplit8K? It's the fastest T-SQL splitter and you can find it here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

    If you can explain again your requirements, I might be able to help you.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Quick thought, this can be simplified with the charindex function, no need to actually split the input. The focus must be on what has to be done, not how;-)

    😎

    DECLARE @reportID VARCHAR(10) = '0,1,'

    IF CHARINDEX(',0,',',' + @reportID + ',') = 0

    BEGIN

    SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue

    UNION

    SELECT 'Students report 2', 2

    UNION

    SELECT 'Students report 3', 3

    UNION

    SELECT 'Students report 4', 4

    UNION

    SELECT 'Students report 5', 5

    ORDER BY selectRptName

    END

    ELSE

    SELECT 'Report 0' AS selectRptName,0 AS rptNumValue

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply