To the best of my knowledge, no we cannot have a single (i.e. combined) output. Here's the reason:

The query that is being executed is a general, parameterized query. SQL Server tries to parameterize all queries (if not already parameterized) so that it allows query plan re-use.

The parameter values on the other hand, are specific to that particular instance of the query execution.

When one executes the same query with a different set of parameter values, SQL server does not re-generate the plan again - it simply re-uses the already available plan for the parameterized version of the query.

Here's an example for your better understanding. Do let me know if you have any further questions. Also, please do not forget to mark this reply as "answer" if I was able to help you in resolving your query.

/* WARNING */
/*
This query is provided "as-is" and without warranty.
The query is provided for demonstration purposes only.

PLEASE RUN THIS ON A DEVELOPMENT ENVIRONMENT ONLY.
EXECUTING THIS ON A QA/PRODUCTION ENVIRONMENT MAY RESULT IN SERIOUS PERFORMANCE COMPLICATIONS.
*/
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

USE AdventureWorks2008R2
GO
DECLARE @sqlstring NVARCHAR(100)
DECLARE @paramDefinition NVARCHAR(50)
DECLARE @paramValue BIT

SET @sqlstring = N'SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1'
SET @paramDefinition = N'@p1 BIT'
SET @paramValue = 1

EXEC sp_executesql @sqlstring, @paramDefinition, @p1 = @paramValue

--Check if the SQL Server is re-using the query plan
-- USECOUNTS value should be 1
SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
    	text like '%SELECT * FROM HumanResources.Employee%'
ORDER BY usecounts DESC;

/********* RESULTS ****************/
/* (Compressed for sake of brevity)
usecounts   cacheobjtype	objtype	     text
  1         Compiled Plan	Prepared	(@p1 BIT)SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1
  1         Compiled Plan	Adhoc	    DECLARE @sqlstring ...............
*/

--Execute the query again, but with a different value
SET @paramValue = 0
EXEC sp_executesql @sqlstring, @paramDefinition, @p1 = @paramValue

--Check if the SQL Server is re-using the query plan
-- USECOUNTS value should be 2
SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
    	text like '%SELECT * FROM HumanResources.Employee%'
ORDER BY usecounts DESC;

/********* RESULTS ****************/
/* (Compressed for sake of brevity)
usecounts   cacheobjtype	objtype	     text
  2         Compiled Plan	Prepared	(@p1 BIT)SELECT * FROM HumanResources.Employee WHERE SalariedFlag = @p1
  1         Compiled Plan	Adhoc	    DECLARE @sqlstring ...............
*/
GO

Thank you. It was very explanative.

Hi Nakul, Thanks for your explaination. I have a queation on similar topic. I am trying to capture the query execution time form ASP.NET web applicaiton. To do this, I ran the SQL profiler to capture the database events. Now I can see there are total 12 SP:StmtCompleted with 12, 27,1,1,0...0( rest all zero) duration(all in microsecodes) after these, there is one RPC completed with duration 239. This RPC is executing all the avove 12 SQL statements(writen in SP:StmtCompleted) with parameterized value.

Quesiton: 1. Should I sum the duraitons of all SP:StmtCompleted with RPC:completed statement to find the exact executaion time? 2. From applicaiton we have used CommandParameter as Text, but we have not explicitly issued any RPC call. Who created RPC call? 3. Is there anyway to know if the stored execution plan was reused during RPC call?

Thanks for your help, Dipankar