September 23, 2008 at 8:59 am
Hi,
I've a report with a client lookup as a parameter option, but I need to improve runtime performance. I have 5000+ clients and would like a string search to narrow the results, I'd also like to allow multiple string searches.
I need to allow users to input multiple names into an input box. The value of the multi-value input parameter will then be passed to another multi-value parameter to perform a lookup on the database table.
I've tried this as a non-multi-value parameters and it works, but how do i make it work for multi-value parameters?
My query is:
SELECT DISTINCT NAMES.NAME_ID,
NAMES.NAME_ID + ' ' + NAMES.NAME1 as id_and_name,
NAMES.NAME1
FROMNAMES,
NAMES
WHERENAMES.NAME1 LIKE '%' + (@namelike) + '%'
September 23, 2008 at 9:14 am
I would suggest reading up on preventing "SQL injection" as your first step:
http://www.sqlservercentral.com/articles/Security/updatedsqlinjection/2065/
If you must pass multiple values in one variable, you'll need to parse out the values and specify a delimiter, such as a comma. I would suggest using one value per input parameter.
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
September 23, 2008 at 9:26 am
You need to use IN as Like does comparison to the entire parameter. So if your parameter contains:
'Johnson, Jones, Smith'
the LIKE is Where Name Like '%Johnson, Jones, Smith%' and I don't think anyone will have that type of name. Whereas the IN clause would be Where name IN ('Johnson', 'Jones', 'Smith') which is like OR'ing together 3 Name =.
I also don't think that SSRS will handle this correctly in a textbox parameter like it does with a multi-value select list. You may need to write some custom code to build your where so your dataset query would look like:
='Select DISTINCT NAMES.NAME_ID,
NAMES.NAME_ID + ' ' + NAMES.NAME1 as id_and_name,
NAMES.NAME1
FROM NAMES,
NAMES' + Code.BuildWhere(@namelike)
Then your custom code would do something like this:
[font="Courier New"] Function BuildWhere(ByVal NameList As String) As String
Dim strWhere As String = "Where "
Dim strNames As String() = NameList.Split(",")
Dim i As Integer = 0
While i < strNames.Length
If i = 0 Then
strWhere = strWhere & " Name Like '%" & strNames(i) & "%'"
Else
strWhere = strWhere & " Or Name Like '%" & strNames(i) & "%'"
End If
i = i + 1
End While
Return strWhere
End Function[/font]
I see I was second. Yes you should consider sql injection as well. In my code the "&" is an "&" the VB concatenation character.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2008 at 9:39 am
Then your custom code would do something like this:
[font="Courier New"] Function BuildWhere(ByVal NameList As String) As String
Dim strWhere As String = "Where "
Dim strNames As String() = NameList.Split(",")
Dim i As Integer = 0
While i < strNames.Length
If i = 0 Then
strWhere = strWhere & " Name Like '%" & strNames(i) & "%'"
Else
strWhere = strWhere & " Or Name Like '%" & strNames(i) & "%'"
End If
i = i + 1
End While
Return strWhere
End Function[/font]
I see I was second. Yes you should consider sql injection as well. In my code the "&" is an "&" the VB concatenation character.
Hi Jack, Thanks for your reply, It sounds like the kind of thing im after. But I'd really like to do this using SQL as much as possible, Do you know if I'd be able to use a case statement using an 'in' and 'like'?
The reason for this is that I'm trying to create a paralell report in sybase's report tool 'Infomaker'. Some aspects of my reports may be pulled out of the SSRS reports and used in a client application.
Sorry if it sounds like i've moved the goal posts, I just hadn't anticipated alternative code.
Regards
Dan
September 23, 2008 at 9:50 am
September 23, 2008 at 10:04 am
Adrian Nichols (9/23/2008)
Hi guys,If you wanted to keep this on the SQL Server side, you could use Full Text Search? :crazy:
I don't think Full Text search is useful in this case, but I'm not an expert or even really a novice in that.
As far as keeping it in SQL you could do something with a tally or numbers table to split the string into a table and then join on it using Like in the join. Check out these articles on tally tables:
http://www.sqlservercentral.com/articles/TSQL/62867/
http://www.sqlservercentral.com/articles/Advanced+Querying/2547/
You may want to encapsulate the code in a stored procedure if you need to go this route. I normally do that anyway, but not everyone agrees with me on that:cool:
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2008 at 11:02 am
But I'd really like to do this using SQL as much as possible, Do you know if I'd be able to use a case statement using an 'in' and 'like'?
That's the spirit! If you are able to enforce a limit to the number of values, there are a couple of solutions in T-SQL. Typically I don't use the IN operator unless absolutely necessary, for performance (...performance gurus chime in here...)
Can you limit the values to something like 3? If so, use CHARINDEX, and also a CASE statement to check for empty values, in the where clause.
But before that, why are you using the "FROM NAMES, NAMES" phrase? or is this a typo?
SELECT DISTINCT NAMES.NAME_ID,
NAMES.NAME_ID + ' ' + NAMES.NAME1 as id_and_name,
NAMES.NAME1
FROM NAMES,
NAMES
WHERE NAMES.NAME1 LIKE '%' + (@namelike) + '%'
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
September 24, 2008 at 1:57 am
bkDBA (9/23/2008)
But I'd really like to do this using SQL as much as possible, Do you know if I'd be able to use a case statement using an 'in' and 'like'?
That's the spirit! If you are able to enforce a limit to the number of values, there are a couple of solutions in T-SQL. Typically I don't use the IN operator unless absolutely necessary, for performance (...performance gurus chime in here...)
Can you limit the values to something like 3? If so, use CHARINDEX, and also a CASE statement to check for empty values, in the where clause.
But before that, why are you using the "FROM NAMES, NAMES" phrase? or is this a typo?
SELECT DISTINCT NAMES.NAME_ID,
NAMES.NAME_ID + ' ' + NAMES.NAME1 as id_and_name,
NAMES.NAME1
FROM NAMES,
NAMES
WHERE NAMES.NAME1 LIKE '%' + (@namelike) + '%'
Hi bkDBA.
Thanks for your help here. Your correct, "From Names, Names" is a mistake, I'd trimmed the sql query to simplify it for anyone reading it, the actual query has a few 'covert' and 'ltrim' functions in, all are unnescessary to the reader. Thanks for you help again.
Regards
Dan
September 24, 2008 at 7:00 am
Here's one possible sample solution using 2 values in a comma-separated variable. You can run this against any database. You'll want to add checks for empty strings passed in, as this code would return all rows in the table if you pass in an empty string. You'll also want to add a check for the case where a user does not enter a comma, if that's a possibility.
BTW, the string parsing functions of T-SQL are extremely powerful and one of my favorite features.
--T-SQL example of parsing a multi-value variable into multiple where clause conditions
--Set up input variable
declare @namelike as varchar(30)
select @namelike='master,log'
--Validate input variable
select @namelike = replace(@namelike,'''','')
select @namelike = replace(@namelike,'-','')
select @namelike = replace(@namelike,';','')
--Sample select using charindex to parse the input variable
select distinct names.name,
'%' + left(@namelike,charindex(',',@namelike)-1) + '%' as value1,
'%' + substring(@namelike,charindex(',',@namelike)+1, len(@namelike)-charindex(',',@namelike)) + '%' as value2
from sysfiles as names
where names.name like '%' + left(@namelike,charindex(',',@namelike)-1) + '%'
or names.name like '%' + substring(@namelike,charindex(',',@namelike)+1, len(@namelike)-charindex(',',@namelike)) + '%'
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
September 24, 2008 at 8:08 am
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
Failing to plan is Planning to fail
September 26, 2008 at 8:31 am
Firstly. Thanks to everyone for their help, However i'm still stuck.
I've now got two problems
1. I've followed the link from Medhivanan and added the following to my query, I've also moved my query to a stored procedure.
CREATE @TempList TABLE (NAMEID INT)
DECLARE @NAMEID varchar(10), @Pos int
SET @NameList = LTRIM(RTRIM(@NameList))+ ','
SET @Pos = CHARINDEX(',', @NameList, 1)
IF REPLACE(@NameList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @NAMEID = LTRIM(RTRIM(LEFT(@NameList, @Pos - 1)))
IF @NAMEID <> ''
BEGIN
INSERT INTO @TempList (NAMEID) VALUES (CAST(@NAMEID AS int))
END
SET @NameList = RIGHT(@NameList, LEN(@NameList) - @Pos)
SET @Pos = CHARINDEX(',', @NameList, 1)
END
END
with a simple select statement it works
SELECT NAMEID, NAME1 from NAMES join @TEMPLIST tl on NAMES.NAME_ID = TL.NAMEID
However, this only partially works, it will only work if I only enter one name. If I select two names from the multi value parameter It fails with the following error message.
An error occurred during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the dataset Case.
Conversion failed when converting the varchar value '1234, 4567, 7890, 5599, 9877, 3211'
I've set the Multi-value parameter type to 'Text'
Where is this going wrong?
2. I'll need to add more multi-value parameters into the query, can I just chain them up one after another? Is that the best/most efficient way?
Regards
Dan
September 26, 2008 at 8:46 am
What is the data type of the parameter in your stored procedure? Sounds like this is your error.
2. Sure you can just add parameters to the SP, you'll just have to parse them all.
I'll also comment that in reality the Tally or Numbers table solution to splitting a delimited string is probably the best performance-wise.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 26, 2008 at 8:53 am
These are my parameters
@TMC_pNAME_ID VARCHAR(500) ,
@TMC_pROLE_TYPE_ID INT ,
@TMC_pCATCHWORD VARCHAR(500) ,
@TMC_pStates VARCHAR(500) ,
@TMC_Status_label VARCHAR(500) ,
@TMC_pStart_date datetime ,
@TMC_pEND_date datetime
The multi value parameter which I started this thread on is the @TMC_pNAME_ID parameter.
I intend to have the following Multi value parameters, each one will point to its respective SP parameter, Hence the reason for question 2. Do i add a new routein to pass each multi value parameter? @TMC_pROLE_TYPE_ID INT ,
@TMC_pStates VARCHAR(500) ,
@TMC_Status_label VARCHAR(500) ,
Regards
Dan
September 26, 2008 at 9:15 am
Here's a quote from BOL about MultiValue paramters:
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
This is only partially true as you can still use a stored procedure, you just can't use the parameter directly, you just need to do this (also from BOL):
=Join(Parameters!ParameterName.Value,", ")
Join is an expression that concatenates all the values in the array of a multivalued parameter of type String into one string.
The second value passed to the join function is the delimiter.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply