Cook with dotNet Technology: CURSOR and Temporary Table on Microsoft SQL Server

Prepare the Ingredients
We have four tables named food_category, food, orders, and orders_detail. The Structure of the tables :

food table

No.	Table Name	Data Type	Desc.
1	food_id	INT	PK, Autoincrement
2	food_name	VARCHAR(255)	-
3	food_description	VARCHAR(255)	-
4	food_status	BIT	-
5	category_id	INT	FK

food_category table

No.	Table Name	Data Type	Desc.
1	category_id	INT	PK, Autoincrement
2	category_name	VARCHAR(100)	-
3	category_description	VARCHAR(255)	-
4	category_status	BIT	-

orders table

No.	Table Name	Data Type	Desc.
1	order_id	INT	PK, Autoincrement
2	order_date	DATE	-
3	order_description	VARCHAR(255)	-
4	order_status	BIT	-

orders_detail table

No.	Table Name	Data Type	Desc.
1	order_detail_id	INT	PK, Autoincrement
2	order_id	INT	FK
3	food_id	INT	FK
4	amount	INT	-

Here is the relationship between food, food_category, orders, and orders_detail table :

We want to get a list of foods with minimum sales of [X] item.

Let's Cook, Bro !
1. Run your Visual Studio. I’m using Visual Studio 2010 and Microsoft SQL Server 2008 as DataSource
2. Create a New Web Site > ASP.NET Empty Web Site > Give the name of Food : menu02_cursortemptable
3. Add New Item > Web Form > Default.aspx
4. Create a simple page like this :

5. Create a New Stored Procedure, write this query, and execute the query :

USE [menu02]
GO
/****** Object: StoredProcedure [dbo].[sp_view_food_order] Script Date: 01/26/2015 13:43:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Yudha Permana
-- Create date: 2015-01-22
-- Description: Cursor and Temporary Table
-- =============================================
ALTER PROCEDURE [dbo].[sp_view_food_order]
    -- Add the parameters for the stored procedure here
    @minimum_amount AS INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Decalare Temporary Variable to Store food_id from CURSOR
    DECLARE @get_food_id INT

    -- Declare Temporary Table
    DECLARE @TempTable TABLE(col_food_id INT,
                col_food_name VARCHAR(255),
                col_food_description VARCHAR(255))

    -- Declare CURSOR to Get the List of food_id (Selected Category and All Orders)
    DECLARE cursor_food_id CURSOR
    STATIC FOR
        SELECT DISTINCT(food_id)
        FROM orders_detail
        WHERE amount > @minimum_amount

    -- Open the CURSOR
    OPEN cursor_food_id
    -- If Data is Available
    IF @@CURSOR_ROWS > 0
    BEGIN
      FETCH NEXT FROM cursor_food_id INTO @get_food_id
      WHILE @@FETCH_STATUS = 0
      BEGIN
          -- Store the Value on Temporary Table
          INSERT INTO @TempTable(col_food_id, col_food_name,
                    col_food_description)
          SELECT food_id, food_name, food_description
          FROM food
          WHERE food_id = @get_food_id

          FETCH NEXT FROM cursor_food_id INTO @get_food_id
      END
    END

    -- Close the CURSOR
    CLOSE cursor_food_id
    DEALLOCATE cursor_food_id

    SET NOCOUNT OFF

    -- View the Result of Temporary Table
    SELECT * FROM @TempTable;
END

6. Back to Visual Studio 2010, Click Gridview, Configure Data Source...