Sunday, December 21, 2014
   
Text Size
Login

SSRS Use T-SQL Like with a Parameter

This must have been so obvious to everyone else that nobody bothered to write an article on it... till now.

Situation
I have a report that returns room bookings based on a user and given a date range. The problem is that there are a few thousand users and Microsoft's SQL Server Reporting Services interface isn't the most fun to scroll endlessly down. Advanced users can type the name really fast for it to auto-scroll down to the desired name. Our advanced users are exceptions to the rule.

Problem
Consider the following query:

  1.  SELECT 
  2.       rb.[GivenDate] 
  3.       , rb.[DateStart] 
  4.       , rb.[DateFinish] 
  5.       , rb.[ContactUsername] 
  6.       , rb.[ContactName] 
  7.       , rb.[Room] 
  8.       , rb.[BookingNotes] 
  9.  FROM 
  10.       roombookings rb 
  11.  WHERE 
  12.      rb.[GivenDate] BETWEEN @GivenDate AND @ToDate 
  13.  AND 
  14.      rb.[ContactName] LIKE '%@GivenName%'   -- WARNING: 0 matching rows 
This returns nothing. Took me a while to figure why but it's the parameter that's the issue. You don't enclose it with apostrophes for one thing. So let me rewrite this:

  1.  SELECT 
  2.       rb.[GivenDate] 
  3.       , rb.[DateStart] 
  4.       , rb.[DateFinish] 
  5.       , rb.[ContactUsername] 
  6.       , rb.[ContactName] 
  7.       , rb.[Room] 
  8.       , rb.[BookingNotes] 
  9.  FROM 
  10.       roombookings rb 
  11.  WHERE 
  12.      rb.[GivenDate] BETWEEN @GivenDate AND @ToDate 
  13.  AND 
  14.      rb.[ContactName] LIKE '%'+@GivenName+'%'  --WORKS! Enclose percent signs as strings. 
For anyone that thinks there exists stupid questions, here's a silly answer which I hope helps!

Comments   

Trish M
# Trish Thu, 12th September 2013
Excellent tip. Thanks!
Like | Dislike | 0 Reply | Reply with quote | Quote
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Basic Android App using Google Maps and Current Location

    • Wed 17-Dec-14
      Is that how it's spelt? setContentVie( "View". If not try going to build and "clean project". Also ...
      Joel L.  
    • Tue 16-Dec-14
      I have problem in "cannot resolved R" " setContentVie(R .layout.activit y_maps);" :cry: How to solve ...
      LucasR
  • Migrate Joomla! 1.5.x to 2.5.x+

    • Thu 18-Dec-14
      Interesting report LLiSEIL! I'll definitely have to review it when Joomla 3.5 comes out. Error 1: is ...
      Joel L.  
    • Tue 16-Dec-14
      * Categories: - duplicates, I suppose from the multiple times I launched the script. - love the notes ...
      LLiSEIL  
    • Tue 16-Dec-14
      SQL ERror 3 > -- Step 2) Import articles from J15 and insert as articles in J30 INSERT INTO `newbase_j25`.d ...
      LLiSEIL