Reporting Services Wild Card Search

Hi,
I am creating reports in Reporting Services querying a SQL 2005 stored procedure.

The report requires a 'name' input with a wild card option on the search. I have 2 stored procedures, example 1 returns only the actual name entered, 'JO'. Example 2 returns all data that contains 'JO' at the beginning. My question is why is the first example not behaving in the anticipated way by returning the wild card data as expected?

As far as the LTRIM(RTRIM operators in the second example, if I add LTRIM to example 1 I get the same result and adding only RTRIM I get no output at all. Can anyone see what the issue might be?

Thanks,
Jonathanr

Example 1 stored procedure:
SELECT * FROM dbo.RS_Rates_Person_Mailing_Rate
WHERE [Surname] LIKE @SURNAME + '%'
and [Rate account no, Debtor no, Creditor no] NOT LIKE '%RTZ%'
ORDER BY [Person ID] ASC    
                         
Name entered 'jo'
data returned
JO

Example 2 stored procedure:
SELECT * FROM RS_AP_Apoitrn_Apoitrnh_Creditor_Details
WHERE LTRIM(RTRIM(t2_Fnorg_name)) LIKE  @NAME + '%'
ORDER BY Fntrans_date ASC  

Name entered 'jo'
data returned
JO COLLINS                              
JOHN DIAZ                              
JOE'S MEAT                              
JOHN SHUTTLEWORTH                      
JOHN GLYNN                              
JONATHON DRANE                          
JOHNSTONE ENVIROMENTAL  
JOSEPH MICHAEL CIATAR                  
JOY HYNES  
jonathanrAsked:
Who is Participating?
 
jonathanrAuthor Commented:
OK, I found the solution. It has to be set as 'varchar' not 'char', eventhough it is a char.
0
 
sampipesCommented:
You might want to check you are allowing blank but not allowing nulls in the report parameters in the report.

To cover yourself completely in the SPs use ISNULL(@param,'')
0
 
sampipesCommented:
Ignore me I was talking tosh.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sampipesCommented:
One thing that does occur to me is that you are only looking in surname whereas query 2 looks like it is looking in a full name field.
0
 
jonathanrAuthor Commented:
Hi sampipes,

The Name/Surname is set correctly for each SP so no dramas there.

jonathanr
0
 
griffonageCommented:
your examples aren't querying the same table, could it be that only JO exists in the first table?
0
 
jonathanrAuthor Commented:
Hi griffonage,

No, the table has a full range of names beginning with 'Jo'.

0
 
sampipesCommented:
have you tried RTRIM(@SURNAME) in case the concat is comeing out as 'Jo %'
0
 
sampipesCommented:
I meant to say RTRIM on it's own rather than with an LTRIM
0
 
jonathanrAuthor Commented:
Hi sampipes,

using RTRIM by itself provides no results at all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.