[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

Default parameter in stored procedure

How can I add a default paratmeter to a stored procedure, so that old applications that used to send only two parameters will not be affected?

0
f_asmaa
Asked:
f_asmaa
  • 3
1 Solution
 
adatheladCommented:
Hi,

Here's an example:

CREATE PROCEDURE stpMyProcedure
    @ExistingParam1 INTEGER,
    @ExistingParam2 INTEGER,
    @NewParam INTEGER=0 -- the "=0" assigns the default
AS
...
...

Cheers
0
 
f_asmaaAuthor Commented:
Can I change your code to be

CREATE PROCEDURE stpMyProcedure
    @ExistingParam1 INTEGER,
    @NewParam INTEGER=0, -- the "=0" assigns the default
    @ExistingParam2 INTEGER
AS
...
...
0
 
adatheladCommented:
It depends on how the stored procedure is called in the old applications but IMO you should always add new parameters on to the end of the parameter list just to be sure.

e.g.
if an application calls the procedure using an EXECUTE statement like this:
EXECUTE stp_MyProcedure 1,2

Then what you have suggested will not work as "2" will be used as the new parameter value
0
 
adatheladCommented:
f_asmaa, thanks for accepting my answer.

However, I'm confused as to why this only merited a Grade B as I supplied an exact working example within 2 minutes and gave an explanation to your follow up question very quickly also (with an example to back up my answer)?

Please review the help section of this site if you are unsure of how to grade;  experts put a lot of effort into providing helpful solutions quickly and the grade should reflect this. You may find experts are less-responsive if you under-grade.

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now