Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 947
  • Last Modified:

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  
0
jonathanr
Asked:
jonathanr
  • 5
  • 4
1 Solution
 
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
 
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
jonathanrAuthor Commented:
OK, I found the solution. It has to be set as 'varchar' not 'char', eventhough it is a char.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now