CODEWITHSUNDEEP
sql-serverstored-procedures
xtreme_user
xtreme_user

Reputation: 11

Function in SQL Server knowing which stored procedure called it

On SQL Server 2005+, if a user-defined function is called from a stored procedure, is there a way for the function to get the name of the stored procedure that called it?

This needs to be without the stored procedure passing a parameter to the function with its name.

Any help I'd be very grateful thanks

Upvotes: 1

Views: 59

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10277

More information is needed to give a specific answer, but here are some options that will help.

To find which stored procedures call your function:

SELECT name
FROM sys.procedures
WHERE object_definition(object_id) LIKE '%yourUDFname%'

Then you could use sys.dm_exec_procedure_stats to find the last execution time of those SPs:

SELECT name, last_execution_time
FROM sys.procedures p
JOIN sys.dm_exec_procedure_stats s on p.object_id = s.object_id
WHERE object_definition(p.object_id) LIKE '%yourUDFname%'  
--ORDER BY last_execution_time DESC

If you need more than that, the most thorough but most time consuming option is to add logging within the SPs that writes a log when they call your UDF.

Upvotes: 1

podiluska
podiluska

Reputation: 51494

Without passing parameters, or passing the value in some other way, for instance, by storing it in a table, and passing a unique key to that value, this is not possible.

Upvotes: 1

Related Questions