geoff5 (9/27/2013)
Although I cannot tell you why the procedure you are using does not work -- I would have done the same thing more than likely -- I can suggest an alternate way to code the WHERE clause in the stored procedure to eliminate the LIKE comparison from the mix. Try the following:
WHERE LEFT(cipcode, LEN(@cip)) = @cipUsing this code if a person chooses a two-digit option, it will match the left two digits of the cipcode column to the @cip variable. If they select a four-digit code, it will match the left four digits, and so forth. (I know you are only providing two-digit options in the report parameter, but this approach will allow for other options should you want to allow longer parts of the code to match.)
This approach should be functionally identical to the LIKE comparison you used, but perhaps it will work better in this context.
This code will have a negative effect on performance if the OP's table is very large. Typically, a query like this over a large table will be supported by an index on [cipcode], but wrapping the column [cipcode] in the LEFT() function in a WHERE clause will prevent SQL Server from performing a seek on this index. Instead, it will have to scan the entire index to evaluate the result of the LEFT() function for every row to filter out those that don't satisfy the WHERE condition. This may not be a big deal of the OP's table is not very large (SQL Server will opt to scan small tables even when it could perform a seek because scans can be less expensive than seeks on small tables), but scanning a larger table every time the query runs could be disastrous.
Back to the original problem, is it possible that the [cipcode] values you select to populate the parameter drop-down list have trailing spaces, Amy.G? This is very likely if the [cipcode] column in the database is a char() type with a length greater than 2. For example, if [cipcode] is a char(5) type, the value '51' will be padded with 3 trailing blanks. The LEN function still returns 2 for the value '51 ', but the expression @cip + '%' in your WHERE clause would evaluate as '51 ' + '%' (i.e., those blanks would be included in the resulting string) and the LIKE comparison would fail for values such as '51542' and '5154'. The LIKE comparison still works for '51 ', which is why you get that row only. The same thing will happen if the parameter @cip is specified as a char() type with length greater than 2. Geoff5's code works because the LEFT strips off the trailing blanks from [cipcode], with the major drawback identified above, and the equality operator disregards any trailing blanks or spaces in @cip.
Try this instead: WHERE cipcode LIKE RTRIM(@cip) + '%'
RTRIM strips those troublesome trailing blanks from @cip (any trailing blanks in [cipcode] don't matter because they will not affect the outcome of the LIKE comparison) and applying it to the variable rather than the column value avoids the problem explained above.
Jason Wolfkill