Results from cursor into temp table with number of rows and execution time

Question

There is need to fetch 100 IDs from Employee table, using cursor, then to execute stored procedure and to put number of rows and execution time for each ID into one temp table. Some idea, how to count number of rows which stored procedure will catch and execution time for that ?

declare @temptable table
( ID nvarchar
 , numberOfRows int
 , executionTime int)

declare @id nvarchar(15)

declare db_cursor CURSOR FOR
select top 100 NationalIdNumber
from HumanResources.Employee

open db_cursor
fetch next from db_cursor into @id

while @@fetch_status = 0

begin

insert into @temptable

exec [dbo].[uspEmployeeData] @id

fetch next from db_cursor into @id

end

close db_cursor
deallocate db_cursor

I am using SQL Server 2014 Standard edition

Answer 1

You can do something like:

declare @tableresults TABLE (@id INT, row_count INT, durationms int)

DECLARE @starttime DATETIME

open db_cursor
fetch next from db_cursor into @id

while @@fetch_status = 0

begin

    SET @starttime = GETDATE()

    insert into @temptable
    exec [dbo].[uspEmployeeData] @id

    INSERT INTO @tableresults
    VALUES (@id, @@rowcount, DATEDIFF(millisecond, @starttime, GETDATE() )

    fetch next from db_cursor into @id
end