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(userid int)
Can I use dbo.CalculateAverageForUser(userid int, type NVARCHAR(10) = NULL)
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
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.
