Download script for this stored procedure:
sql-server-cursors.zip(It shall be easier to read if you open this file in Microsoft SQL Server)
Example Output:
--- Product Category: Meat --
Product: Susages
Product: Pork
--- Product Category: Bread --
Product: Soft White Bread
Product: Submarine white rolls
PRINT 'CREATE Stored Procedure: sp_get_products'
GO
/************************************************************
// Author: Ahsan Khan
// Web: www.chapterzero.co.uk
// Created on: 05 May, 2006
// Modified: 06 October, 2006
// Description: Returns a list of product types with all the products for that product
// type. See the bottom of this document for output generated by this
// stored procedure.
//
// Please let us know if you find any errors or if you want to send us the improved
// version of this sql script.
// Email: ahsan@chapterzero.co.uk
//
/************************************************************/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_get_products' AND type = 'P')
BEGIN
DROP PROCEDURE sp_get_products
END
GO
CREATE PROCEDURE sp_get_products
-- @info values
-- type = product type
-- product = product name
AS
BEGIN
SET NOCOUNT ON
-- DECLARE VARIABLES
DECLARE @product_type_guid uniqueidentifier,
@product_type varchar(128)
@product_guid uniqueidentifier,
@product_name varchar(128),
@price decimal
-- RESULTS TABLE WHICH WILL BE RETURNED
CREATE TABLE #products
(
info varchar(10),
[guid] uniqueidentifier,
product_name varchar(128),
price decimal,
)
-- PRODUCT TYPES CURSOR
DECLARE product_types_cursor CURSOR FOR
SELECT pt_type_guid, pt_product_type
FROM product_types
ORDER BY pt_product_type
-- OPEN PRODUCT TYPES CURSOR
OPEN product_types_cursor
FETCH NEXT FROM product_types_cursor
INTO @product_type_guid, @product_type
-- FETECH ALL PRODUCT TYPES
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #products
(
[info],
[guid],
product_name,
price
)
VALUES
(
'type',
@product_type_guid,
@product_type,
NULL
)
-- DECLARE PRODUCT ITEMS CURSORS
DECLARE products_cursor CURSOR FOR
SELECT [pd_guid]
,[pd_product_name]
,[pd_price]
FROM products_list pl
WHERE (pd_product_type = @product_type_guid)
OPEN products_cursor
FETCH NEXT FROM products_cursor
INTO @product_guid,
@product_name,
@price
-- LOOP THROUGH PRODUCT ITEMS
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #products
(
info,
[guid],
product_name,
price
)
VALUES
(
'product',
@product_guid,
@product_name,
@price
)
FETCH NEXT FROM products_cursor
INTO @product_guid,
@product_name,
@price
END
-- CLOSE INNER CURSOR i.e. products_list cursor
CLOSE products_cursor
DEALLOCATE products_cursor
-- GET NEXT FROM THE PRODUCTS TYPES TABLE
FETCH NEXT FROM product_types_cursor
INTO @product_type_guid, @product_type
END
-- CLOSE PRODUCT TYPES CURSOR
CLOSE product_types_cursor
DEALLOCATE product_types_cursor
-- RETURN PRODUCTS
SELECT * FROM #products
-- DROP RESULTS TABLE
DROP TABLE #products
RETURN(0)
END
GO
GRANT EXECUTE ON sp_get_products TO Public
GO
---------------------------------------------------------------------
/*
.. DATA IN THE TABLES ..
Table name: product_types
pt_type_guid pt_product_type
ae102e63-a9f7-418b-1001-e7728473f301 Meat
ae102e63-a9f7-418b-1001-e7728473f302 Bread & Rolls
Table name: products_list
pd_guid pd_product_name pd_price pd_product_type
ae102e63-a9f7-418b-1001-e7728473aaaa Beef Fillet Steak 1.40 ae102e63-a9f7-418b-1001-e7728473f301
da4c4120-d153-4d37-9ddd-db4a909cff4a Lamb Chops 4-6 (approx. 400g) 10.99 ae102e63-a9f7-418b-1001-e7728473f301
1ce8003a-b517-442a-aa3d-3cf02534b771 Soft White Bread 1.29 ae102e63-a9f7-418b-1001-e7728473f302
b1e02735-0338-41df-8da8-408259ce7075 Burger Buns 0.63 ae102e63-a9f7-418b-1001-e7728473f302
b3779920-b2f0-4c14-b20f-4c77ae33ef8d BBQ Pork Ribs 1.55 ae102e63-a9f7-418b-1001-e7728473f301
94652435-2f18-42ff-9762-614ea3a9aeba Mixed BBQ (approx. 500g) 3.89 ae102e63-a9f7-418b-1001-e7728473f301
ab178e51-b93f-43dc-8c52-61f8e9302c93 Submarine White Rolls x 2 0.66 ae102e63-a9f7-418b-1001-e7728473f302
.. OUTPUT FOR DISPLAYING ON THE WEBPAGE ..
------- Breads ---------
Soft White Bread £1.29
Burger Buns £0.63
Submarine White Rolls x 2 £0.66
------- Meat -----------
Beef Fillet Steak £1.40
Lamb Chops 4-6 (approx. 400g) £10.99
BBQ Pork Ribs £1.55
Mixed BBQ (approx. 500g) £3.89
*/