Optional parameter in SQL server

Question

Hi,

I have a user defined function which is used in many stored procedures which will return me some value. If it possible for me to add a new optional parameter to the same.

If I don't pass any value it should be null and if I pass some value it should take it. I don't want to go and change all the stored procedures to do so.

Example code

dbo.CalculateAverageForUser(int userid)

Can I use dbo.CalculateAverageForUser(int userid, type NVARCHAR(10) = NULL)

Answer 1

I think the easiest way is to make a stored procedure CalculateAverageForUserAndType(int userid ,type NVARCHAR(10)) Put your original code inside the new procedure. Then change CalculateAverageForUser to

dbo.CalculateAverageForUser(int userid)
AS
BEGIN
    EXEC CalculateAverageForUserAndType userid, NULL

END

This way you can slowly migrate to your new stored procedure and your old one still works.

Answer 2

If you don't want to go adjusting all of your existing stored procedures that reference the function then I think you would need to create a new function with the code from your existing one

CREATE FUNCTION CalculateAverageForUser2
(
    @userid int,
    @param2 nvarchar(10) = NULL
)
RETURNS float
AS
/*Code from existing function goes here*/

Then just change the existing function to the following

ALTER FUNCTION CalculateAverageForUser 
(
 @userid int
)
RETURNS float
AS
BEGIN
RETURN dbo.CalculateAverageForUser2(@userid, DEFAULT)
END