Blank Parameters

I am trying to use a stored procedure that can accept blank parameters to
return a complete set of data. when I use the preview screen in reporting
services, the sproc does not return anything, yet when i run the sproc from
anywhere else, it works. Is there a setting I am missing to be able pass
balnk values to a sproc from the report?
Yes, if i pass values to the report, not blanks, the sproc does run and
returns data.
--
John Cleveland
Network Manager
Urban systems LtdTry using SQL Profiler to find out what is actually sent to the stored
procedure. Blank might not be blank. Do you expect a NULL or a '' as a blank
in the sp? Anyway, SQL Profiler shows what is actually sent, so you can
modify either Parameter or Stored Procedure accordingly.
Kaisa M. Lindahl Lervik
"John Cleveland" <JohnCleveland@.discussions.microsoft.com> wrote in message
news:EA34E0D4-7E12-4730-96F5-B783261CADF4@.microsoft.com...
>I am trying to use a stored procedure that can accept blank parameters to
> return a complete set of data. when I use the preview screen in reporting
> services, the sproc does not return anything, yet when i run the sproc
> from
> anywhere else, it works. Is there a setting I am missing to be able pass
> balnk values to a sproc from the report?
> Yes, if i pass values to the report, not blanks, the sproc does run and
> returns data.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>|||When you click on preview, is it giving a small entry screen ? if yes dont
enter anything like double quotes etc.. before that is it giving a screen?
Amarnath
"John Cleveland" wrote:
> I am trying to use a stored procedure that can accept blank parameters to
> return a complete set of data. when I use the preview screen in reporting
> services, the sproc does not return anything, yet when i run the sproc from
> anywhere else, it works. Is there a setting I am missing to be able pass
> balnk values to a sproc from the report?
> Yes, if i pass values to the report, not blanks, the sproc does run and
> returns data.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>|||I ran a trace in profiler and here is what I got:
exec spCostReport @.Project=N'', @.Phase=N''
These are the 2 params i am trying to send. I have default values set in
the sproc that should kick in if the params are blank. I exported the trace
to make sure there was nothing between the '', and there wasn't. Any thing
else to check for?
When I run this from the dataset screen in reporting services, it runs fine
leaving the params blank. it only seems to be when the report is run.
--
John Cleveland
Network Manager
Urban systems Ltd
"Kaisa M. Lindahl Lervik" wrote:
> Try using SQL Profiler to find out what is actually sent to the stored
> procedure. Blank might not be blank. Do you expect a NULL or a '' as a blank
> in the sp? Anyway, SQL Profiler shows what is actually sent, so you can
> modify either Parameter or Stored Procedure accordingly.
> Kaisa M. Lindahl Lervik
> "John Cleveland" <JohnCleveland@.discussions.microsoft.com> wrote in message
> news:EA34E0D4-7E12-4730-96F5-B783261CADF4@.microsoft.com...
> >I am trying to use a stored procedure that can accept blank parameters to
> > return a complete set of data. when I use the preview screen in reporting
> > services, the sproc does not return anything, yet when i run the sproc
> > from
> > anywhere else, it works. Is there a setting I am missing to be able pass
> > balnk values to a sproc from the report?
> >
> > Yes, if i pass values to the report, not blanks, the sproc does run and
> > returns data.
> >
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> >
>
>