December 3, 2013 at 7:57 am
Guys,
Unusually I'm using a production procedure in our of my reports, so we return *identical* data with the logic centralised. This proceduer has a number of parameters, I wish to return the data set when all of these are as per their specified defaults. I.e. in Management Studio 'EXEC Procedure' is all I need to do.
When I do this in Visual Studio for a report I'm prompted to enter values for the parameters before the report will run.
I could find out and 'hard code' these default values, however, as I want to maintain exact synchronicity with the production procedure I'd rather pull these through than specify them.
Is there any way I can do this?
Edit: besides creating a separate procedure which simply executes the product facing one and doesn't have any parameters, which would work.
December 3, 2013 at 8:11 am
I'm not sure if you're trying to add defaults to your report parameters or your SP parameters.
In case that you want defaults to your SP parameters, you have two options and I believe that you found one.
First option:
CREATE PROCEDURE MyProcedure(
@Param1 int = 5,
@Param2 varchar(50) = 'Some default'
)
AS ...
The second option is to default the values to NULL and assign values dinamically.
CREATE PROCEDURE MyProcedure(
@Param1 int = NULL,
@Param2 varchar(50) = NULL
)
AS
BEGIN
IF @Param1 IS NULL AND @Param2 IS NULL
BEGIN
--Code to assign default values
END
--Code for SP
END
December 3, 2013 at 8:24 am
If you delete the parameters from both the SSRS report & the DataSet you should be OK, as long as they've all got defaults.
December 4, 2013 at 2:04 am
What I'm saying is there's a proc happily in use in another situation which already has a bunch of parameters, all of which have defaults set already, I'd rather use the defaults in the procedure (which may change over time) than hard code them.
Deleting the parameters from the report didn't seem to work, SSRS whinged:
Error1[rsParameterReference] The Value expression for the query parameter ‘@X’ refers to a non-existing report parameter ‘X’. Letters in the names of parameters must use the correct case.
However, my 'hack' of wrapping the procedure within another procedure seems to work from what I can see so far.
December 4, 2013 at 2:09 am
Luis Cazares (12/3/2013)
The second option is to default the values to NULL and assign values dinamically.
CREATE PROCEDURE MyProcedure(@Param1 int = NULL,
@Param2 varchar(50) = NULL
)
AS
BEGIN
IF @Param1 IS NULL AND @Param2 IS NULL
BEGIN
--Code to assign default values
END
--Code for SP
END
I don't recommend this one because it messes with the optimiser's ability to sniff parameter values for cardinality estimates.
http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2013 at 2:15 am
The 'wrapping' seems to work okay:
CREATE PROCEDURE Proc_Report AS
EXEC Proc_Product
Obviously they aren't called that but you get the idea - the Proc_Product has about 5-6 parameters, by doing it this way if we change what we show on our products and hence change the defaults the report should pull them through automatically.
I didn't really want to create an additional SP but it's not like I've had to create 40 more or something.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply