/******************************************************************************
 * Name:        PR_LENGTHY_PROCEDURE 
 *
 * Description: Perform a lengthy operation by inserting a given number of
 *              records into a temporary table.
 *
 * Author:      Hans De Schrijver
 *
 * Input:       @Iterations (bigint)
 *              The number of records to insert into the temporary table.
 *              Default value is 10000.
 *
 * Output:      Returns a recordset with the content of the temporary table
 *              at the end of the procedure.
 *
 *****************************************************************************/
CREATE PROCEDURE PR_LENGTHY_PROCEDURE
(
    @Iterations bigint = 10000
)
AS

SET NOCOUNT ON

-- Drop the temporary table if it already exists 
-- (most likely a leftover from a previously cancelled procedure call).
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
    DROP TABLE #MyTempTable

-- Create a temporary table in which we will insert a given number of records
-- in order to simulate a lenghty operation.
CREATE TABLE #MyTempTable(record_id bigint IDENTITY(1,1), record_description varchar(200))


-- Create and initialize the loop counter.
DECLARE @loopcounter bigint
SET @loopcounter = 0

-- Start the process of inserting records.
WHILE @loopcounter < @Iterations
BEGIN
    -- Insert a new record in the temporary table.
    SET @loopcounter = (@loopcounter + 1)
    INSERT #MyTempTable (record_description) VALUES ('This is Record #' + CONVERT(VARCHAR(20), @loopcounter))
END

-- Display the results from the temporary table.
SELECT * FROM #MyTempTable

-- Delete the temporary table.
DROP TABLE #MyTempTable

GO