Blank Parameters do not work when running stored proc in Data view

I have a stored procedure that gives different outputs based on whether an
input parameter is given or not. When I set the parameter to blank, not
NULL, I assume this will give the same result as running it in Query Analyzer
and not inputting anything for that parameter, but instead I get a "input
string was not in the correct format" error. Is this a bug? It is not the
behavior I would expect. I think it is very important that blank parameters
work correctly.I'm talking about the parameters in a SQL Server stored procedure. When I
run the report, it's fine if I allow blank values, and there is no error.
Only in Data View do I get the error when I try to run it or do refresh
fields, and it asks me to input all parameters.
"Aaron Williams" wrote:
> Did you set the parameter to allow blank values?
> "Stefan Wrobel" wrote:
> > I have a stored procedure that gives different outputs based on whether an
> > input parameter is given or not. When I set the parameter to blank, not
> > NULL, I assume this will give the same result as running it in Query Analyzer
> > and not inputting anything for that parameter, but instead I get a "input
> > string was not in the correct format" error. Is this a bug? It is not the
> > behavior I would expect. I think it is very important that blank parameters
> > work correctly.|||That seems about correct. I assumed if you specified a parameter as blank it
was like not sending it to the SP at all. For clarification, I only need to
use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
need from the parameters list. I assume its as if the Report Server doesn't
even know these parameters exist, and doesn't attempt to set them to
anything. This is how I want it. However, in Data View, if I try to get the
fields in the first place, I have to give some sort of value for all 4
parameters, because if I leave the 2 I don't need set to <Blank>, I get that
error. The only time I ever have run into this problem is while designing
the Report, but not anywhere else. It works fine in Query Analyzer, and also
when I run the report through Report Server.
Actually, now that I think about it, to clarify, here's a simple example
that applies to 1 of the 2 parameters.
@.param1 int,
@.param2 int = @.param1
So basically if I don't specify anything for @.param2, it should just be
equal to what I specified for @.param1. However, there doesn't seem to be any
way in Data View not to specify anything. Leaving it set to <blank> I think
it still tries to set the parameter to something, and obviously to something
that causes an error.
I think that's about the best I can do to explain it. It's not like it
prevents me from using Reporting Services, but it is very annoying and seems
counterintuitive.
Thanks for your help.
"Aaron Williams" wrote:
> Could you provide an example of your stored proc?
> Lets make sure we're on the same page. If I get what you're saying, your
> stored proc allows you to send a parameter value if desired, but if you don't
> send anything, then it will still return a query. So your stored proc might
> look something like this:
> CREATE proc MyStoredProcedure
> @.MyParameter int = null
> AS
> SELECT * FROM MyTable
> WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> In this case, you can run it in query analyzer like so:
> exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> In this case, the first would return one record, and the second would return
> ALL records. So, if this is what you mean, then your problem is that when
> you try to pass a ' ' (blank value) to the report parameter you get an error.
> '
> -Aaron
>
> "Stefan Wrobel" wrote:
> > By the way, I'm speaking about when designing a report in Visual Studio
> >
> > "Stefan Wrobel" wrote:
> >
> > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > run the report, it's fine if I allow blank values, and there is no error.
> > > Only in Data View do I get the error when I try to run it or do refresh
> > > fields, and it asks me to input all parameters.
> > >
> > > "Aaron Williams" wrote:
> > >
> > > > Did you set the parameter to allow blank values?
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > work correctly.|||Could you provide an example of your stored proc?
Lets make sure we're on the same page. If I get what you're saying, your
stored proc allows you to send a parameter value if desired, but if you don't
send anything, then it will still return a query. So your stored proc might
look something like this:
CREATE proc MyStoredProcedure
@.MyParameter int = null
AS
SELECT * FROM MyTable
WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
In this case, you can run it in query analyzer like so:
exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
In this case, the first would return one record, and the second would return
ALL records. So, if this is what you mean, then your problem is that when
you try to pass a ' ' (blank value) to the report parameter you get an error.
'
-Aaron
"Stefan Wrobel" wrote:
> By the way, I'm speaking about when designing a report in Visual Studio
> "Stefan Wrobel" wrote:
> > I'm talking about the parameters in a SQL Server stored procedure. When I
> > run the report, it's fine if I allow blank values, and there is no error.
> > Only in Data View do I get the error when I try to run it or do refresh
> > fields, and it asks me to input all parameters.
> >
> > "Aaron Williams" wrote:
> >
> > > Did you set the parameter to allow blank values?
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I have a stored procedure that gives different outputs based on whether an
> > > > input parameter is given or not. When I set the parameter to blank, not
> > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > and not inputting anything for that parameter, but instead I get a "input
> > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > behavior I would expect. I think it is very important that blank parameters
> > > > work correctly.|||I think the way you want it do it will really only work in Query Analyzer.
Reporting Services gets a list of all the parameters in the stored procedure
when you create the dataset. I assume you could do what you said and remove
the unnessisary parameters from the list, but then you no longer have the
option of using them which is obviously something you want to do.
Another option would be to change your stored procedure so that the optional
parameters can accept NULL's and then change the values later. That way
Reporting Services can send a null value to those parameters and you can
still use them the way you desire. Here's an example of how I would change
your stored proc so you can do what you're looking for.
CREATE proc Tmp_TestParams
@.param1 int,
@.param2 int = null,
@.param3 int = null
AS
-- Here's where you set the value of the blank parameters
IF @.param2 IS null
BEGIN
SET @.param2 = @.param1
END
IF @.param3 is null
BEGIN
SET @.param3 = @.param2
END
SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
Go
exec Tmp_TestParams 1, null, null
Hope this helps!
-Aaron
"Stefan Wrobel" wrote:
> That seems about correct. I assumed if you specified a parameter as blank it
> was like not sending it to the SP at all. For clarification, I only need to
> use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> need from the parameters list. I assume its as if the Report Server doesn't
> even know these parameters exist, and doesn't attempt to set them to
> anything. This is how I want it. However, in Data View, if I try to get the
> fields in the first place, I have to give some sort of value for all 4
> parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> error. The only time I ever have run into this problem is while designing
> the Report, but not anywhere else. It works fine in Query Analyzer, and also
> when I run the report through Report Server.
> Actually, now that I think about it, to clarify, here's a simple example
> that applies to 1 of the 2 parameters.
> @.param1 int,
> @.param2 int = @.param1
> So basically if I don't specify anything for @.param2, it should just be
> equal to what I specified for @.param1. However, there doesn't seem to be any
> way in Data View not to specify anything. Leaving it set to <blank> I think
> it still tries to set the parameter to something, and obviously to something
> that causes an error.
> I think that's about the best I can do to explain it. It's not like it
> prevents me from using Reporting Services, but it is very annoying and seems
> counterintuitive.
> Thanks for your help.
> "Aaron Williams" wrote:
> > Could you provide an example of your stored proc?
> >
> > Lets make sure we're on the same page. If I get what you're saying, your
> > stored proc allows you to send a parameter value if desired, but if you don't
> > send anything, then it will still return a query. So your stored proc might
> > look something like this:
> >
> > CREATE proc MyStoredProcedure
> > @.MyParameter int = null
> >
> > AS
> >
> > SELECT * FROM MyTable
> > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> >
> > In this case, you can run it in query analyzer like so:
> >
> > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> >
> > In this case, the first would return one record, and the second would return
> > ALL records. So, if this is what you mean, then your problem is that when
> > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > '
> >
> > -Aaron
> >
> >
> > "Stefan Wrobel" wrote:
> >
> > > By the way, I'm speaking about when designing a report in Visual Studio
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > fields, and it asks me to input all parameters.
> > > >
> > > > "Aaron Williams" wrote:
> > > >
> > > > > Did you set the parameter to allow blank values?
> > > > >
> > > > > "Stefan Wrobel" wrote:
> > > > >
> > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > work correctly.|||Good suggestion, thanks!
"Aaron Williams" wrote:
> I think the way you want it do it will really only work in Query Analyzer.
> Reporting Services gets a list of all the parameters in the stored procedure
> when you create the dataset. I assume you could do what you said and remove
> the unnessisary parameters from the list, but then you no longer have the
> option of using them which is obviously something you want to do.
> Another option would be to change your stored procedure so that the optional
> parameters can accept NULL's and then change the values later. That way
> Reporting Services can send a null value to those parameters and you can
> still use them the way you desire. Here's an example of how I would change
> your stored proc so you can do what you're looking for.
> CREATE proc Tmp_TestParams
> @.param1 int,
> @.param2 int = null,
> @.param3 int = null
> AS
> -- Here's where you set the value of the blank parameters
> IF @.param2 IS null
> BEGIN
> SET @.param2 = @.param1
> END
> IF @.param3 is null
> BEGIN
> SET @.param3 = @.param2
> END
> SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
> Go
> exec Tmp_TestParams 1, null, null
> Hope this helps!
> -Aaron
> "Stefan Wrobel" wrote:
> > That seems about correct. I assumed if you specified a parameter as blank it
> > was like not sending it to the SP at all. For clarification, I only need to
> > use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> > need from the parameters list. I assume its as if the Report Server doesn't
> > even know these parameters exist, and doesn't attempt to set them to
> > anything. This is how I want it. However, in Data View, if I try to get the
> > fields in the first place, I have to give some sort of value for all 4
> > parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> > error. The only time I ever have run into this problem is while designing
> > the Report, but not anywhere else. It works fine in Query Analyzer, and also
> > when I run the report through Report Server.
> >
> > Actually, now that I think about it, to clarify, here's a simple example
> > that applies to 1 of the 2 parameters.
> >
> > @.param1 int,
> > @.param2 int = @.param1
> >
> > So basically if I don't specify anything for @.param2, it should just be
> > equal to what I specified for @.param1. However, there doesn't seem to be any
> > way in Data View not to specify anything. Leaving it set to <blank> I think
> > it still tries to set the parameter to something, and obviously to something
> > that causes an error.
> >
> > I think that's about the best I can do to explain it. It's not like it
> > prevents me from using Reporting Services, but it is very annoying and seems
> > counterintuitive.
> >
> > Thanks for your help.
> >
> > "Aaron Williams" wrote:
> >
> > > Could you provide an example of your stored proc?
> > >
> > > Lets make sure we're on the same page. If I get what you're saying, your
> > > stored proc allows you to send a parameter value if desired, but if you don't
> > > send anything, then it will still return a query. So your stored proc might
> > > look something like this:
> > >
> > > CREATE proc MyStoredProcedure
> > > @.MyParameter int = null
> > >
> > > AS
> > >
> > > SELECT * FROM MyTable
> > > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> > >
> > > In this case, you can run it in query analyzer like so:
> > >
> > > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> > >
> > > In this case, the first would return one record, and the second would return
> > > ALL records. So, if this is what you mean, then your problem is that when
> > > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > > '
> > >
> > > -Aaron
> > >
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > By the way, I'm speaking about when designing a report in Visual Studio
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > > fields, and it asks me to input all parameters.
> > > > >
> > > > > "Aaron Williams" wrote:
> > > > >
> > > > > > Did you set the parameter to allow blank values?
> > > > > >
> > > > > > "Stefan Wrobel" wrote:
> > > > > >
> > > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > > work correctly.|||Did you set the parameter to allow blank values?
"Stefan Wrobel" wrote:
> I have a stored procedure that gives different outputs based on whether an
> input parameter is given or not. When I set the parameter to blank, not
> NULL, I assume this will give the same result as running it in Query Analyzer
> and not inputting anything for that parameter, but instead I get a "input
> string was not in the correct format" error. Is this a bug? It is not the
> behavior I would expect. I think it is very important that blank parameters
> work correctly.|||By the way, I'm speaking about when designing a report in Visual Studio
"Stefan Wrobel" wrote:
> I'm talking about the parameters in a SQL Server stored procedure. When I
> run the report, it's fine if I allow blank values, and there is no error.
> Only in Data View do I get the error when I try to run it or do refresh
> fields, and it asks me to input all parameters.
> "Aaron Williams" wrote:
> > Did you set the parameter to allow blank values?
> >
> > "Stefan Wrobel" wrote:
> >
> > > I have a stored procedure that gives different outputs based on whether an
> > > input parameter is given or not. When I set the parameter to blank, not
> > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > and not inputting anything for that parameter, but instead I get a "input
> > > string was not in the correct format" error. Is this a bug? It is not the
> > > behavior I would expect. I think it is very important that blank parameters
> > > work correctly.