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:
SELECT
     rb.[GivenDate]
     , rb.[DateStart]
     , rb.[DateFinish]
     , rb.[ContactUsername]
     , rb.[ContactName]
     , rb.[Room]
     , rb.[BookingNotes]
FROM
     roombookings rb
WHERE
    rb.[GivenDate] BETWEEN @GivenDate AND @ToDate
AND
    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:
SELECT
     rb.[GivenDate]
     , rb.[DateStart]
     , rb.[DateFinish]
     , rb.[ContactUsername]
     , rb.[ContactName]
     , rb.[Room]
     , rb.[BookingNotes]
FROM
     roombookings rb
WHERE
    rb.[GivenDate] BETWEEN @GivenDate AND @ToDate
AND
    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!

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.