How to check parameters values used for compiling the stored procedure?

It is a known fact that the initial plan of the stored procedure is created with its first execution. SQL Server engines creates the plan based on the values passed for parameters with stored procedure's first execution and places the plan in the cache. This plan will be reused with consecutive executions regardless of the values passed to parameters. This behavior is called as Parameter Sniffing, and it helps to improve the overall performance of the stored procedure's execution as well as downgrade the performance of it. For more info on this, read one of my old post: Do we need to recompile Stored Procedures when an index is added or parameter value is vary greatly?

With consecutive executions, if you want to see the values that have been used for compiling the procedure, or in other words, parameters values used for the plan in the cache, what is the easiest way of seeing it? Simplest way is, get the Properties of last component in the plan and see Parameter List property. Here is the way of doing it;

First, let's create a procedure and execute once.

USE AdventureWorks2014;
GO

-- Drop if it is exist
IF OBJECT_ID('dbo.GetOrderDetails', 'P') IS NOT NULL
 DROP PROC dbo.GetOrderDetails;
GO

-- Create the procedure
CREATE PROC dbo.GetOrderDetails @Number int
AS
    SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID <= @Number
GO

-- Execute once. This adds the plan which is optimized for 
-- parameter value 1.
EXEC dbo.GetOrderDetails 1;

Now let's execute it with a different value enabling Include Actual Execution Plan.

Open the Execution Plan tab and click on SELECT.

Properties window can be opened by either pressing F4 or Right-Clicking and Selecting Properties. Once it is opened, You will see property called Parameter List and it has the values used for compiling the stored procedure and values used for current execution.

Is this useful? Yes, if you are planning to recompile or want to know whether the plan is optimized for new executions, this holds some of required information you need on it.