Meneruskan parameter ke prosedur tersimpan

  • 3 menit

Salah satu keuntungan menggunakan prosedur tersimpan adalah Anda dapat meneruskan parameter ke prosedur tersimpan saat runtime. Parameter input dapat digunakan untuk memfilter hasil kueri, seperti dalam predikat klausa WHERE, atau nilai dalam operator TOP. Parameter prosedur juga dapat mengembalikan nilai ke program panggilan jika parameter ditandai sebagai parameter OUTPUT. Anda juga dapat menetapkan nilai default ke parameter.

Parameter input

Prosedur tersimpan mendeklarasikan parameter input berdasarkan nama dan jenis data di header pernyataan CREATE PROCEDURE. Parameter tersebut kemudian digunakan sebagai variabel lokal dalam badan prosedur. Anda dapat mendeklarasikan dan menggunakan lebih dari satu parameter dalam prosedur tersimpan. Parameter input adalah jenis default parameter.

Nama parameter harus diawali oleh karakter @, dan bersifat unik dalam cakupan prosedur.

Untuk meneruskan parameter ke prosedur tersimpan, gunakan sintaks berikut:

 EXEC <schema_name>.<procedure_name> @<parameter_name> = 'VALUE'

Misalnya, prosedur tersimpan bernama ProductsBySupplier dalam skema Produk, akan dijalankan dengan parameter bernama supplierid menggunakan kode berikut:

EXEC Products.ProductsBySupplier @supplierid = 5

Ini adalah praktik terbaik untuk meneruskan nilai parameter sebagai pasangan nama-nilai. Parameter jamak dipisahkan dengan koma. Misalnya, jika parameter disebut dengan customerid dan nilai yang akan diteruskan adalah 5, gunakan kode berikut:

EXEC customers.customerid @customerid=5

Anda juga dapat meneruskan parameter berdasarkan posisi, menghilangkan nama parameter. Namun, parameter harus diteruskan baik berdasarkan nama atau berdasarkan posisi - Anda tidak dapat mencampur cara parameter diteruskan ke prosedur. Jika parameter diteruskan berdasarkan urutan, parameter harus berada dalam urutan yang sama seperti yang tercantum dalam pernyataan CREATE PROCEDURE.

Anda dapat meneruskan nilai sebagai konstanta, atau sebagai variabel, seperti:

EXEC customers.customerid @CustomerID

Namun, Anda tidak dapat menggunakan fungsi untuk meneruskan parameter. Misalnya, kode berikut akan menimbulkan kesalahan:

EXEC customers.customerid GETDATE()

Periksa bahwa parameter adalah jenis data yang benar. Misalnya, jika prosedur menerima NVARCHAR, teruskan dalam format string karakter Unicode: N'string'.

Anda dapat melihat nama parameter dan jenis data di Azure Data Studio atau SQL Server Management Studio (SSMS). Perluas daftar objek database hingga Anda melihat folder Prosedur Tersimpan, pada folder Programabilitas.

Diagram memperlihatkan perluas folder Pemrograman untuk melihat prosedur tersimpan dan jenis data parameter.

Prosedur tersimpan nama dua bagian ditampilkan, bersama dengan folder Parameter yang berisi untuk setiap parameter:

  • Nama parameter.
  • Jenis data.
  • Panah masuk yang menandakan parameter input.
  • Panah keluar yang menandakan parameter output.

Anda dapat mengkueri tampilan katalog sistem seperti sys.parameter untuk mengambil definisi parameter bersama dengan ID objek.

Nilai default

Jika parameter dinyatakan dengan nilai default, Anda tidak perlu meneruskan nilai saat prosedur tersimpan dijalankan. Jika nilai diteruskan, nilai tersebut akan digunakan. Tetapi jika tidak ada nilai yang diteruskan, maka default digunakan.

Saat prosedur tersimpan dibuat, parameter diberi nilai default menggunakan operator = , seperti:

CREATE PROCEDURE Sales.SalesYTD  
    -- Set NULL as the default value
    @SalesPerson nvarchar(50) = NULL 
    AS ...

Parameter output

Anda telah melihat cara meneruskan nilai ke dalam prosedur tersimpan, yang dikenal sebagai parameter input.

Namun, Anda juga dapat menampilkan nilai ke program panggilan. Ini dikenal sebagai parameter OUTPUT. Gunakan kata kunci OUTPUT atau OUT untuk menentukan parameter output dalam pernyataan CREATE PROCEDURE. Prosedur menampilkan nilai saat ini dari parameter output ke program panggilan ketika prosedur keluar.

Program panggilan juga harus menggunakan kata kunci OUTPUT saat menjalankan prosedur untuk menyimpan nilai parameter dalam variabel yang dapat digunakan dalam program panggilan.

Dalam fragmen kode T-SQL berikut, dua parameter ditentukan sebagai parameter OUTPUT, @ComparePrice dan @ListPrice.

CREATE PROCEDURE Production.uspGetList @Product varchar(40)
    , @MaxPrice money   
    , @ComparePrice money OUTPUT  
    , @ListPrice money OUT  
AS

Nilai kemudian ditetapkan untuk parameter OUTPUT dalam badan prosedur tersimpan, misalnya, SET @ComparePrice = @MaxPrice;.