Pada studi kasus kedua SQL Server, kita akan bahas mengenai stored procedure. Studi kasus ini masih ada hubungannya dengan materi sebelum, yaitu di Studi kasus 1 SQL Server (Menggunakan Query)
Langsung saja ke studi kasusnya ya.
Mari kita buat stored procedure sederhana yang menampilkan total penjualan dari 2 tabel. Lihat contoh di bawah ini :
CREATE PROCEDURE GetSalesItem
AS
BEGIN
SET NOCOUNT ON
SELECT P.item_id,Q.item_name,sum(P.sales_grandtotal) as sales_grandtotal
FROM
tbl_sales P
INNER JOIN tbl_item Q on P.item_id=Q.item_id
GROUP BY P.item_id,Q.item_name
END
Kita dapat menggunakan "EXEC ProcedureName" untuk menjalankan stored procedures.
Saat kita jalankan stored procedures di atas, maka hasilnya akan terlihat seperti di bawah ini
Membuat stored procedure dengan parameter
Mari kita buat stored procedures SQL Server menggunakan parameter. Ikuti contoh di bawah ini.
CREATE PROCEDURE GetSalesItem_withparameters
(@itemid INT)
AS
BEGIN
SET NOCOUNT ON
SELECT P.item_id,Q.item_name,sum(P.sales_grandtotal) as sales_grandtotal
FROM
tbl_sales P
INNER JOIN tbl_item Q on P.item_id=Q.item_id
WHERE P.item_id=@itemid
GROUP BY P.item_id,Q.item_name
END
EXEC GetSalesItem_withparameters 3
Saat menjalankan stored procedure, kita harus memasukan nilai parameternya. Contoh nilai parameter yang dimasukkan disini adalah 3 (Cuci Komplit 1 Hari Jadi). Mari kita lihat gambar berikut
Membuat stored procedure dengan nilai default parameter
Berikut contoh stored procedure dengan nilai default parameter
CREATE PROCEDURE GetSalesItem_withdefaultparameters
(@itemid INT = 1)
AS
BEGIN
SET NOCOUNT ON
SELECT P.item_id,Q.item_name,sum(P.sales_grandtotal) as sales_grandtotal
FROM
tbl_sales P
INNER JOIN tbl_item Q on P.item_id=Q.item_id
WHERE P.item_id=@itemid
GROUP BY P.item_id,Q.item_name
END
Saat kita menjalankan stored procedure di atas tanpa memasukan nilai parameter, nilai default yang akan digunakan adalah 1. Tetapi saat kita menjalankan stored procedure di atas dengan memasukan nilai parameter, nilai default akan diabaikan dan akan menjalankan nilai parameter yang kita masukan.
Membuat stored procedure dengan output parameter
Berikut adalah contoh stored procedure yang menggunakan output parameter. Output yang dihasilkan adalah nilai user_id dari auto identity column saat data baru dimasukan.
CREATE PROCEDURE ins_NewUser_with_outputparamaters
(@Eusername nchar(10),
@Epass nchar(10),
@Estat nchar(10),
@Esetting int,
@EId int output)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO tbl_user (username,password,stat,setting_id) VALUES (@Eusername,@Epass,@Estat,@Esetting)
SELECT @EId= SCOPE_IDENTITY()
END
Menjalankan stored procedures menggunakan output parameter sedikit berbeda. Kita harus mendeklarasikan variabel untuk menyimpan nilai balikan dari output parameter.
declare @UserID INT
EXEC ins_NewUser_with_outputparamaters 'sugismart','sugismart123',1,1, @UserID OUTPUT
SELECT @UserID
SELECT * from tbl_user
Selamat, kamu telah menyelesaikan Studi kasus 2 SQL Server (Menggunakan Stored Procedure)
