Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

multi-valued report parameter weirdness Expand / Collapse
Author
Message
Posted Wednesday, September 18, 2013 5:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:32 AM
Points: 923, Visits: 5,870
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
, 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));




Post #1495870
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse