Membuat prosedur tersimpan

Prosedur tersimpan adalah salah satu alat paling kuat di SQL Server untuk merangkum logika bisnis dan meningkatkan performa aplikasi. Saat membuat prosedur tersimpan, Anda membuat blok kode yang dapat digunakan kembali yang dijalankan di server, mengurangi lalu lintas jaringan, dan memusatkan logika akses data.

Memahami prosedur tersimpan

Prosedur tersimpan adalah kumpulan pernyataan T-SQL yang dikompilasi yang disimpan dan dijalankan SQL Server sebagai satu unit. Tidak seperti kueri yang tidak diencana yang Anda kirim ke server setiap kali, prosedur tersimpan telah dikommpilasikan dan dioptimalkan, yang berarti kueri berjalan lebih cepat pada eksekusi berikutnya.

Anda menggunakan prosedur tersimpan untuk merangkum logika bisnis yang kompleks, menerapkan aturan validasi data, dan mengontrol bagaimana aplikasi berinteraksi dengan database Anda. Misalnya, alih-alih mengizinkan akses tabel langsung, Anda dapat membuat prosedur tersimpan yang memvalidasi input, menerapkan aturan bisnis, dan mencatat perubahan sebelum memodifikasi data.

Manfaat performa berasal dari caching rencana kueri. Dengan kueri yang tidak diencana, SQL Server harus mengurai dan mengoptimalkan setiap kueri setiap saat. Dengan prosedur tersimpan, rencana eksekusi di-cache setelah eksekusi pertama, mengurangi beban untuk operasi berulang.

Membuat prosedur simpan dasar

Membuat prosedur yang disimpan dimulai dengan pernyataan CREATE PROCEDURE yang diikuti oleh logika T-SQL Anda. Anda menentukan nama prosedur menggunakan pengidentifikasi yang memenuhi syarat skema, yang meningkatkan kejelasan dan performa.

CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        CustomerID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    ORDER BY OrderDate DESC;
END

Pernyataan SET NOCOUNT ON mencegah pesan tentang jumlah baris yang terpengaruh dikirim ke klien. Ini mengurangi lalu lintas jaringan dan meningkatkan performa, terutama ketika prosedur menjalankan beberapa pernyataan.

Saat Anda membuat prosedur, gunakan BEGIN kata kunci dan END untuk menentukan isi prosedur dengan jelas. Ini membuat kode Anda lebih mudah dibaca dan membantu mencegah kesalahan saat menambahkan atau memodifikasi logika nanti.

Bekerja dengan parameter

Parameter-parameter membuat prosedur tersimpan fleksibel dan dapat digunakan kembali. Anda menentukan parameter input untuk menerima nilai dari aplikasi panggilan, dan parameter output untuk mengembalikan nilai kembali ke pemanggil.

Parameter input menggunakan simbol @ diikuti dengan nama parameter dan jenis data. Anda dapat memberikan nilai default untuk membuat parameter opsional:

CREATE PROCEDURE dbo.GetCustomerOrdersByDate
    @CustomerID int,
    @StartDate datetime = NULL,
    @EndDate datetime = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
        AND (@StartDate IS NULL OR OrderDate >= @StartDate)
        AND (@EndDate IS NULL OR OrderDate <= @EndDate)
    ORDER BY OrderDate DESC;
END

Parameter output memungkinkan Anda mengembalikan nilai ke aplikasi panggilan. Anda menentukannya menggunakan OUTPUT kata kunci:

CREATE PROCEDURE dbo.CalculateOrderTotal
    @OrderID int,
    @TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT @TotalAmount = SUM(Quantity * UnitPrice)
    FROM dbo.OrderDetails
    WHERE OrderID = @OrderID;
    
    RETURN 0;
END

Saat Anda memanggil prosedur dengan parameter output, Anda harus mendeklarasikan variabel untuk menerima nilai dan menggunakan OUTPUT kata kunci dalam EXECUTE pernyataan.

Menerapkan penanganan kesalahan

Prosedur tersimpan yang kuat termasuk penanganan kesalahan untuk mengelola kondisi tak terduga dan menjaga integritas data. Anda menerapkan penanganan kesalahan menggunakan TRY...CATCH blok, yang berfungsi mirip dengan penanganan pengecualian dalam bahasa pemrograman lainnya.

CREATE PROCEDURE dbo.InsertCustomerOrder
    @CustomerID int,
    @OrderDate datetime,
    @TotalAmount decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validate customer exists
        IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
        BEGIN
            RAISERROR('Customer does not exist.', 16, 1);
        END
        
        -- Insert order
        INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
        VALUES (@CustomerID, @OrderDate, @TotalAmount);
        
        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        DECLARE @ErrorState int = ERROR_STATE();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        RETURN -1;
    END CATCH
END

TRY Blok berisi logika utama Anda, sementara CATCH blok menangani kesalahan apa pun yang terjadi. Anda dapat menggunakan fungsi sistem seperti ERROR_MESSAGE(), ERROR_SEVERITY(), dan ERROR_STATE() untuk menangkap detail kesalahan dan meneruskannya ke aplikasi panggilan.

Selalu periksa @@TRANCOUNT sebelum menggulung balik transaksi di blok CATCH. Ini mencegah kesalahan jika transaksi sudah selesai atau tidak pernah dimulai.

Menerapkan praktik terbaik

Mengikuti praktik terbaik yang ditetapkan saat Anda membuat prosedur tersimpan memastikan prosedur tersebut dapat dipertahankan, aman, dan berkinerja.

Menggunakan nama yang memenuhi kualifikasi skema

Gunakan nama yang memenuhi syarat skema untuk semua objek. Ini menghilangkan ambiguitas dan meningkatkan kinerja dengan menghindari beban resolusi skema.

-- Good
SELECT * FROM dbo.Orders

-- Avoid
SELECT * FROM Orders

Menerapkan validasi parameter

Terapkan validasi parameter di awal prosedur Anda. Segera hentikan proses ketika input tidak valid agar tidak memproses data yang salah:

IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
    RAISERROR('CustomerID must be a positive integer.', 16, 1);
    RETURN -1;
END

Menghindari SELECT *

Hindari SELECT * dalam kode produksi. Cantumkan kolom secara eksplisit untuk mencegah masalah saat struktur tabel berubah dan untuk meningkatkan performa kueri:

-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders

-- Avoid
SELECT * FROM dbo.Orders

Gunakan nama yang bermakna

Gunakan nama yang bermakna yang menjelaskan apa yang dilakukan prosedur. Sertakan kata kerja yang menunjukkan operasi (Dapatkan, Sisipkan, Perbarui, Hapus, Hitung):

CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders

Hindari awalan sp_

Jangan gunakan awalan sp_ untuk prosedur tersimpan Anda. SQL Server mencadangkan awalan ini untuk prosedur sistem yang disimpan dalam master database. Saat Anda memberi nama prosedur dengan sp_, SQL Server terlebih dahulu mencari master sebelum memeriksa database saat ini, menambahkan overhead yang tidak perlu:

-- Good
CREATE PROCEDURE dbo.GetCustomerOrders

-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders

Membangun praktik ini membantu Anda membuat prosedur tersimpan yang dapat dipahami, dirawat, dan dipercaya oleh tim Anda untuk berkinerja andal di lingkungan produksi.