I added a parameter to an existing user defined function. This user-defined function was
used in many procedures across system. Whenever a parameter is added to a user defined function, then we will have to change the calling of the function and pass the value for the new parameter added. Even though the parameter is set default value as NULL. An explicit value will have to be passed to the new parameter. The same is true when you delete a parameter from the user-defined function. If the necessary changes are not done in calling of the function then it leads to an error.
Now since I added a parameter to the existing user-defined function, I wanted to find out the names of the stored procedures / functions wherever the modified function is called. So that I can pass an additional value to the new parameter added. Hence the procedure is created.
Solution: The UP_FindAWordUsedInSPOrFun is created which takes a single parameter @FindStr. It generates a list of procedures and functions wherever the matching string is found in any of the procedures or functions.
A function is initially created as
Create function UF_Function1 (Parameter1 Integer) ……
A procedure is created as
Create PROC UP_Proc1(…)
Select * from DBO.UF_Function1(2)
A function is CHANGED with an additional parameter as
Create function UF_Function1 (Parameter1 Integer, Paramter2 Integer) ……
Assuming you do not know the name of the procedure where you have used the function,
Executing a UP_FindAWordUsedInSPOrFun :
EXEC UP_FindAWordUsedInSPOrFun 'UF_Function1’
The output of the procedure would be as
One can also use the procedure to find any particular string that is used in any stored procedure or function.