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

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

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

mssql wont find NULL values in datetime field?

Hi

I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:

SELECT * FROM ADMIN WHERE date_edited = NULL

I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:

thanksignore me, found the answer

SELECT * FROM ADMIN WHERE date_edited is NULL|||Consider yourself ignored. ;)|||I'm ignoring him right now. Intensely.|||please dun treat newbies like that, we all started off as a newbie right?

maybe we shall have a newbie section for them to post elementary questions...

but seriously, this NULL question does look dumb......TS shall try harder before posting it......|||personally I still hate nulls and three part logic. It causes problems for so many programmers. I slap as many not null constraints in my software as I can. Of course I spend so much time fighting fires these days, I only get to do reactive development or redevelopment.|||ROTFL ... look at Sean's title in the above post ... i almost missed that.

Another contradictory Seanism :p|||Declare @.ZenosParadox varchar(50)
Set @.ZenosParadox = 'This value is null'|||I learned in math class that zeno's paradox was that you could never reach any destination because you first had to travel 1/2 the distance, then 1/2 again, then again, so you'd never reach it.

poor zeno didn't know an infinite series can sum to a finite number.|||http://en.wikipedia.org/wiki/Liar_paradox ?

:)|||NULL value is good, I dun need to be bothered about putting constraint while showing them on a report hehe...