1

I'm trying to write a query that will allow me to include or exclude a filter in my where statement based on whether a parameter passed in is set to true or false. So far my statement is

IF @IncludeAttended = 'true'
begin
    SELECT        id, fullName, Address, PhoneNumber, NeverAttended
    FROM            Guests_View
    WHERE        (GroupID = @groupid) 
end else begin
    SELECT        id, fullName, Address, PhoneNumber, NeverAttended
    FROM            Guests_View
    WHERE        (GroupID = @groupid) 
    AND NeverAttended = 'false'
end

So I'm trying to filter based on whether or not the guest has ever attended an event. Running the above SQL in Management Studio will get me appropriate results with no issues, but when I try to run this in SSRS, I get an error message Conversion failed when converting the varchar value 'AM13425' to data type int.

I have tried several different statements and methods to find where this string might be, but I can't find it anywhere in the database, nor can I find it anywhere in the project itself, so as far as I can tell, the data doesn't exist. Wiping the table and repopulating it with fresh test data doesn't help either, the message just resurfaces. Also, if I take out the @IncludeAttended parameter, and just run only one half of the statement above (among various iterations), I can get the results back, but I need this report to allow this filtering. What else can I try, and where can I look for this phantom data that's causing me the problem?

closed as off-topic by dezso, billinkc, Marian, Philᵀᴹ, Tom V Jan 13 '16 at 20:57

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, billinkc, Marian, Philᵀᴹ, Tom V
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What is the definition of the view? – Martin Smith Jan 9 '16 at 12:05
  • @MartinSmith complex. it encompases 11 different tables thanks to the original company's desire for support revenue, but we now get to support ourselves. – Tom A Jan 11 '16 at 23:51
1

Could you check the data type of "id" in the report dataset?

Maybe when the NeverAttended = 'false' is used, the data returned is having text data whereas the dataset is having Id as integer type.

  • Turns out, the view is accessing data below it that contains both alpha and numeric IDs in the same column. – Tom A Jan 12 '16 at 23:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.