Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Procedure with default parameters Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 7:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
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.
Post #1519242
Posted Tuesday, December 3, 2013 8:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:04 PM
Points: 3,572, Visits: 8,008
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




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1519253
Posted Tuesday, December 3, 2013 8:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
If you delete the parameters from both the SSRS report & the DataSet you should be OK, as long as they've all got defaults.

Post #1519256
Posted Wednesday, December 4, 2013 2:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
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:

Error 1 [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.
Post #1519523
Posted Wednesday, December 4, 2013 2:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 39,866, Visits: 36,208
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1519525
Posted Wednesday, December 4, 2013 2:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:19 AM
Points: 314, Visits: 677
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.
Post #1519526
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse