I am trying to procduce a report using an " IN (x,y,z) "clause except that
x,y,z could contain 1 or 10 values, is it possible to change the base query
at run time or use a single parameter array to convert the values?
Any help and guuidence appriciated.Richard,
Maybe a filter might be an option and wite some VB.NET code to use instr
command to handle the variable values ?
- peteZ
"Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
>I am trying to procduce a report using an " IN (x,y,z) "clause except that
>x,y,z could contain 1 or 10 values, is it possible to change the base query
>at run time or use a single parameter array to convert the values?
> Any help and guuidence appriciated.
>
>|||Your report query can be expression-based. You can pass the whole IN clouse
(x, y, x) as a report parameter but be careful about SQL injection attacks.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I am trying to procduce a report using an " IN (x,y,z) "clause except that
> x,y,z could contain 1 or 10 values, is it possible to change the base
query
> at run time or use a single parameter array to convert the values?
> Any help and guuidence appriciated.
>
>|||No i've tried to look at what you are saying but cant see how you would
implement it
Do you put a parameter in the SQL code, or is it a filer? a simple example
would be really helpful.
Thanks
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:eOtAzSXtEHA.3564@.tk2msftngp13.phx.gbl...
> Your report query can be expression-based. You can pass the whole IN
> clouse
> (x, y, x) as a report parameter but be careful about SQL injection
> attacks.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
> news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
>> I am trying to procduce a report using an " IN (x,y,z) "clause except
>> that
>> x,y,z could contain 1 or 10 values, is it possible to change the base
> query
>> at run time or use a single parameter array to convert the values?
>> Any help and guuidence appriciated.
>>
>|||Hi,
What attached report definition are you referring to? I am also having to
pass multivalues to a parameter and I would like to use in(x,y).
Thanks
"Teo Lachev [MVP]" wrote:
> Namshub,
> Please look at the attached report definition which demostrates an
> expression-based SQL statement that takes comma-separated list of employee
> identifiers passed as a report parameter. It produces a report that shows
> the purchase orders associated with a single or mutiple employees.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
> news:uSEkVxdtEHA.1368@.TK2MSFTNGP15.phx.gbl...
> > No i've tried to look at what you are saying but cant see how you would
> > implement it
> >
> > Do you put a parameter in the SQL code, or is it a filer? a simple
> example
> > would be really helpful.
> >
> > Thanks
> >
> > "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> > news:eOtAzSXtEHA.3564@.tk2msftngp13.phx.gbl...
> > > Your report query can be expression-based. You can pass the whole IN
> > > clouse
> > > (x, y, x) as a report parameter but be careful about SQL injection
> > > attacks.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > Publisher website: http://www.manning.com/lachev
> > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > Home page and blog: http://www.prologika.com/
> > > ---
> > >
> > > "Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in
> message
> > > news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
> > >> I am trying to procduce a report using an " IN (x,y,z) "clause except
> > >> that
> > >> x,y,z could contain 1 or 10 values, is it possible to change the base
> > > query
> > >> at run time or use a single parameter array to convert the values?
> > >>
> > >> Any help and guuidence appriciated.
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
>
>|||Hi,
Hi,
What you could do is create a function on the SQL server that will
convert your comma separated list to a table.
Like this:
CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
(
@.CommaSeparatedList VARCHAR(8000)
)
RETURNS @.ParsedTableValue TABLE (TableValue VARCHAR(1000))
AS
BEGIN
DECLARE @.TableValue VARCHAR(1000)
DECLARE @.Pos INT
SET @.CommaSeparatedList = LTRIM(RTRIM(@.CommaSeparatedList))+ ','
SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
IF REPLACE(@.CommaSeparatedList, ',', '') <> ''
BEGIN
WHILE @.Pos > 0
BEGIN
SET @.TableValue = LTRIM(RTRIM(LEFT(@.CommaSeparatedList, @.Pos - 1)))
IF @.TableValue <> ''
BEGIN
INSERT INTO @.ParsedTableValue (TableValue)
VALUES (RTRIM(@.TableValue))
END
SET @.CommaSeparatedList = RIGHT(@.CommaSeparatedList,
LEN(@.CommaSeparatedList) - @.Pos)
SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
END
END
RETURN
END
Then use this function in your query or stored procedure like this:
SELECT a.country, ..., ..., ...
FROM table1 a
JOIN dbo.UDF_CharCommaSeparatedListToTable(@.CountryList) b ON
RTRIM(b.TableValue) = a.country
HTH,
Eric|||Hi,
Thanks alot for the help.
Now I need to modify the function. I tried but was unsuccessful.
What I need is to be able to pass null values so if the user doesn't select
a value all the employee names will bel returned.
Also I need multiple where statements. I tried multiple inner joins but
obviously it returns the error:
The correlation name 'b' is specified multiple times in a FROM clause
How can I fix the problems?
Thanks
"Aiwa" wrote:
> Hi,
> Hi,
>
> What you could do is create a function on the SQL server that will
> convert your comma separated list to a table.
>
> Like this:
> CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
> (
> @.CommaSeparatedList VARCHAR(8000)
> )
> RETURNS @.ParsedTableValue TABLE (TableValue VARCHAR(1000))
> AS
> BEGIN
> DECLARE @.TableValue VARCHAR(1000)
> DECLARE @.Pos INT
>
> SET @.CommaSeparatedList = LTRIM(RTRIM(@.CommaSeparatedList))+ ','
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> IF REPLACE(@.CommaSeparatedList, ',', '') <> ''
> BEGIN
> WHILE @.Pos > 0
> BEGIN
>
> SET @.TableValue = LTRIM(RTRIM(LEFT(@.CommaSeparatedList, @.Pos - 1)))
> IF @.TableValue <> ''
> BEGIN
> INSERT INTO @.ParsedTableValue (TableValue)
> VALUES (RTRIM(@.TableValue))
> END
> SET @.CommaSeparatedList = RIGHT(@.CommaSeparatedList,
> LEN(@.CommaSeparatedList) - @.Pos)
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> END
> END
> RETURN
> END
>
> Then use this function in your query or stored procedure like this:
>
> SELECT a.country, ..., ..., ...
> FROM table1 a
> JOIN dbo.UDF_CharCommaSeparatedListToTable(@.CountryList) b ON
> RTRIM(b.TableValue) = a.country
>
> HTH,
> Eric
>|||I solved my problem for null values and for multiple statements in the where
clause as follows:
WHERE (@.param is null or userid in (Select tablevalue from
dbo.UDF_CharCommaSeparatedListToTable (@.param)))
"Aiwa" wrote:
> Hi,
> Hi,
>
> What you could do is create a function on the SQL server that will
> convert your comma separated list to a table.
>
> Like this:
> CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
> (
> @.CommaSeparatedList VARCHAR(8000)
> )
> RETURNS @.ParsedTableValue TABLE (TableValue VARCHAR(1000))
> AS
> BEGIN
> DECLARE @.TableValue VARCHAR(1000)
> DECLARE @.Pos INT
>
> SET @.CommaSeparatedList = LTRIM(RTRIM(@.CommaSeparatedList))+ ','
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> IF REPLACE(@.CommaSeparatedList, ',', '') <> ''
> BEGIN
> WHILE @.Pos > 0
> BEGIN
>
> SET @.TableValue = LTRIM(RTRIM(LEFT(@.CommaSeparatedList, @.Pos - 1)))
> IF @.TableValue <> ''
> BEGIN
> INSERT INTO @.ParsedTableValue (TableValue)
> VALUES (RTRIM(@.TableValue))
> END
> SET @.CommaSeparatedList = RIGHT(@.CommaSeparatedList,
> LEN(@.CommaSeparatedList) - @.Pos)
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> END
> END
> RETURN
> END
>
> Then use this function in your query or stored procedure like this:
>
> SELECT a.country, ..., ..., ...
> FROM table1 a
> JOIN dbo.UDF_CharCommaSeparatedListToTable(@.CountryList) b ON
> RTRIM(b.TableValue) = a.country
>
> HTH,
> Eric
>|||Hi,
To fix the null values issues maybe you could try someting like this:
*** It is not going to work if the parameter @.EmployeeList is null...
maybe en empty string could represent all possible values...
SELECT a.EmployeeName, a.col2,..., ...
FROM table1 a
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.EmployeeList) b
ON 1 =1
WHERE a.EmployeeName = CASE WHEN RTRIM(@.EmployeeList) = '' THEN
a.EmployeeName
ELSE RTRIM(b.TableValue)
END
For the correlation name issue, you can change the alias for every join
you make to the function:
SELECT a.EmployeeName, a.col2,..., ...
FROM table1 a
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList1)
b ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList2)
c ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList3)
d ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList4)
e ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList5)
f ON ...
HTH,
Eric|||Hi,
Your way of managing null values is better than mine, I'll be using
that in the future!!
Eric|||Ouups,
After giving it a little more thought, I will stick to my way of
managing null (empty) values since your way of managing nulls would
require the function to be called for every records of your resultset
and my way will be calling the function only once...
Regards,
Eric
TimMulholland wrote:
