0

I have implemented a SSRS 2008 report which has a parameter embed_id which is supposed to be numeric. I have also added a validation code to check if the entered embed_id is valid (numeric) or not.

A stored procedure is used to fetch the report data. When I enter some letters like abc in the embed_id textbox, validation code correctly returns false but SP execution still takes place giving the following error on UI -

Query execution failed for dataset 'embed'. Invalid column name 'abc'.

I have added a red colored message showing custom text like "Embed ID is not valid" but that is not shown in this case.

Is there a way to cancel SP execution on the validation error? Or am I supposed to handle it within SP itself such that SP returns zero rows on receiving such invalid input?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mandar
  • 303
  • 4
  • 18
  • 1
    What is the parameter datatype set to? Presumably text? Why not just set it to numeric and let reporting services do the validation? – Martin Smith Jun 17 '14 at 06:26
  • @MartinSmith - Thanks for that! It was set to text. I tried your suggestion and set that to Integer. But when I entered letters, the parameters at the top disappeared and all I got on the page was this message - "The value provided for the report parameter 'embed_id' is not valid for its type. (rsReportParameterTypeMismatch)" - which isn't really pretty. It also accepts negative values. So this is not going to work for me. There is another a bit complex validation which involves two dates that I need to implement. Considering that if there is a way to stop SP execution, that will really help. – Mandar Jun 17 '14 at 08:33
  • Well AFAIK there is no way of hooking in and doing custom parameter validation in reporting services itself so you would need to do it in the stored proc itself. – Martin Smith Jun 17 '14 at 08:39
  • Though maybe something of use here? http://stackoverflow.com/q/606621/73226 – Martin Smith Jun 17 '14 at 08:48

2 Answers2

2

Your last sentence is the best answer - instead of crashing your SP with an error, return 0 rows.

In SSRS, objects such as a Tablix have a customizable message which is shown when no rows are returned by the underlying dataset.

Another way to work around this would be to base your Date parameters (or better yet some other parameter) on a Dataset, and validate the embed_id in the logic for that dataset, along the lines of:

WHERE (SELECT COUNT(*) FROM embed_table WHERE embed_id = @embed_id ) > 0

With that in place, the users wont get a list of values for that parameter until they have entered a valid embed_id.

I'm really over the SSRS Parameter UI - it hasnt been updated in over 10 years and the functionality even then was woeful.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • It would also be nice if they gave the properties window a bit of TLC as well. Quite annoying that some strings are case sensitive and others aren't. For some of the properties double clicking to the left toggles through the list, for others (including booleans that you just want to switch from true to false or vice versa the double click doesn't work ...) – Martin Smith Jun 18 '14 at 21:30
  • 1
    I am marking this as accepted answer. In the end I had to make some small change in my SP in addition to the custom validation I added in SSRS. I am providing the change description as another answer just in case that can help someone else. Thanks! – Mandar Jun 27 '14 at 19:39
0

In the end I reached conclusion that there is no way to stop execution of a stored procedure from SSRS. So I have implemented the validation in 2 steps.

  1. Added custom validation at report level which checks if the embed id is really numeric. If not, it will show an error message.

  2. In the SP, earlier I used to get the mentioned error because my query was something like this (in a simplified way):

    select * from embed_table where embed_id = @embed_id

When @embed_id was not numeric, say abc, then query used be constructed as

select * from embed_table where embed_id = abc

which would throw the mentioned error and due to which message in #1 above would not be displayed. So I changed the query to make string comparison.

select * from embed_table where cast(embed_id as varchar) = cast(@embed_id as varchar)

This will return correct result on numeric embed_ids and will not return anything on non-numeric embed_ids as we do not have such ids in the table. With this change, query will not break and validation message will be shown to the user.

Mandar
  • 303
  • 4
  • 18