My internet queries are not the best and I only get how to execute a stored procedure when I query.
Anyway, how do I tell what called a stored procedure? I know that OBJECT_NAME(@@PROCID) will return the stored procedure name however I want what called it. Is this possible?
No, there's no direct way to do that in SQL Server. You'd have to pass in the caller name/id yourself somehow: a parameter, a table (temp or not), CONTEXT_INFO(), or whatever other method.
You could run a trace or profiler (with filters if required to eliminate noise) and look at the various columns in the profiler output - such as Application Name, NTUserName, Login Name etc. to get an idea of what/where the calls are coming from. What you see may depend on how the caller is connecting. For example, the Application Name is something a caller can set in the connection string if they choose to. If they have not set it, it might return you something generic.
If you need to capture/store that information in a production environment regularly, the stored proc would have to pass in the caller name as a parameter.
I thought of one other possible way to pass in the calling app -- setting application name when making the SQL connection and then using APP_NAME() within the proc.
We actually do that for some of our processes. However, this is one that we did not, and since I do not know what is calling it, I cannot add to the connection.
All (as in "so close to zero exceptions as makes no odds") of our SProcs start with a SessionID value. Each SProc logs the SessionID and (a concatenated list of) the values of all its parameters.
Makes solving this type of thing a lot easier. Our APPs are all Web Based, so no opportunity to walk round and look over a user's shoulder! and we proactively review logged data for sessions which have encountered an error - we find a lot of good stuff in there Even in a closed-shop organisation users are inclined to put-up-with issues, rather than reporting them, and then the APP just gets negative-PR ("B****dy thing doesn't work")