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:
copyraw
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%' -- WARNING: 0 matching rows
- 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
copyraw
For anyone that thinks there exists stupid questions, here's a silly answer which I hope helps!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.
- 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.
Category: SQL Server Reporting Services :: Article: 336
