multi-valued report parameter weirdness

Question

multi-valued report parameter weirdness

Viewing 6 posts - 16 through 20 (of 20 total)

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

pietlinden SSC Guru Points: 63428 More actions · Answer 1

I'm trying (in vain) to use the DelimitedSplit8K function to pass a delimited list to my stored procedure to filter my underlying resultset

Here's my SQL

CREATE PROCEDURE BuildDatesForReport

( @StartDate DATETIME

, @EndDate DATETIME

, @BuildEventTypes VARCHAR(80)

)

AS

BEGIN

SELECT BuildSite.SiteName, House.LotNumber, House.HouseID, House.Homeowner, BuildDates.bhHouseID, BuildDates.HouseBuildDate, BuildDates.BuildEventType

FROM House INNER JOIN

BuildSite ON House.hBuildSiteID = BuildSite.SiteID INNER JOIN

BuildDates ON House.HouseID = BuildDates.bhHouseID

WHEREBuildDates.HouseBuildDate BETWEEN @StartDate AND @EndDate

ANDBuildDates.BuildEventType IN (dbo.DelimitedSplit8K(@BuildEventTypes,','));

END

If I leave out the @BuildEventType part, everything works fine.

I was trying to follow this article http://www.sqlservercentral.com/articles/T-SQL/73838/ but there's something there I just don't get. DelimitedSplit8K returns a table, so shouldn't I just join to the table?

Sorry to be so thick. There's just something here that I just goes right over my head!

Thanks!

Pieter

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

Time to take a step back. You have:

BuildDates.BuildEventType IN (dbo.DelimitedSplit8K(@BuildEventTypes,','));

This is the same as

BuildDates.BuildEventType = dbo.DelimitedSplit8K(@BuildEventTypes,',');

But since DelimtedSplit8K is a table-valued function, this is not going to work out. You need to query the table-valued function:

BuildDates.BuildEventType IN (SELECT col FROM dbo.DelimitedSplit8K(@BuildEventTypes,','));

You need to replace "col" with the actual column name used by the function (which I don't know by heart).

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

pietlinden SSC Guru Points: 63428 More actions · Answer 3

Oh... must have missed that very subtle hint: TABLE-valued function. (So you query it, because it's a ... well, a TABLE!

Thanks!

pietlinden SSC Guru Points: 63428 More actions · Answer 4

In case any other Reporting Services rookie comes across this, here's a really good article walking you through a solution and explaining what works and what doesn't and why.

http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/

(apologies to everyone reading my questions - I come from an Access background, so I'm used to the constructs available there. Hence my mashing up T-SQL and weird things like collections.

Case closed, I hope!

pietlinden SSC Guru Points: 63428 More actions · Answer 5

Oh Super cool!!! Works a CHAMP!!! Definitely a good trick to learn! Thanks!

pietlinden SSC Guru Points: 63428 More actions · Answer 6

Here's the whole function and stored procedure declaration etc, just in case someone else has this fun.

ALTER FUNCTION [dbo].[fn_SplitStringList]

(

@StringList VARCHAR(MAX)

)

RETURNS @TableList TABLE( StringLiteral VARCHAR(128))

AS

BEGIN

DECLARE @StartPointer INT, @EndPointer INT

SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList)

WHILE (@StartPointer < LEN(@StringList) + 1)

BEGIN

IF @EndPointer = 0

SET @EndPointer = LEN(@StringList) + 1

INSERT INTO @TableList (StringLiteral)

VALUES(LTRIM(RTRIM(SUBSTRING(@StringList, @StartPointer,

@EndPointer - @StartPointer))))

SET @StartPointer = @EndPointer + 1

SET @EndPointer = CHARINDEX(',', @StringList, @StartPointer)

END -- WHILE

RETURN

END

CREATE PROC GetBuildEvents

(@BuildEventType VARCHAR(100))

AS

SELECT BuildSite.SiteName

, House.LotNumber

, House.HouseID

, House.Homeowner

, BuildDates.bhHouseID

, BuildDates.HouseBuildDate

, BuildDates.BuildEventType

FROM House INNER JOIN

BuildSite ON House.hBuildSiteID = BuildSite.SiteID INNER JOIN

BuildDates ON House.HouseID = BuildDates.bhHouseID

WHERE BuildDates.BuildEventType IN (select * from [dbo].[fn_SplitStringList](@BuildEventType));SELECT BuildSite.SiteName