Tutorial SQL Server - 5. Function & Store Procedure

Function & Store Procedure sama-sama merupakan kumpulan statement-statement SQL yang digabung menjadi 1 function atau procedure. Perbedaan utamanya adalah function dapat dipanggil bersamaan dengan statement select sql sedangkan store procedure tidak bisa. Contoh function-function default yang sudah dimiliki sistem misalnya SUM(), ABS(), LEFT(), RIGHT() dan masih banyak lagi. Untuk melihat function-function yang sudah ada, klik pada bagian Object Explorer. Expand <Databases>~<Nama Database>~<Programmability>~<Functions>~<System Functions>. Sama seperti object database lainnya, function & store procedure bisa kita buat, ubah dan hapus dengan statement CREATE, ALTER dan DROP. Function & Store Procedure sama-sama terletak pada bagian Programmability dalam masing-masing database. Untuk membuat function, klik kanan bagian Functions~New~Pilih Jenis Function. Untuk membuat procedure, klik kanan bagian Stored Procedures~New Store Procedure.

NB : Sebelum dilanjutkan, pastikan database inventory1 sudah dibuat dan data-data sudah diisi.

Function

Function memiliki 3 jenis parameter yaitu sbb :

• Input
Adalah parameter yang wajib diisi ketika function dipanggil. Contoh :

CREATE FUNCTION nama (@parameter1 int)

• Optional
Adalah parameter yang boleh dikosongkan ketika function dipanggil. Contoh :

CREATE FUNCTION nama (@parameter1 int = NULL)

• Default
Adalah parameter yang akan berisi nilai default jika parameter tidak disi ketika function dipanggil. Contoh :

CREATE FUNCTION nama (@parameter1 int = 100)

Berikut adalah contoh syntax function :

    USE inventory1;
    GO
    
    -- membuat function
    CREATE FUNCTION umur_faktur(@tgl_faktur DATE)
    RETURNS INT
    AS
    BEGIN
        DECLARE @umur INT
        SELECT @umur = DATEDIFF(YEAR,@tgl_faktur,GETDATE())
        RETURN @umur
    END
    GO
    
    -- mengubah function
    ALTER FUNCTION umur_faktur(@tgl_faktur DATE)
    RETURNS INT
    AS
    BEGIN
        DECLARE @umur INT
        SELECT @umur = DATEDIFF(DAY,@tgl_faktur,GETDATE())
        RETURN @umur
    END
    GO
    
    -- menghapus function
    DROP FUNCTION umur_faktur;
    GO
    
    -- menggunakan function
    SELECT kd_jual, tgl_jual, dbo.umur_faktur(tgl_jual) AS 'Umur Faktur' FROM t_jual_h;
    GO

Referensi-referensi function dalam MSDN :

• Create Function
• Alter Function
• Drop Function

Store Procedure

Store procedure memiliki 3 jenis parameter yang sama dengan parameter function diatas. Cara penulisan parameternya sama dengan parameter function. Berikut adalah contoh syntax store procedure :

    USE inventory1;
    GO
    
    -- membuat store procedure
    CREATE PROCEDURE lihat_umur_faktur
    AS
    BEGIN
        SELECT kd_jual, tgl_jual, dbo.umur_faktur(tgl_jual) AS 'Umur Faktur' 
        FROM t_jual_h
    END
    GO
    
    -- mengubah store procedure
    ALTER PROCEDURE lihat_umur_faktur
    AS
    BEGIN
        SELECT kd_jual, tgl_jual, DATEDIFF(DAY,tgl_jual,GETDATE()) AS 'Umur Faktur' 
        FROM t_jual_h
        ORDER BY kd_jual DESC
    END
    GO
    
    -- menghapus store procedure
    DROP PROCEDURE lihat_umur_faktur;
    GO
    
    -- menjalankan store procedure
    EXECUTE lihat_umur_faktur; -- hasilnya sama dengan syntax EXEC
    EXEC lihat_umur_faktur;
    GO
    
    -- store procedure dengan parameter
    CREATE PROCEDURE lihat_faktur_per_cust (@kd_cust VARCHAR(20), @status_lunas BIT = 0)
    AS
    BEGIN
        SELECT kd_jual,tgl_jual,ttl_jual,lunas 
        FROM t_jual_h
        WHERE kd_cust=@kd_cust AND lunas=@status_lunas
        ORDER BY tgl_jual DESC
    END
    GO
    
    -- menjalankan store procedure dengan parameter
    EXEC lihat_faktur_per_cust @kd_cust='J001';
    EXEC lihat_faktur_per_cust @kd_cust='J001', @status_lunas=1;
    GO
    
    -- store procedure utk menginput data
    CREATE PROCEDURE input_customer (@kd_custX VARCHAR(20), @nm_custX VARCHAR(100), @sisa_piutangX MONEY = 0)
    AS
    BEGIN
        INSERT INTO t_cust (kd_cust,nm_cust,sisa_piutang)VALUES (@kd_custX,@nm_custX,@sisa_piutangX)
        SELECT * FROM t_cust
    END
    GO
    
    -- menjalankan store procedure utk menginput data
    EXEC input_customer @kd_custX='ZZZZ', @nm_custX='nama customer via store procedure'
    GO

Referensi-referensi store procedure dalam MSDN :

• Create Procedure
• Alter Procedure
• Drop Procedure