Cara Membuat SQL Stored Procedure dan Contohnya

Pernah buat sebuah script SQL untuk mengambil data tertentu dan kamu pakai berulang kali? Nah, bagaimana cara supaya script tersebut bisa dipakai berulang kali dan secara otomatis berjalan dengan sendirinya tanpa harus mengeksekusi satu-satu? 

Maka kamu membutuhkan stored procedure. Tapi apa itu stored procedure? Mari kita bahas di artikel ini.

SQL Stored Procedure, Apa Itu?

Stored procedure merupakan sebuah fungsi dari SQL untuk menyimpan syntax SQL dan dapat digunakan berulang kali tanpa mengubah isi dari script tersebut. Biasanya, stored procedure digunakan untuk membuat sebuah tabel atau database yang digunakan berulang kali.  

Jadi, di dalam stored procedure ini kamu bisa menyimpan banyak query dan menjalankannya secara bersamaan dalam satu kali eksekusi.

Contoh dari penggunaan stored procedure adalah ketika seorang data analyst ingin membuat sebuah database dari beberapa tabel. Tetapi tabel tersebut merupakan sebuah tabel yang dibentuk dengan menggunakan script SQL. 

Maka, untuk menghemat waktu sembari dia mengerjakan pekerjaan lain, ia membuat sebuah stored procedure yang berisi script pembuatan tabel-tabel yang dibutuhkan untuk menyusun database tersebut.

Syntax SQL untuk Membuat Stored Procedure

Dalam membuat sebuah stored procedure, kamu harus mempersiapkan dulu statement SQL yang akan digunakan. Nantinya, stored procedure ini akan menjalankan statement SQL tersebut. Secara sederhana, stored procedure dapat dibentuk dalam syntax seperti berikut ini:

CREATE PROCEDURE procedure_name
AS
BEGIN
sql statement
END;

Setiap pembuatan stored procedure selalu diawali dengan command BEGIN dan END. BEGIN menunjukkan dimulainya pekerjaan dari stored procedure dan END menunjukkan akhir dari pekerjaan stored procedure. 

Syntax ini cukup dijalankan satu kali saja, karena syntax ini bertujuan untuk membuat dan menyimpan stored procedure. Kemudian, sebagai perintah untuk menjalankan stored procedure, cukup menggunakan command EXEC seperti pada contoh di bawah ini.

EXEC procedure_name

Dalam pembuatan stored procedure, dapat juga menggunakan parameter-parameter yang sudah ditentukan, seperti tanggal atau ID dari sebuah produk. Dengan menentukan parameter tersebut, stored procedure bisa diatur untuk secara otomatis mengambil data berdasarkan parameter tersebut. Atau kamu yang menentukan data yang diambil berdasarkan parameter pada saat menggunakan command EXEC. 

Untuk syntax-nya adalah seperti berikut.

CREATE PROCEDURE procedure_name
	@parameter1 data type,
	@parameter2 data type,
…
AS
BEGIN
	sql_ statement
END;

Apabila ada penciptaan, maka ada juga penghapusan. Untuk melakukan penghapusan stored procedure dapat menggunakan syntax berikut ini.

DROP procedure_name;

Contoh Pembuatan Stored Procedure

Contoh 1

Untuk contoh yang pertama, kita akan membuat sebuah stored procedure sederhana yang tidak menggunakan parameter. Pada bagian SQL statement, kita hanya perlu menuliskan SELECT * FROM customers karena kita akan mengambil semua data yang ada di tabel customers.

CREATE PROCEDURE get_customers
AS
BEGIN
SELECT * FROM customers;
END

‍

Kemudian pada command EXEC, dapat dibuat seperti berikut ini.

EXEC get customers;

Contoh 2

Dalam contoh selanjutnya, stored procedure bernama 'get_customers_by_country' dibuat dengan satu parameter 'country' yang digunakan untuk memfilter hasil pencarian data pada tabel 'customers'. Ketika stored procedure ini dipanggil, kamu dapat memberikan nilai yang berbeda untuk parameter 'country' dan stored procedure akan mengembalikan semua data pelanggan yang sesuai dengan kondisi tersebut.

CREATE PROCEDURE get_ customers_by_country
	@country nvarchar(50)
AS
BEGIN
	SELECT * FROM customers WHERE country = @country;
END;

Berikut adalah cara memanggil stored procedure dengan parameter di atas:

EXEC get_ customers_by_country @country = 'USA';

Dalam contoh di atas, stored procedure 'get_customers_by_country' dipanggil dengan memberikan nilai 'USA' pada parameter 'country'. Stored procedure akan mengembalikan semua data pelanggan dari 'USA'.

Contoh 3

Dalam contoh di bawah, stored procedure bernama 'get_orders_by_date_range' dibuat dengan dua parameter 'start_date' dan 'end_date' yang digunakan untuk memfilter hasil pencarian data pada tabel 'orders' berdasarkan tanggal order. 

Ketika stored procedure ini dipanggil, kamu dapat memberikan nilai yang berbeda untuk parameter 'start_date' dan 'end_date', dan stored procedure akan mengembalikan semua data order yang terjadi pada rentang waktu tersebut.

CREATE PROCEDURE get_orders _by_ date_range
@start_date date,
@end_date date
AS
BEGIN
SELECT * FROM orders WHERE order_date BETWEEN @start_date AND @end_date;
END;

Berikut adalah cara memanggil stored procedure dengan parameter tanggal di atas:

EXEC get _orders _by_ date_range 
@start_date = '2022-01-01', @end _date = '2022-12-31’;

Dalam contoh di atas, stored procedure 'get_orders_by_date_range' dipanggil dengan memberikan nilai '2022-01-01' pada parameter 'start_date' dan '2022-12-31' pada parameter 'end_date'. Stored procedure akan mengembalikan semua data order yang terjadi antara 1 Januari 2022 dan 31 Desember 2022.

Penutup

Stored procedure dapat digunakan apabila kamu mempunyai pekerjaan yang menggunakan script SQL yang sama untuk berulang kali atau perlu untuk melakukan otomasi. 

Kamu juga bisa menambahkan parameter-parameter untuk stored procedure kamu sehingga data yang dihasilkan sesuai dengan keinginan yang kamu ingin ambil.