September 15, 2014 at 4:30 pm
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?
September 15, 2014 at 4:49 pm
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.
September 15, 2014 at 10:59 pm
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