Multi Select Parameter from Function - Select All?

I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.

Query:

Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

Function:

CREATE FUNCTION charlist_to_table

(@.list ntext,

@.delimiter nchar(1) = N',')

RETURNS @.tbl Table (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),

nstr nvarchar(2000)) AS

BEGIN

DECLARE @.pos int,

@.textpos int,

@.chunklen smallint,

@.tmpstr nvarchar(4000),

@.leftover nvarchar(4000),

@.tmpval nvarchar(4000)

SET @.textpos = 1

SET @.leftover = ''

WHILE @.textpos <= datalength(@.list) / 2

BEGIN

SET @.chunklen = 4000 - datalength(@.leftover) / 2

SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)

SET @.textpos = @.textpos + @.chunklen

SET @.pos = charindex(@.delimiter, @.tmpstr)

WHILE @.pos > 0

BEGIN

SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))

INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)

SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))

SET @.pos = charindex(@.delimiter, @.tmpstr)

END

SET @.leftover = @.tmpstr

END

INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),

ltrim(rtrim(@.leftover)))

RETURN

END

GO

Anyone have any ideas?

Thanks,

Deb

I think by using the UNION function from sql we can add "All" parameter to the query.

Here I have taken only one field for the example but you can change the whole table.

Ex:

Select 'All' AS NAME

UNION

Select name

From Golf inner join charlist_to_table(@.State,Default)f on State = f.str

By using this query this should give you 'All' parameter.

Hope this helps...

--Deepak

|||

Thanks for your reply, however that doesn't work, as it just inserts "All" into the name. I know how to add the all parameter using Union with a drop down select list, but I am unsure how to do it with this function. Any other suggestions?

|||

Bump

|||

I finally figured out a simple solution - In the Report Parameters window under Default Values, Non-queried enter whatever the criteria is for "All'.

Deb

sql