1. Definisi
Store procedure adalah kumpulan dari statement SQL yang digunakan bersama sama. Store procedure mengijinkan penggunaan statemen SQL lebih dari sekedar
statemen SQL biasa untuk meretrieve dan mengupdate database.Store procedure mendukung pemakaian variabel, flow control dan cursor.
2. Sintaks SQL
Sintaks SQL untuk membuat procedure :
CREATE PROCEDURE procedure_name
@param data_type = default_value,
@param data_type = default_value,
@param data_type = default_value
AS
-- statements for procedure here
Sintaks SQL untuk menghapus procedure :
DROP PROCEDURE stored_procedure_name
Berikut contoh penggunaan sintaks procedure :
CREATE PROCEDURE SalesByCategory
@CategoryName nvarchar(15),
@OrdYear int = 1998
AS
SELECT ProductName, SUM(OD.Quantity * (1-OD.Discount) * OD.UnitPrice) AS TotalPurchase
FROM "Order Details" od, Orders o, Products p, Categories c
WHERE od.OrderID = o.OrderID
AND od.ProductID = p.ProductID
AND p.CategoryID = c.CategoryID
AND c.CategoryName = @CategoryName
AND DATEPART(year,OrderDate) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
Sintaks SQL untuk membuat procedure :
CREATE PROCEDURE procedure_name
@param data_type = default_value,
@param data_type = default_value,
@param data_type = default_value
AS
-- statements for procedure here
Sintaks SQL untuk menghapus procedure :
DROP PROCEDURE stored_procedure_name
Berikut contoh penggunaan sintaks procedure :
CREATE PROCEDURE SalesByCategory
@CategoryName nvarchar(15),
@OrdYear int = 1998
AS
SELECT ProductName, SUM(OD.Quantity * (1-OD.Discount) * OD.UnitPrice) AS TotalPurchase
FROM "Order Details" od, Orders o, Products p, Categories c
WHERE od.OrderID = o.OrderID
AND od.ProductID = p.ProductID
AND p.CategoryID = c.CategoryID
AND c.CategoryName = @CategoryName
AND DATEPART(year,OrderDate) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
Langganan:
Posting Komentar (Atom)
0 komentar:
Posting Komentar