How to debug/troubleshoot a SP in production environment?

There are times when you need to debug

some stored procedure(s) in Production

environment or to make a SP to behave

differently when you execute it via the SSMS

versus case when the SP executed by an application.

Or to make SP to behave differently from requests

that come from different version of an application

or requests that come from specific IPs and etc.

Or you may want to know what values were

provided for the input parameters of the SP?

Here some usefull SQL functions that you can use in the SPs to achieve the above:

1) APP_NAME() - Returns the application name for the current session if set by
   the application. You can set it in the connection string of the application by
   including this: "Application Name=AppName".
   By using this function you can change a SP to do X if it executed from Server A
   and to do Y if it executed by Server Y or if it executed via the SSMS.
   Simply set the application name for different value in Server X and Y.
   By using this function you can also upload SPs changes without any downtime
   and with full backward compatibility.

2) CONNECTIONPROPERTY('client_net_address') - Returns the Client IP address.

3) CONNECTIONPROPERTY('local_net_address')  - Returns the IP address of the server.
   For example, if you connect to production SQL server from the SSMS in QA/DEV
   SQL Server then it will return the IP of that QA/DEV SQL Server machine.

By using these functions listed above you can add logic (If or CASE statements)
to the SP and you can troubleshoot/debug the SP without running the SQL Profiler.

SELECT APP_NAME() AS Application_Name,
               CONNECTIONPROPERTY('local_net_address') AS local_net_address,
               CONNECTIONPROPERTY('client_net_address') AS client_net_address
GO