Looooong parameter list
-
Hi.
I like RS -- it sure is pretty.
I've been making reports with my dataset queries having lots of terms in an IN() list. I mean LOTS of terms -- sometimes 1500 things in the IN().
My question -- is there a better way to do this? I'd like a more dynamic way where I can pass all the 1500 searched-for terms in a parameter as they change from day-to-day. Will RS take a 1500-term (that'd be about 10,000 characters in the parameter)?
Thanks!
-
Where do you take this list of 1500 ids?
Why not use an inner join?
-
Well, sorry to get bogged down in specifics, but I run a list of order numbers given to me by a client against a SQL table with order #/ship dates return ship dates. I've thought of making a derived table or something but still, I'd need to pass all those numbers the client has given me to the report dataset query.
I've also thought of somehow using an Excel spreadsheet as a data source but can't quite get it going. Or at all, really. Excel -> XML -> MSSRS seems like a hassle, too.
-
I use the following to put the incoming parameter list into a table I can join against:
set
@nextcomma = charindex(',',@paramlist,1)
create
table #param_list_table (id int)
while
@nextcomma >0
begin
insert into #param_list_table (id)
select left(@paramlist,charindex(',',@paramlist,1)-1)
set @paramlist = substring(@paramlist,@nextcomma+1,99)
set @nextcomma = charindex(',',@paramlist,1)
end
insert
into #param_list_table (id) select @paramlist
-
We ran into a similar issue and used a table valued UDF to resolve it. You can join to the UDF result set.
CREATE
FUNCTION dbo.udf_splitString
(
@prmString VARCHAR(8000), -- VARCHAR(MAX) in SQL2005 with extreme caution!
@prmDelim sql_variant = NULL
)
RETURNS @retArray TABLE (idx INT IDENTITY, element VARCHAR(8000))
AS
/*
Name: udf_splitString
Author: Arturo T. de Lamerens (Based on a script by Joe Celko)
Version: MS SQL Server 2000
Function: Parse a 'delimited' character string.
Strategy: Set-based delimiter detection and element extraction utilizing
sql_variant to preserve trailing blanks in delimiter.
Usage: dbo.udf_splitString(characterString, delimiter)
Returns: Table variable of ordinals and elements.
Revisions: 2003.06.27 ATL - Initial Version.
*/
BEGIN
DECLARE
@idx INT,
@parseLen INT,
@delimLen TINYINT,
@delimPos TINYINT
DECLARE @sequence TABLE (pos int)
SET @prmDelim = ISNULL(@prmDelim, 'EMPTY') -- change NULL to a 'detectable' value
-- CAST'ing THEN expression is necessary to prevent padding (bug?)
SET @prmDelim =
CASE UPPER(CAST(@prmDelim AS VARCHAR(20)))
WHEN 'SPACE' THEN CAST(' ' AS sql_variant)
WHEN 'COMMA' THEN CAST(',' AS sql_variant)
WHEN 'TAB' THEN CAST(CHAR(9) AS sql_variant)
WHEN 'TILDE' THEN CAST('~' AS sql_variant)
WHEN 'EMPTY' THEN CAST('' AS sql_variant)
ELSE @prmDelim
END
SET @idx = 1
SET @prmString = CAST(@prmDelim AS VARCHAR(20)) + LTRIM(RTRIM(@prmString)) + CAST(@prmDelim AS VARCHAR(20))
SET @delimLen = DATALENGTH(@prmDelim)
SET @parseLen = LEN(@prmString) + 1
WHILE @idx <= @parseLen
BEGIN
INSERT INTO @sequence (pos) VALUES (@idx)
SET @idx = @idx + 1
END
--ENDWHILE @idx <= @parseLen
IF @delimLen <> 0
BEGIN
-- Parse delimited string
INSERT INTO @retArray (element)
SELECT SUBSTRING(@prmString, s1.pos + @delimLen, s2.pos - s1.pos - @delimLen)
FROM
@sequence s1,
@sequence s2
WHERE
SUBSTRING(@prmString, s1.pos, @delimLen) = CAST(@prmDelim AS VARCHAR(20)) AND
SUBSTRING(@prmString, s2.pos, @delimLen) = CAST(@prmDelim AS VARCHAR(20)) AND
s1.pos < s2.pos AND
s2.pos = (
SELECT MIN(s3.pos)
FROM @sequence s3
WHERE SUBSTRING(@prmString, s3.pos, @delimLen) = CAST(@prmDelim AS VARCHAR(20)) AND
s1.pos < s3.pos
)
END
ELSE
BEGIN
-- Parse undelimited string (return each character as an element)
SET @idx = 1
WHILE @idx <= @parseLen
BEGIN
INSERT @retArray (element)
VALUES (SUBSTRING(@prmString, @idx, 1))
SET @idx = @idx + 1
END
-- ENDWHILE @idx < @parseLen
END
--ENDIF @delimLen <> 0
RETURN
END
go
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply