Studi kasus 2 SQL Server (Menggunakan Stored Procedure)

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)