SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Procedure with default parameters


Procedure with default parameters

Author
Message
Rob-350472
Rob-350472
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 684
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.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16615 Visits: 19098
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
If you delete the parameters from both the SSRS report & the DataSet you should be OK, as long as they've all got defaults.
Rob-350472
Rob-350472
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 684
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87629 Visits: 45272
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

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


Rob-350472
Rob-350472
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 684
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search