Parameters using like

Hi,
I have a parameter named County. County, can have multiple values in it -
ex: 01, 02, 03.
I need to somehow use a like statement with this parameter so that it will
find all the counties in this field. I tried like%@.County% but this doesn't
work.
Anybody have any suggestions'
ThanksJill,
I suspect that what you'd like to do is something like this:-
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (1,2,3,4,5)
and then your thinking probably goes that you'd want to replace (1,2,3,4,5)
with a parameter such as:
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (@.CountyParameter)
You could make this work like I'm about to show you. BUT DONT!
EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (' + @.CountyParameter+ ')')
The reason not to do this is that this is very insecure from attacks from
SQL INJECTION. Just consider what would happen if someone passed
4);INSERT INTO CountiesTable(CountyName,
CountyID)VALUES('DisneyFantasyCounty',667)--
as the value for @.CountyParameter (or something far worse).
So having told you what not to do - the correct thing to do is create a
function on the Server such as this one taken directly from the
'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages 534 -
537)
CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
RETURNS @.paramtable
TABLE ( Id int )
AS BEGIN
DECLARE @.len int,
@.index int,
@.nextindex int
SET @.len = DATALENGTH(@.delimString)
SET @.index = 0
SET @.nextindex = 0
WHILE (@.len > @.index )
BEGIN
SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
INSERT @.paramtable
SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
SET @.index = @.nextindex + 1
END
RETURN
END
What this function does is that you pass it a parameter of a delimited
string such as your counties '1;2;6;' and returns a table. This table can
then be joined into your query. This approach is safer from SQL Injection
attacks. And so your Query for the DataSet becomes:
SELECT CountyID, CountyName FROM CountiesTable
INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
ON CountiesTable.CountyID= ParamParserFn.Id
I hope this is able to help you. More details as I mentioned are available
in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
Services. (see http://www.sqlreportingservices.net ). I would heartily
recommend our book to you as a valuable resource that comes with 2.5 GB of
DVD content and video demonstrations, and we especially concentrate on
security matters throughout.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Jill" <Jill@.discussions.microsoft.com> wrote in message
news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
> Hi,
> I have a parameter named County. County, can have multiple values in it -
> ex: 01, 02, 03.
> I need to somehow use a like statement with this parameter so that it will
> find all the counties in this field. I tried like%@.County% but this
> doesn't
> work.
> Anybody have any suggestions'
> Thanks
>|||We will also natively support multiple values in queries in the next release
of Reporting Services.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Blackburn (www.sqlreportingservices.net)"
<http://www.sqlreportingservices.net> wrote in message
news:u6Py%239gxEHA.2040@.tk2msftngp13.phx.gbl...
> Jill,
> I suspect that what you'd like to do is something like this:-
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (1,2,3,4,5)
> and then your thinking probably goes that you'd want to replace
> (1,2,3,4,5) with a parameter such as:
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (@.CountyParameter)
> You could make this work like I'm about to show you. BUT DONT!
>
> EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (' + @.CountyParameter+ ')')
> The reason not to do this is that this is very insecure from attacks from
> SQL INJECTION. Just consider what would happen if someone passed
> 4);INSERT INTO CountiesTable(CountyName,
> CountyID)VALUES('DisneyFantasyCounty',667)--
> as the value for @.CountyParameter (or something far worse).
>
>
> So having told you what not to do - the correct thing to do is create a
> function on the Server such as this one taken directly from the
> 'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages
> 534 - 537)
>
> CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
> RETURNS @.paramtable
> TABLE ( Id int )
> AS BEGIN
> DECLARE @.len int,
> @.index int,
> @.nextindex int
> SET @.len = DATALENGTH(@.delimString)
> SET @.index = 0
> SET @.nextindex = 0
> WHILE (@.len > @.index )
> BEGIN
> SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
> if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
> INSERT @.paramtable
> SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
> SET @.index = @.nextindex + 1
> END
> RETURN
> END
> What this function does is that you pass it a parameter of a delimited
> string such as your counties '1;2;6;' and returns a table. This table can
> then be joined into your query. This approach is safer from SQL Injection
> attacks. And so your Query for the DataSet becomes:
> SELECT CountyID, CountyName FROM CountiesTable
> INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
> ON CountiesTable.CountyID= ParamParserFn.Id
>
> I hope this is able to help you. More details as I mentioned are available
> in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
> Services. (see http://www.sqlreportingservices.net ). I would heartily
> recommend our book to you as a valuable resource that comes with 2.5 GB of
> DVD content and video demonstrations, and we especially concentrate on
> security matters throughout.
> Peter Blackburn
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
>
>
>
>
> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
>> Hi,
>> I have a parameter named County. County, can have multiple values in
>> it -
>> ex: 01, 02, 03.
>> I need to somehow use a like statement with this parameter so that it
>> will
>> find all the counties in this field. I tried like%@.County% but this
>> doesn't
>> work.
>> Anybody have any suggestions'
>> Thanks
>