CARA MEMBUAT STORE PROCEDURE DI SQL SERVER ~ NARASI SPARTAN

Pada artikel ini, kita akan belajar cara membuat STORE PROCEDURE di SQL Server dengan berbagai contoh.

STORE PROCEDURE SQL Server adalah kumpulan pernyataan yang dikelompokkan sebagai unit logis dan disimpan dalam database. STORE PROCEDURE menerima parameter dan mengeksekusi pernyataan T-SQL dalam prosedur, mengembalikan set hasil jika ada.

Untuk memahami perbedaan antara fungsi dan prosedur tersimpan di SQL Server, Anda dapat merujuk ke artikel ini, Fungsi vs prosedur tersimpan di SQL Server dan untuk mempelajari tentang prosedur tersimpan sebagian di SQL Server, klik Prosedur tersimpan sebagian di SQL Server .

Manfaat menggunakan Store procedure

Dapat dengan mudah dimodifikasi : Kita dapat dengan mudah memodifikasi kode di dalam stored procedure tanpa perlu me-restart atau menjalankan aplikasi. Misalnya, jika kueri T-SQL ditulis dalam aplikasi dan jika kita perlu mengubah logika, kita harus mengubah kode dalam aplikasi dan menerapkannya kembali. Prosedur SQL Server Stored menghilangkan tantangan tersebut dengan menyimpan kode dalam database. jadi, ketika kita ingin mengubah logika di dalam prosedur kita bisa melakukannya dengan pernyataan ALTER PROCEDURE sederhana.

Mengurangi lalu lintas jaringan: Saat kami menggunakan prosedur tersimpan alih-alih menulis kueri T-SQL di tingkat aplikasi, hanya nama prosedur yang dilewatkan melalui jaringan alih-alih seluruh kode T-SQL.

Dapat digunakan kembali : Prosedur tersimpan dapat dijalankan oleh banyak pengguna atau beberapa aplikasi klien tanpa perlu menulis kode lagi.

Keamanan: Prosedur tersimpan mengurangi ancaman dengan menghilangkan akses langsung ke tabel. kami juga dapat mengenkripsi prosedur tersimpan saat membuatnya sehingga kode sumber di dalam prosedur tersimpan tidak terlihat. Gunakan alat pihak ketiga seperti ApexSQL Decrypt untuk mendekripsi prosedur tersimpan terenkripsi.

Kinerja: Prosedur tersimpan SQL Server saat dijalankan untuk pertama kali membuat rencana dan menyimpannya di buffer pool sehingga rencana dapat digunakan kembali saat dijalankan di lain waktu.

Saya membuat tabel sampel yang akan digunakan dalam contoh di artikel ini.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE Product
(ProductID INT, ProductName VARCHAR(100) )
GO
 
CREATE TABLE ProductDescription
(ProductID INT, ProductDescription VARCHAR(800) )
GO
 
INSERT INTO Product VALUES (680,'HL Road Frame - Black, 58')
,(706,'HL Road Frame - Red, 58')
,(707,'Sport-100 Helmet, Red')
GO
 
INSERT INTO ProductDescription VALUES (680,'Replacement mountain wheel for entry-level rider.')
,(706,'Sturdy alloy features a quick-release hub.')
,(707,'Aerodynamic rims for smooth riding.')
GO

Membuat prosedur tersimpan sederhana

Kami akan membuat prosedur tersimpan sederhana yang menggabungkan dua tabel dan mengembalikan set hasil seperti yang ditunjukkan pada contoh berikut.

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE GetProductDesc
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
 
END

Kita dapat menggunakan 'EXEC ProcedureName' untuk menjalankan prosedur tersimpan. Ketika kita menjalankan prosedur GetProductDesc, kumpulan hasil terlihat seperti di bawah ini.

kumpulan hasil dari prosedur tersimpan SQL Server

Membuat prosedur tersimpan dengan parameter

Mari kita buat prosedur tersimpan SQL Server yang menerima parameter input dan memproses catatan berdasarkan parameter input.

Berikut adalah contoh prosedur tersimpan yang menerima parameter.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE GetProductDesc_withparameters
(@PID INT)
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
WHERE P.ProductID=@PID
 
END

1
EXEC GetProductDesc_withparameters 706

Saat menjalankan prosedur tersimpan, kita harus melewati parameter input. Silakan lihat gambar di bawah untuk set hasil.

Prosedur tersimpan SQL Server dengan parameter

Membuat prosedur tersimpan dengan nilai parameter default

Berikut adalah contoh prosedur tersimpan dengan nilai parameter default.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE GetProductDesc_withDefaultparameters
(@PID INT =706)
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
WHERE P.ProductID=@PID
 
END

Ketika kita menjalankan prosedur di atas tanpa melewati nilai parameter, nilai default 706 akan digunakan. Tetapi ketika dieksekusi melewati nilai, nilai default akan diabaikan dan nilai yang diteruskan akan dianggap sebagai parameter.

Prosedur tersimpan SQL Server dengan parameter default

Membuat prosedur tersimpan dengan parameter keluaran

Di bawah ini adalah contoh prosedur tersimpan dengan parameter keluaran. Contoh berikut mengambil EmpID yang merupakan kolom identitas otomatis ketika karyawan baru dimasukkan.

1
CREATE TABLE Employee (EmpID int identity(1,1),EmpName varchar(500))

1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE ins_NewEmp_with_outputparamaters
(@Ename varchar(50),
@EId int output)
AS
BEGIN
SET NOCOUNT ON
 
INSERT INTO Employee (EmpName) VALUES (@Ename)
 
SELECT @EId= SCOPE_IDENTITY()
 
END

Menjalankan prosedur tersimpan dengan parameter keluaran sedikit berbeda. Kita harus mendeklarasikan variabel untuk menyimpan nilai yang dikembalikan oleh parameter output.

1
2
3
4
5
declare @EmpID INT
 
EXEC ins_NewEmp_with_outputparamaters 'Andrew', @EmpID OUTPUT
 
SELECT @EmpID

Prosedur tersimpan SQL Server dengan parameter default

catatan yang dimasukkan ke dalam tabel

Membuat prosedur tersimpan terenkripsi

Kita dapat menyembunyikan kode sumber dalam prosedur tersimpan dengan membuat prosedur dengan opsi "ENKRIPSI".

Berikut adalah contoh prosedur tersimpan terenkripsi.

1
2
3
4
5
6
7
8
CREATE PROCEDURE GetEmployees
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON 
 
SELECT EmpID,EmpName from Employee
END

Ketika kami mencoba untuk melihat kode prosedur tersimpan SQL Server menggunakan sp_helptext, ia mengembalikan "Teks untuk objek 'GetEmployees' dienkripsi."

prosedur tersimpan SQL Server terenkripsi

Saat Anda mencoba membuat skrip prosedur tersimpan terenkripsi dari studio manajemen SQL Server, kesalahan seperti di bawah ini muncul.

prosedur tersimpan SQL Server terenkripsi

Membuat prosedur sementara

Seperti tabel sementara, kita juga dapat membuat prosedur sementara. Ada dua jenis prosedur sementara, satu adalah prosedur penyimpanan sementara lokal dan yang lainnya adalah prosedur sementara global.

Prosedur ini dibuat dalam database tempdb .

Prosedur tersimpan SQL Server lokal sementara : Ini dibuat dengan # sebagai awalan dan hanya dapat diakses di sesi tempat prosedur tersebut dibuat. Prosedur ini secara otomatis dibatalkan ketika koneksi ditutup.

Berikut ini adalah contoh membuat prosedur sementara lokal.

1
2
3
4
5
CREATE PROCEDURE #Temp
AS
BEGIN
PRINT 'Local temp procedure'
END

Prosedur tersimpan SQL Server sementara global: Prosedur ini dibuat dengan ## sebagai awalan dan dapat diakses di sesi lain juga. Prosedur ini secara otomatis terputus ketika koneksi yang digunakan untuk membuat prosedur ditutup.

Di bawah ini adalah contoh membuat prosedur sementara global.

1
2
3
4
5
CREATE PROCEDURE ##TEMP
AS
BEGIN
PRINT 'Global temp procedure'
END

Memodifikasi prosedur tersimpan

Gunakan pernyataan ALTER PROCEDURE untuk memodifikasi prosedur tersimpan yang ada. Berikut adalah contoh modifikasi prosedur yang ada.

1
2
3
4
5
6
7
8
9
10
ALTER PROCEDURE GetProductDesc
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
 
END

Mengganti nama prosedur tersimpan

Untuk mengganti nama prosedur tersimpan menggunakan T-SQL, gunakan prosedur tersimpan sistem sp_rename. Berikut adalah contoh yang mengganti nama prosedur "GetProductDesc" menjadi nama baru "GetProductDesc_new".

1
sp_rename 'GetProductDesc','GetProductDesc_new'

mengganti nama prosedur tersimpan SQL Server

Kesimpulan

Pada artikel ini, kami menjelajahi prosedur tersimpan SQL Server dengan contoh yang berbeda

NARASI SPARTAN

JSON Variables

Selasa, 28 Juni 2022