Home » SQL Server » Membuat Stored Procedure dan Function di SQL Server
Posted at January 6th, 2021 | Categorised in SQL Server
Buat sebagian orang bahkan termasuk saya, Stored Procedure atau SP dan Function wajib dimiliki saat membangun aplikasi yang menggunakan database. Karena saya sendiri lebih suka menyimpan logic di dalam stored procedure dan function-function dapat dibuat dan dipanggil di dalam stored procedure untuk mempermudah dalam proses develop. Sebelum kita masuk lebih dalam, sudahkah tahu apa itu Stored Procedure atau Function di SQL Server?
- Stored Procedure atau SP
Pengertian mudah dari Stored Procedure adalah kumpulan baris perintah dalam sintaks SQL. Jadi semua logic programming selain dibuat menggunakan bahasa programming seperti C#, Java, PHP, Phyton dll, dapat juga di buat menggunakan sintaks SQL. Stored Procedure dapat menerima input parameter dan juga mengeluarkan output parameter. Ingat ya, stored procedure itu menempel di database, baik itu SQL Server, MySQL, PostgreSQl, Oracle dll.
Jika memang dapat dibuat di dalam aplikasi atau bahasa programming, mengapa harus di buat di Stored Procedure? Berikut ini adalah beberapa keuntungan yang bisa kita dapatkan jika menggunakan Stored Procedure:
- Lebih Cepat : Stored Procedure lebih cepat dalam memproses query database
- Irit Bandwith : Jika proses dilakukan di dalam stored procedure, maka dapat mengurangi lalu lintas data, contoh kamu akan memproses data dari table yang memiliki jutaan row, tapi output yang dibutuhkan hanya 1 data saja, maka jika dilakukan di apps akan semakin berat, karena harus menarik data dari table yang berisi jutaan rows tersebut baru di proses di apps.
- Lebih Aman : Tidak ada yang aman 100% dalam security, tapi Stored Procedure tetap lebih aman dibandingkan menggunakan cara standar dalam memproses data langsung dari apps.
- Mudah Penggunaan dan Reusable : Membuat stored procedure di SQL Server sangat mudah, dan SP yang sudah dibuat juga dapat dipanggil dari SP yang lain
Di atas adalah beberapa keuntungan menggunakan Stored Procedure yang saya rasakan, dan tentunya ini sifatnya subjektif saya sendiri, jika ada yang punya sanggahan infokan saja di kolom komentar supaya saya juga dapat ilmu baru.
Sekarang kita langsung melihat, seperti apa cara membuat Stored Procedure di SQL Server. Owh ya, sekedar info bahwa saya membuat Stored Procedure menggunakan SQL Server 2017 Developer Edition.
Pilih Database –> Programmability –> Stored Procedure –> Klik Kanan dan pilih New untuk membuat Stored Procedure baru. Atau kamu juga langsung dapat menuliskan script untuk membuat stored procedure
Create Procedure [Nama Stored Procedure] as begin -- Logic sintaks TSQL end
Script di atas adalah script untuk membuat stored procedure/SP tanpa harus melalui UI SQL Server. Untuk lebih jelasnya lihat script SQL dibawah ini. Berikut ini adalah contoh Stored Procedure sederhana untuk select data dari table.
-- ================================================ -- Template generated from Template Explorer using: -- Create Procedure [New Menu].SQL -- -- Use the Specify Values for Template Parameters -- command [Ctrl-Shift-M] to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the procedure. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE dbo.UspGetDataUser -- Add the parameters for the stored procedure here @UserId varchar[20], @FullName varchar[100], @Alamat varchar[500], @Email varchar[50] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select @UserId = ISNULL[@UserId,''] select @FullName = ISNULL[@FullName,''] select @Alamat = ISNULL[@Alamat,''] select @Email = ISNULL[@Email,''] SELECT * from dbo.DataUser d where d.UserId like '%'+@UserId+'%' and d.FullName like '%'+@FullName+'%' and d.Alamat like '%'+d.Alamat+'%' and d.Email like '%'+@Email+'%' END GO
2. Function
Sama seperti Stored Procedure, Function di SQL server juga berisi perintah-perintah atau sintaks SQL. dapat di isi dengan berbagai logic yang ada. Perbedaan mendasar antara Stored Procedure dengan function adalah:
* Cara penggunaannya
* Ada beberapa hal yang tidak dapat dilakukan di function tetapi bisa di dalam SP seperti membuat table temporary [#tempTable]
* Function dapat di panggil dari Stored Procedure, tetapi tidak sebaliknya.
Oke langsung saja kita buat contoh sederhana function di SQL Server
Pilih Database –> Programmability –> Functions –> Pilih di antara 2 pilihan di bawah:
1. Table-valued Functions : Untuk menghasilkan output dalam bentuk table.
2. Scalar-valued Functions : Untuk menghasilkan single result.
Create FUNCTION [dbo].[dbmoneyformat][@passedval float] RETURNS varchar[50] as begin declare @data varchar[50] select @data = REPLACE[CONVERT[varchar[50], [CAST[@passedval AS money]], 1], '.00', ''] return @data end
Contoh function di atas adalah function untuk memberikan hasil convert to money yang artinya menggunakan function tipe Scalar-valued Function.
Sekarang mari kita lihat dari function dan SP di atas, bagaimana cara pakainya
cukup menggunakan select kemudian sebutkan schema, di sini saya menggunakan schema default yaitu dbo dan nama functionya. Hasilnya seperti gambar di atas, berfungsi memberikan hasil convert ke dalam bentuk uang.
Sedangkan untuk menggunakan Stored Procedure, maka gunakan perintah Exec namaSP beserta parameter2 yang terdapat di dalam SP tersebut, seperti gambar di atas. Sintaks di atas berfungsi untuk mengambil data dengan pencarian fullname yang memiliki karakter test.
Demikian penjelasan singkat, cara membuat dan memakai dari Stored Procedure beserta Function di SQL Server. Jika masih bingung jangan ragu untuk bertanya, biar saya juga belajar kalo ada hal-hal yang baru yang belum saya tahu.
Related Post to Membuat Stored Procedure dan Function di SQL Server
Langkah Membuat Linked Server ke Database PostgreSQL
Posted at 24/01/2022
Buat yang masih bingung bagaimana cara membuat linked server ke database PostgreSQL, kamu bisa menemukan langkah-langkahnya disini karena saya akan berbagi informasi terkait cara... Read More
Fixing Gagal Login SQL Server di DBeaver – Connection timed out: no further information.. Verify the connection properties
Posted at 29/12/2021
Error Login SQL Server – DBeaver merupakan salah satu GUI untuk mengakses database, aplikasi ini sangat bermanfaat karena dapat mengakses hingga puluhan jenis database... Read More
Cara Cepat Download dan Install SQL Server 2019 Developer Edition
Posted at 23/12/2021
Install SQL Server 2019 Developer Edition – SQL Server merupakan salah satu database RDBMS yang sangat populer, digunakan oleh berbagai kalangan dan instansi sebagai... Read More
Cara Generate Random Password di SQL Server Menggunakan Stored Procedure
Posted at 20/09/2021
Banyak cara untuk melakukan generate password secara otomatis, kamu dapat melakukannya di sisi apps menggunakan koding bahasa programan atau melakukannya di sisi database menggunakan... Read More
Membuat Generate Random Password Menggunakan Function SQL Server
Posted at 19/09/2021
Password memiliki standar tersendiri supaya menjadi lebih aman dari proses hacking, semakin rumit password maka semakin aman password tersebut dalam di tebak tetapi tentunya... Read More