CREATE PROCEDURE (Transact-SQL)

Berlaku untuk: Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL di Microsoft Fabric Warehouse di Microsoft Fabric

Membuat prosedur tersimpan Transact-SQL atau common language runtime (CLR) di SQL Server, Azure SQL Database, dan Analytics Platform System (PDW). Prosedur tersimpan mirip dengan prosedur dalam bahasa pemrograman lain yang dapat mereka:

• Terima parameter input dan kembalikan beberapa nilai dalam bentuk parameter output ke prosedur panggilan atau batch.
• Berisi pernyataan pemrograman yang melakukan operasi dalam database, termasuk memanggil prosedur lain.
• Mengembalikan nilai status ke prosedur panggilan atau batch untuk menunjukkan keberhasilan atau kegagalan (dan alasan kegagalan).

Gunakan pernyataan ini untuk membuat prosedur permanen dalam database saat ini atau prosedur sementara dalam tempdb database.

Catatan

Integrasi .NET Framework CLR ke SQL Server dibahas dalam topik ini. Integrasi CLR tidak berlaku untuk Azure SQL Database.

Lompat ke Contoh Sederhana untuk melewati detail sintaksis dan dapatkan contoh cepat prosedur tersimpan dasar.

Konvensi sintaks transact-SQL

Sintaks

Sintaks t-SQL untuk prosedur tersimpan di SQL Server dan Azure SQL Database:

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ VARYING ] [ NULL ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

Sintaks T-SQL untuk prosedur tersimpan CLR:

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

Sintaks t-SQL untuk prosedur tersimpan yang dikompilasi secara asli:

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ]
        [ OUT | OUTPUT ] [READONLY]
    ] [ ,... n ]
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]
AS
{
  BEGIN ATOMIC WITH ( <set_option> [ ,... n ] )
sql_statement [;] [ ... n ]
[ END ]
}
[;]

<set_option> ::=
    LANGUAGE = [ N ] 'language'
  | TRANSACTION ISOLATION LEVEL = { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }
  | [ DATEFIRST = number ]
  | [ DATEFORMAT = format ]
  | [ DELAYED_DURABILITY = { OFF | ON } ]

Sintaks t-SQL untuk prosedur tersimpan di Azure Synapse Analytics dan Gudang Data Paralel:

CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Sintaks t-SQL untuk prosedur tersimpan di Microsoft Fabric:

CREATE [ OR ALTER ] { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Argumen

ATAU UBAH

Berlaku untuk: Azure SQL Database, SQL Server (dimulai dengan SQL Server 2016 (13.x) SP1).

Mengubah prosedur jika sudah ada.

schema_name

Nama skema tempat prosedur berada. Prosedur terikat skema. Jika nama skema tidak ditentukan saat prosedur dibuat, skema default pengguna yang membuat prosedur akan ditetapkan secara otomatis.

procedure_name

Nama prosedur. Nama prosedur harus mematuhi aturan untuk pengidentifikasi dan harus unik dalam skema.

Perhatian

Hindari penggunaan awalan sp_ saat penamaan prosedur. Awalan ini digunakan oleh SQL Server untuk menunjuk prosedur sistem. Menggunakan awalan dapat menyebabkan kode aplikasi rusak jika ada prosedur sistem dengan nama yang sama.

Prosedur sementara lokal atau global dapat dibuat dengan menggunakan satu tanda angka (#) sebelum procedure_name (#procedure_name) untuk prosedur sementara lokal, dan dua tanda angka untuk prosedur sementara global (##procedure_name). Prosedur sementara lokal hanya terlihat oleh koneksi yang membuatnya dan dihilangkan ketika koneksi tersebut ditutup. Prosedur sementara global tersedia untuk semua koneksi dan dihilangkan di akhir sesi terakhir menggunakan prosedur . Nama sementara tidak dapat ditentukan untuk prosedur CLR.

Nama lengkap untuk prosedur atau prosedur sementara global, termasuk ##, tidak boleh melebihi 128 karakter. Nama lengkap untuk prosedur sementara lokal, termasuk #, tidak boleh melebihi 116 karakter.

; angka

Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru, dan Azure SQL Database.

Bilangan bulat opsional yang digunakan untuk mengelompokkan prosedur dengan nama yang sama. Prosedur yang dikelompokkan ini dapat dihilangkan bersama-sama dengan menggunakan satu pernyataan DROP PROCEDURE.

Catatan

Fitur ini akan dihapus dalam versi SQL Server yang akan datang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini.

Prosedur bernomor tidak dapat menggunakan jenis xml atau CLR yang ditentukan pengguna dan tidak dapat digunakan dalam panduan paket.

@parameter_name

Parameter yang dinyatakan dalam prosedur. Tentukan nama parameter dengan menggunakan tanda@ () sebagai karakter pertama. Nama parameter harus mematuhi aturan untuk pengidentifikasi. Parameter bersifat lokal untuk prosedur; nama parameter yang sama dapat digunakan dalam prosedur lain.

Satu atau beberapa parameter dapat dideklarasikan; maksimum adalah 2.100. Nilai setiap parameter yang dideklarasikan harus disediakan oleh pengguna ketika prosedur dipanggil kecuali nilai default untuk parameter ditentukan atau nilai diatur ke sama dengan parameter lain. Jika prosedur berisi parameter bernilai tabel, dan parameter hilang dalam panggilan, tabel kosong akan diteruskan. Parameter hanya dapat menggantikan ekspresi konstanta; mereka tidak dapat digunakan alih-alih nama tabel, nama kolom, atau nama objek database lainnya. Untuk informasi selengkapnya, lihat EXECUTE (Transact-SQL).

Parameter tidak dapat dideklarasikan jika REPLIKASI FOR ditentukan.

[ type_schema_name . ] data_type

Jenis data parameter dan skema tempat tipe data berada.

Panduan untuk prosedur T-SQL:

• Semua jenis data Transact-SQL dapat digunakan sebagai parameter.
• Anda dapat menggunakan jenis tabel yang ditentukan pengguna untuk membuat parameter bernilai tabel. Parameter bernilai tabel hanya dapat berupa parameter INPUT dan harus disertai dengan kata kunci READONLY. Untuk informasi selengkapnya, lihat Menggunakan Parameter Bernilai Tabel (Mesin Database)
• jenis data kursor hanya dapat berupa parameter OUTPUT dan harus disertai dengan kata kunci VARYING.

Panduan untuk prosedur CLR:

• Semua jenis data SQL Server asli yang memiliki kode terkelola yang setara dapat digunakan sebagai parameter. Untuk informasi selengkapnya tentang korespondensi antara jenis CLR dan jenis data sistem SQL Server, lihat Memetakan Data Parameter CLR. Untuk informasi selengkapnya tentang jenis data sistem SQL Server dan sintaksnya, lihat Jenis Data (Transact-SQL).

• Jenis data bernilai tabel atau kursor tidak dapat digunakan sebagai parameter.

• Jika jenis data parameter adalah jenis yang ditentukan pengguna CLR, Anda harus memiliki izin EXECUTE pada jenis tersebut.

BERBAGAI

Menentukan kumpulan hasil yang didukung sebagai parameter output. Parameter ini dibangun secara dinamis oleh prosedur dan isinya dapat bervariasi. Hanya berlaku untuk parameter kursor . Opsi ini tidak valid untuk prosedur CLR.

Default

Nilai default untuk parameter. Jika nilai default didefinisikan untuk parameter, prosedur dapat dijalankan tanpa menentukan nilai untuk parameter tersebut. Nilai default harus berupa konstanta atau bisa NULL. Nilai konstanta dapat dalam bentuk kartubebas, sehingga memungkinkan untuk menggunakan kata kunci LIKE saat meneruskan parameter ke dalam prosedur.

Nilai default dicatat dalam sys.parameters.default kolom hanya untuk prosedur CLR. Kolom tersebut adalah NULL untuk parameter prosedur Transact-SQL.

OUT | HASIL

Menunjukkan bahwa parameter adalah parameter output. Gunakan parameter OUTPUT untuk mengembalikan nilai ke pemanggil prosedur. parameter teks, ntext, dan gambar tidak dapat digunakan sebagai parameter OUTPUT, kecuali prosedurnya adalah prosedur CLR. Parameter output dapat menjadi tempat penampung kursor, kecuali prosedurnya adalah prosedur CLR. Jenis data nilai tabel tidak dapat ditentukan sebagai parameter OUTPUT prosedur.

READONLY

Menunjukkan bahwa parameter tidak dapat diperbarui atau dimodifikasi dalam isi prosedur. Jika jenis parameter adalah jenis nilai tabel, READONLY harus ditentukan.

KOMPILASI ULANG

Menunjukkan bahwa Mesin Database tidak menyimpan cache rencana kueri untuk prosedur ini, memaksanya untuk dikompilasi setiap kali dijalankan. Untuk informasi selengkapnya mengenai alasan memaksa kompilasi ulang, lihat Mengkombinasikan ulang Prosedur Tersimpan. Opsi ini tidak dapat digunakan ketika FOR REPLICATION ditentukan atau untuk prosedur CLR.

Untuk menginstruksikan Mesin Database untuk membuang rencana kueri untuk kueri individual di dalam prosedur, gunakan petunjuk kueri RECOMPILE dalam definisi kueri. Untuk informasi selengkapnya, lihat Petunjuk Kueri (Transact-SQL).

ENKRIPSI

Berlaku untuk: SQL Server (SQL Server 2008 (10.0.x) dan yang lebih baru), Azure SQL Database.

Menunjukkan bahwa SQL Server mengonversi teks asli pernyataan CREATE PROCEDURE ke format yang dikaburkan. Output obfuscation tidak langsung terlihat di salah satu tampilan katalog di SQL Server. Pengguna yang tidak memiliki akses ke tabel sistem atau file database tidak dapat mengambil teks yang dikaburkan. Namun, teks tersedia untuk pengguna istimewa yang dapat mengakses tabel sistem melalui port DAC atau langsung mengakses file database. Selain itu, pengguna yang dapat melampirkan debugger ke proses server dapat mengambil prosedur yang didekripsi dari memori saat runtime. Untuk informasi selengkapnya tentang mengakses metadata sistem, lihat Konfigurasi Visibilitas Metadata.

Opsi ini tidak valid untuk prosedur CLR.

Prosedur yang dibuat dengan opsi ini tidak dapat diterbitkan sebagai bagian dari replikasi SQL Server.

Klausa EXECUTE AS

Menentukan konteks keamanan untuk menjalankan prosedur.

Untuk prosedur tersimpan yang dikompilasi secara asli, mulai SQL Server 2016 (13.x) dan di Azure SQL Database, tidak ada batasan pada klausa EXECUTE AS. Dalam klausa SQL Server 2014 (12.x) klausa SELF, OWNER, dan 'user_name' didukung dengan prosedur tersimpan yang dikompilasi secara asli.

Untuk informasi selengkapnya, lihat EXECUTE AS Clause (Transact-SQL).

UNTUK REPLIKASI

Berlaku untuk: SQL Server (SQL Server 2008 (10.0.x) dan yang lebih baru), Azure SQL Database.

Menentukan bahwa prosedur dibuat untuk replikasi. Akibatnya, itu tidak dapat dijalankan pada Pelanggan. Prosedur yang dibuat dengan opsi FOR REPLICATION digunakan sebagai filter prosedur dan hanya dijalankan selama replikasi. Parameter tidak dapat dideklarasikan jika REPLIKASI FOR ditentukan. UNTUK REPLIKASI tidak dapat ditentukan untuk prosedur CLR. Opsi RECOMPILE diabaikan untuk prosedur yang dibuat dengan FOR REPLICATION.

Prosedur FOR REPLICATION memiliki jenis objek RF di sys.objects dan sys.procedures.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

Satu atau beberapa pernyataan Transact-SQL yang terdiri dari isi prosedur. Anda dapat menggunakan kata kunci BEGIN dan END opsional untuk mengapit pernyataan. Untuk informasi, lihat bagian Praktik Terbaik, Keterangan Umum, dan Batasan dan Pembatasan yang mengikutinya.

ASSEMBLY_NAME NAMA EKSTERNAL.class_name.method_name

Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru, SQL Database.

Menentukan metode rakitan .NET Framework untuk prosedur CLR yang akan dirujuk. class_name harus berupa pengidentifikasi SQL Server yang valid dan harus ada sebagai kelas di rakitan. Jika kelas memiliki nama yang memenuhi syarat namespace yang menggunakan titik (.) untuk memisahkan bagian namespace, nama kelas harus dibatasi dengan menggunakan tanda kurung ([]) atau tanda kutip (""). Metode yang ditentukan harus merupakan metode statis dari kelas .

Secara default, SQL Server tidak dapat menjalankan kode CLR. Anda dapat membuat, memodifikasi, dan menghilangkan objek database yang mereferensikan modul runtime bahasa umum; namun, Anda tidak dapat menjalankan referensi ini di SQL Server sampai Anda mengaktifkan opsi yang diaktifkan clr. Untuk mengaktifkan opsi , gunakan sp_configure.

Catatan

Prosedur CLR tidak didukung dalam database mandiri.

ATOMIK DENGAN

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru, dan Azure SQL Database.

Menunjukkan eksekusi prosedur tersimpan atomik. Perubahan diterapkan atau semua perubahan digulung balik dengan melemparkan pengecualian. Blok ATOMIC WITH diperlukan untuk prosedur tersimpan yang dikompilasi secara asli.

Jika prosedur RETURN (secara eksplisit melalui pernyataan RETURN, atau secara implisit dengan menyelesaikan eksekusi), pekerjaan yang dilakukan oleh prosedur dilakukan. Jika prosedur THROWs, pekerjaan yang dilakukan oleh prosedur digulung balik.

XACT_ABORT AKTIF secara default di dalam blok atom dan tidak dapat diubah. XACT_ABORT menentukan apakah SQL Server secara otomatis mengembalikan transaksi saat ini ketika pernyataan Transact-SQL menimbulkan kesalahan run-time.

Opsi SET berikut selalu AKTIF di blok ATOMIC, dan tidak dapat diubah.

• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER, ARITHABORT
• NOCOUNT
• ANSI_NULLS
• ANSI_WARNINGS

Opsi SET tidak dapat diubah di dalam blok ATOMIC. Opsi SET dalam sesi pengguna tidak digunakan dalam cakupan prosedur tersimpan yang dikompilasi secara asli. Opsi ini diperbaiki pada waktu kompilasi.

Operasi BEGIN, ROLLBACK, dan COMMIT tidak dapat digunakan di dalam blok atom.

Ada satu blok ATOMIC per prosedur tersimpan yang dikompilasi secara asli, di cakupan luar prosedur. Blok tidak dapat disarangkan. Untuk informasi selengkapnya tentang blok atomik, lihat Prosedur Tersimpan yang Dikompilasi Secara Asli.

NULL | BUKAN NULL

Menentukan apakah nilai null diizinkan dalam parameter. NULL adalah default.

NATIVE_COMPILATION

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru, dan Azure SQL Database.

Menunjukkan bahwa prosedur dikompilasi secara asli. NATIVE_COMPILATION, SCHEMABINDING, dan EXECUTE AS dapat ditentukan dalam urutan apa pun. Untuk informasi selengkapnya, lihat Prosedur Tersimpan yang Dikompilasi Secara Asli.

SCHEMABINDING

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru, dan Azure SQL Database.

Memastikan bahwa tabel yang direferensikan oleh prosedur tidak dapat dihilangkan atau diubah. SCHEMABINDING diperlukan dalam prosedur tersimpan yang dikompilasi secara asli. (Untuk informasi selengkapnya, lihat Prosedur Tersimpan yang Dikompilasi Secara Asli.) Pembatasan SCHEMABINDING sama dengan untuk fungsi yang ditentukan pengguna. Untuk informasi selengkapnya, lihat bagian SCHEMABINDING di CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'language'

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru, dan Azure SQL Database.

Setara dengan opsi sesi SET LANGUAGE (Transact-SQL). LANGUAGE = [N] 'language' diperlukan.

TINGKAT ISOLASI TRANSAKSI

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru, dan Azure SQL Database.

Diperlukan untuk prosedur tersimpan yang dikompilasi secara asli. Menentukan tingkat isolasi transaksi untuk prosedur tersimpan. Opsinya meliputi:

Untuk informasi selengkapnya tentang opsi ini, lihat MENGATUR TINGKAT ISOLASI TRANSAKSI (Transact-SQL).

REPEATABLE READ

Menentukan bahwa pernyataan tidak dapat membaca data yang telah dimodifikasi tetapi belum dilakukan oleh transaksi lain. Jika transaksi lain memodifikasi data yang telah dibaca oleh transaksi saat ini, transaksi saat ini gagal.

SERIALIZABLE

Menentukan hal berikut:

• Pernyataan tidak dapat membaca data yang telah dimodifikasi tetapi belum dilakukan oleh transaksi lain.
• Jika transaksi lain memodifikasi data yang telah dibaca oleh transaksi saat ini, transaksi saat ini gagal.
• Jika transaksi lain menyisipkan baris baru dengan nilai kunci yang akan berada dalam rentang kunci yang dibaca oleh pernyataan apa pun dalam transaksi saat ini, transaksi saat ini gagal.

SNAPSHOT

Menentukan bahwa data yang dibaca oleh pernyataan apa pun dalam transaksi adalah versi data yang konsisten secara transaksional yang ada di awal transaksi.

DATEFIRST = angka

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru, dan Azure SQL Database.

Menentukan hari pertama dalam seminggu menjadi angka dari 1 hingga 7. DATEFIRST bersifat opsional. Jika tidak ditentukan, pengaturan disimpulkan dari bahasa yang ditentukan.

Untuk informasi selengkapnya, lihat MENGATUR DATEFIRST (Transact-SQL).

DATEFORMAT = format

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru, dan Azure SQL Database.

Menentukan urutan bagian tanggal bulan, hari, dan tahun untuk menginterpretasikan string karakter tanggal, smalldatetime, datetime, datetime2, dan datetimeoffset. DATEFORMAT bersifat opsional. Jika tidak ditentukan, pengaturan disimpulkan dari bahasa yang ditentukan.

Untuk informasi selengkapnya, lihat MENGATUR DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { NONAKTIF | ON }

Berlaku untuk: SQL Server 2014 (12.x) dan yang lebih baru, dan Azure SQL Database.

Penerapan transaksi SQL Server dapat sepenuhnya tahan lama, default, atau tertunda tahan lama.

Untuk informasi selengkapnya, lihat Mengontrol Durabilitas Transaksi.

Contoh sederhana

Untuk membantu Anda memulai, berikut adalah dua contoh cepat: SELECT DB_NAME() AS ThisDB; mengembalikan nama database saat ini. Anda dapat membungkus pernyataan tersebut dalam prosedur tersimpan, seperti:

CREATE PROC What_DB_is_this
AS
SELECT DB_NAME() AS ThisDB;

Panggil prosedur penyimpanan dengan pernyataan: EXEC What_DB_is_this;

Sedikit lebih kompleks, adalah menyediakan parameter input untuk membuat prosedur lebih fleksibel. Contohnya:

CREATE PROC What_DB_is_that @ID INT
AS
SELECT DB_NAME(@ID) AS ThatDB;

Berikan nomor ID database saat Anda memanggil prosedur. Misalnya, EXEC What_DB_is_that 2; mengembalikan tempdb.

Lihat Contoh di akhir artikel ini untuk contoh lainnya.

Praktik terbaik

Meskipun ini bukan daftar lengkap praktik terbaik, saran ini dapat meningkatkan performa prosedur.

• Gunakan pernyataan SET NOCOUNT ON sebagai pernyataan pertama dalam isi prosedur. Artinya, letakkan tepat setelah kata kunci AS. Ini menonaktifkan pesan yang dikirim SQL Server kembali ke klien setelah pernyataan SELECT, INSERT, UPDATE, MERGE, dan DELETE dijalankan. Ini menjaga output yang dihasilkan minimal untuk kejelasan. Namun, tidak ada manfaat performa yang terukur pada perangkat keras saat ini. Untuk informasi, lihat MENGATUR NOCOUNT (Transact-SQL).
• Gunakan nama skema saat membuat atau mereferensikan objek database dalam prosedur. Dibutuhkan lebih sedikit waktu pemrosesan bagi Mesin Database untuk mengatasi nama objek jika tidak perlu mencari beberapa skema. Ini juga mencegah masalah izin dan akses yang disebabkan oleh skema default pengguna yang ditetapkan saat objek dibuat tanpa menentukan skema.
• Hindari fungsi pembungkusan di sekitar kolom yang ditentukan dalam klausa WHERE dan JOIN. Melakukannya membuat kolom tidak deterministik dan mencegah prosesor kueri menggunakan indeks.
• Hindari menggunakan fungsi skalar dalam pernyataan SELECT yang mengembalikan banyak baris data. Karena fungsi skalar harus diterapkan ke setiap baris, perilaku yang dihasilkan seperti pemrosesan berbasis baris dan menurunkan performa.
• Hindari penggunaan SELECT *. Sebagai gantinya, tentukan nama kolom yang diperlukan. Ini dapat mencegah beberapa kesalahan Mesin Database yang menghentikan eksekusi prosedur. Misalnya, SELECT * pernyataan yang mengembalikan data dari tabel kolom 12 lalu menyisipkan data tersebut ke dalam tabel sementara kolom 12 berhasil sampai jumlah atau urutan kolom di salah satu tabel diubah.
• Hindari memproses atau mengembalikan terlalu banyak data. Persempit hasil sedini mungkin dalam kode prosedur sehingga setiap operasi berikutnya yang dilakukan oleh prosedur dilakukan menggunakan himpunan data sekecil mungkin. Kirim hanya data penting ke aplikasi klien. Ini lebih efisien daripada mengirim data tambahan di seluruh jaringan dan memaksa aplikasi klien untuk bekerja melalui kumpulan hasil yang tidak perlu besar.
• Gunakan transaksi eksplisit dengan menggunakan BEGIN/COMMIT TRANSACTION dan jaga transaksi sesingkat mungkin. Transaksi yang lebih panjang berarti penguncian rekaman yang lebih lama dan potensi kebuntuan yang lebih besar.
• Gunakan Transact-SQL TRY... Fitur CATCH untuk penanganan kesalahan di dalam prosedur. COBA... CATCH dapat merangkum seluruh blok pernyataan Transact-SQL. Ini tidak hanya menciptakan overhead performa yang lebih sedikit, tetapi juga membuat pelaporan kesalahan lebih akurat dengan pemrograman yang jauh lebih sedikit.
• Gunakan kata kunci DEFAULT pada semua kolom tabel yang dirujuk oleh pernyataan CREATE TABLE atau ALTER TABLE Transact-SQL dalam isi prosedur. Ini mencegah meneruskan NULL ke kolom yang tidak memperbolehkan nilai null.
• Gunakan NULL atau NOT NULL untuk setiap kolom dalam tabel sementara. Opsi ANSI_DFLT_ON dan ANSI_DFLT_OFF mengontrol cara Mesin Database menetapkan atribut NULL atau NOT NULL ke kolom saat atribut ini tidak ditentukan dalam pernyataan CREATE TABLE atau ALTER TABLE. Jika koneksi menjalankan prosedur dengan pengaturan yang berbeda untuk opsi ini daripada koneksi yang membuat prosedur, kolom tabel yang dibuat untuk koneksi kedua dapat memiliki nullability yang berbeda dan menunjukkan perilaku yang berbeda. Jika NULL atau NOT NULL secara eksplisit dinyatakan untuk setiap kolom, tabel sementara dibuat dengan menggunakan nullability yang sama untuk semua koneksi yang menjalankan prosedur.
• Gunakan pernyataan modifikasi yang mengonversi null dan menyertakan logika yang menghilangkan baris dengan nilai null dari kueri. Ketahuilah bahwa dalam Transact-SQL, NULL bukan nilai kosong atau "tidak ada". Ini adalah tempat penampung untuk nilai yang tidak diketahui dan dapat menyebabkan perilaku tak terduga, terutama saat mengkueri kumpulan hasil atau menggunakan fungsi AGGREGATE.
• Gunakan operator UNION ALL alih-alih operator UNION atau OR, kecuali ada kebutuhan khusus untuk nilai yang berbeda. Operator UNION ALL memerlukan lebih sedikit overhead pemrosesan karena duplikat tidak difilter dari tataan hasil.

Keterangan

Tidak ada ukuran maksimum prosedur yang telah ditentukan sebelumnya.

Variabel yang ditentukan dalam prosedur dapat ditentukan pengguna atau variabel sistem, seperti @@SPID.

Ketika prosedur dijalankan untuk pertama kalinya, prosedur dikompilasi untuk menentukan rencana akses yang optimal untuk mengambil data. Eksekusi prosedur berikutnya dapat menggunakan kembali rencana yang sudah dihasilkan jika masih tetap berada dalam cache rencana Mesin Database.

Satu atau beberapa prosedur dapat dijalankan secara otomatis saat SQL Server dimulai. Prosedur harus dibuat oleh administrator sistem dalam master database dan dijalankan di bawah peran server tetap sysadmin sebagai proses latar belakang. Prosedur tidak dapat memiliki parameter input atau output apa pun. Untuk informasi selengkapnya, lihat Menjalankan Prosedur Tersimpan.

Prosedur ditumpuk ketika satu prosedur memanggil prosedur lain atau menjalankan kode terkelola dengan merujuk rutinitas CLR, jenis, atau agregat. Prosedur dan referensi kode terkelola dapat ditumpuk hingga 32 tingkat. Tingkat berlapis meningkat satu per satu ketika prosedur yang disebut atau referensi kode terkelola memulai eksekusi dan berkurang satu per satu ketika prosedur yang disebut atau referensi kode terkelola menyelesaikan eksekusi. Metode yang dipanggil dari dalam kode terkelola tidak dihitung terhadap batas tingkat berlapis. Namun, ketika prosedur tersimpan CLR melakukan operasi akses data melalui penyedia terkelola SQL Server, tingkat berlapis tambahan ditambahkan dalam transisi dari kode terkelola ke SQL.

Mencoba melebihi tingkat bersarang maksimum menyebabkan seluruh rantai panggilan gagal. Anda dapat menggunakan fungsi @@NESTLEVEL untuk mengembalikan tingkat bersarang dari eksekusi prosedur tersimpan saat ini.

Interoperabilitas

Mesin Database menyimpan pengaturan SET QUOTED_IDENTIFIER dan SET ANSI_NULLS saat prosedur Transact-SQL dibuat atau dimodifikasi. Pengaturan asli ini digunakan ketika prosedur dijalankan. Oleh karena itu, pengaturan sesi klien apa pun untuk SET QUOTED_IDENTIFIER dan SET ANSI_NULLS diabaikan saat prosedur berjalan.

Opsi SET lainnya, seperti SET ARITHABORT, SET ANSI_WARNINGS, atau SET ANSI_PADDINGS tidak disimpan saat prosedur dibuat atau dimodifikasi. Jika logika prosedur tergantung pada pengaturan tertentu, sertakan pernyataan SET di awal prosedur untuk menjamin pengaturan yang sesuai. Ketika pernyataan SET dijalankan dari prosedur, pengaturan tetap berlaku hanya sampai prosedur selesai berjalan. Pengaturan kemudian dipulihkan ke nilai yang dimiliki prosedur ketika dipanggil. Ini memungkinkan klien individual untuk mengatur opsi yang mereka inginkan tanpa memengaruhi logika prosedur.

Setiap pernyataan SET dapat ditentukan di dalam prosedur, kecuali SET SHOWPLAN_TEXT dan SET SHOWPLAN_ALL. Ini harus menjadi satu-satunya pernyataan dalam batch. Opsi SET yang dipilih tetap berlaku selama eksekusi prosedur lalu kembali ke pengaturan sebelumnya.

Catatan

SET ANSI_WARNINGS tidak dihormati saat meneruskan parameter dalam prosedur, fungsi yang ditentukan pengguna, atau saat mendeklarasikan dan mengatur variabel dalam pernyataan batch. Misalnya, jika variabel didefinisikan sebagai karakter(3), lalu diatur ke nilai yang lebih besar dari tiga karakter, data dipotong ke ukuran yang ditentukan dan pernyataan INSERT atau UPDATE berhasil.

Pembatasan dan batasan

Pernyataan CREATE PROCEDURE tidak dapat dikombinasikan dengan pernyataan Transact-SQL lainnya dalam satu batch.

Pernyataan berikut tidak dapat digunakan di mana saja dalam isi prosedur tersimpan.

CREATE	SET	USE
CREATE AGGREGATE	SET SHOWPLAN_TEXT	GUNAKAN database_name
BUAT DEFAULT	SET SHOWPLAN_XML
BUAT ATURAN	ATUR PARSEONLY
CREATE SCHEMA	SET SHOWPLAN_ALL
BUAT atau UBAH PEMICU
CREATE atau ALTER FUNCTION
BUAT atau UBAH PROSEDUR
BUAT atau UBAH TAMPILAN

Prosedur dapat mereferensikan tabel yang belum ada. Pada waktu pembuatan, hanya pemeriksaan sintaksis yang dilakukan. Prosedur ini tidak dikompilasi sampai dijalankan untuk pertama kalinya. Hanya selama kompilasi, semua objek yang dirujuk dalam prosedur diselesaikan. Oleh karena itu, prosedur yang benar secara sintetis yang mereferensikan tabel yang tidak ada dapat berhasil dibuat; namun, prosedur gagal pada waktu eksekusi jika tabel yang dirujuk tidak ada.

Anda tidak dapat menentukan nama fungsi sebagai nilai default parameter atau sebagai nilai yang diteruskan ke parameter saat menjalankan prosedur. Namun, Anda dapat meneruskan fungsi sebagai variabel seperti yang ditunjukkan dalam contoh berikut.

-- Passing the function value as a variable.
DECLARE @CheckDate DATETIME = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

Jika prosedur membuat perubahan pada instans jarak jauh SQL Server, perubahan tidak dapat digulung balik. Prosedur jarak jauh tidak mengambil bagian dalam transaksi.

Agar Mesin Database mereferensikan metode yang benar ketika kelebihan beban dalam .NET Framework, metode yang ditentukan dalam klausul NAMA EKSTERNAL harus memiliki karakteristik berikut:

• Dinyatakan sebagai metode statis.
• Terima jumlah parameter yang sama dengan jumlah parameter prosedur.
• Gunakan jenis parameter yang kompatibel dengan jenis data parameter yang sesuai dari prosedur SQL Server. Untuk informasi tentang mencocokkan jenis data SQL Server dengan jenis data .NET Framework, lihat Memetakan Data Parameter CLR.

Metadata

Tabel berikut mencantumkan tampilan katalog dan tampilan manajemen dinamis yang bisa Anda gunakan untuk mengembalikan informasi tentang prosedur tersimpan.

Tampilan	Deskripsi
sys.sql_modules	Mengembalikan definisi prosedur Transact-SQL. Teks prosedur yang dibuat dengan opsi ENKRIPSI tidak dapat dilihat dengan menggunakan sys.sql_modules tampilan katalog.
sys.assembly_modules	Mengembalikan informasi tentang prosedur CLR.
sys.parameters	Mengembalikan informasi tentang parameter yang ditentukan dalam prosedur
sys.sql_expression_dependensies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities	Mengembalikan objek yang dirujuk oleh prosedur.

Untuk memperkirakan ukuran prosedur yang dikompilasi, gunakan Penghitung Monitor Performa berikut.

Nama objek Monitor Performa	Nama Penghitung Monitor Performa
SQLServer: Rencanakan Objek Cache	Rasio Hit singgahan
	Halaman Cache
	Jumlah Objek Cache 1

¹ Penghitung ini tersedia untuk berbagai kategori objek cache termasuk ad hoc Transact-SQL, Transact-SQL yang disiapkan, prosedur, pemicu, dan sebagainya. Untuk informasi selengkapnya, lihat SQL Server, Merencanakan Objek Cache.

Izin

CREATE PROCEDURE Memerlukan izin dalam database dan ALTER izin pada skema di mana prosedur sedang dibuat, atau memerlukan keanggotaan dalam peran database tetap db_ddladmin.

Untuk prosedur tersimpan CLR, memerlukan kepemilikan rakitan yang dirujuk dalam klausa NAMA EKSTERNAL, atau REFERENCES izin pada rakitan tersebut.

MEMBUAT TABEL PROSEDUR dan memori yang dioptimalkan

Tabel yang dioptimalkan memori dapat diakses melalui prosedur tersimpan tradisional dan terkompilasi asli. Prosedur asli dalam banyak kasus dengan cara yang lebih efisien. Untuk informasi selengkapnya, lihat Prosedur Tersimpan yang Dikompilasi Secara Asli.

Contoh berikut menunjukkan cara membuat prosedur tersimpan yang dikompilasi secara asli yang mengakses tabel dbo.Departmentsyang dioptimalkan memori :

CREATE PROCEDURE dbo.usp_add_kitchen @dept_id INT, @kitchen_count INT NOT NULL
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

UPDATE dbo.Departments
SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count
WHERE ID = @dept_id
END;
GO

Prosedur yang dibuat tanpa NATIVE_COMPILATION tidak dapat diubah ke prosedur tersimpan yang dikompilasi secara asli.

Untuk diskusi kemampuan pemrograman dalam prosedur tersimpan yang dikompilasi secara asli, area permukaan kueri yang didukung, dan operator lihat Fitur yang Didukung untuk Modul T-SQL yang Dikompilasi Secara Asli.

Contoh

Kategori	Elemen sintaksis unggulan
Sintaks Dasar	CREATE PROCEDURE
Meneruskan parameter	@parameter = default HASIL jenis parameter bernilai tabel KURSOR BERVARIASI
Memodifikasi data dengan menggunakan prosedur tersimpan	UPDATE
Penanganan Kesalahan	TRY...CATCH
Mengaburkan definisi prosedur	DENGAN ENKRIPSI
Memaksa Prosedur untuk Kompilasi Ulang	DENGAN KOMPILASI ULANG
Mengatur Konteks Keamanan	JALANKAN SEBAGAI

Sintaks dasar

Contoh di bagian ini menunjukkan fungsionalitas dasar pernyataan CREATE PROCEDURE menggunakan sintaks minimum yang diperlukan.

J. Membuat prosedur Transact-SQL

Contoh berikut membuat prosedur tersimpan yang mengembalikan semua karyawan (nama depan dan belakang yang disediakan), jabatan pekerjaan mereka, dan nama departemen mereka dari tampilan dalam database AdventureWorks2022. Prosedur ini tidak menggunakan parameter apa pun. Contohnya kemudian menunjukkan tiga metode untuk menjalankan prosedur.

CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

SELECT * FROM HumanResources.vEmployeeDepartment;

Prosedur uspGetEmployees dapat dijalankan dengan cara berikut:

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B. Mengembalikan lebih dari satu tataan hasil

Prosedur berikut mengembalikan dua tataan hasil.

CREATE PROCEDURE dbo.uspMultipleResults
AS
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;
GO

C. Membuat prosedur tersimpan CLR

Contoh berikut membuat GetPhotoFromDB prosedur yang mereferensikan GetPhotoFromDB metode LargeObjectBinary kelas dalam HandlingLOBUsingCLR rakitan. Sebelum prosedur dibuat, HandlingLOBUsingCLR rakitan terdaftar di database lokal.

Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru, SQL Database (jika menggunakan rakitan yang dibuat dari assembly_bits.

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID INT
    , @CurrentDirectory NVARCHAR(1024)
    , @FileName NVARCHAR(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

Meneruskan parameter

Contoh di bagian ini menunjukkan cara menggunakan parameter input dan output untuk meneruskan nilai ke dan dari prosedur tersimpan.

D. Membuat prosedur dengan parameter input

Contoh berikut membuat prosedur tersimpan yang mengembalikan informasi untuk karyawan tertentu dengan meneruskan nilai untuk nama depan dan nama belakang karyawan. Prosedur ini hanya menerima kecocokan yang tepat untuk parameter yang diteruskan.

IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
    @LastName NVARCHAR(50),
    @FirstName NVARCHAR(50)
AS

    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

Prosedur uspGetEmployees dapat dijalankan dengan cara berikut:

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

E. Menggunakan prosedur dengan parameter kartubebas

Contoh berikut membuat prosedur tersimpan yang mengembalikan informasi untuk karyawan dengan meneruskan nilai penuh atau parsial untuk nama depan dan nama belakang karyawan. Pola prosedur ini cocok dengan parameter yang diteruskan atau, jika tidak disediakan, menggunakan default prasetel (nama belakang yang dimulai dengan huruf D).

IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2
    @LastName NVARCHAR(50) = N'D%',
    @FirstName NVARCHAR(50) = N'%'
AS
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;

Prosedur uspGetEmployees2 ini dapat dijalankan dalam banyak kombinasi. Hanya beberapa kemungkinan kombinasi yang ditampilkan di sini.

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

F. Menggunakan parameter OUTPUT

Contoh berikut membuat uspGetList prosedur. Prosedur ini mengembalikan daftar produk yang memiliki harga yang tidak melebihi jumlah yang ditentukan. Contoh menunjukkan penggunaan beberapa SELECT pernyataan dan beberapa OUTPUT parameter. Parameter OUTPUT mengaktifkan prosedur eksternal, batch, atau lebih dari satu pernyataan Transact-SQL untuk mengakses nilai yang ditetapkan selama eksekusi prosedur.

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product VARCHAR(40)
    , @MaxPrice MONEY
    , @ComparePrice MONEY OUTPUT
    , @ListPrice MONEY OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Jalankan uspGetList untuk mengembalikan daftar produk Adventure Works (Sepeda) yang harganya kurang dari $700. Parameter OUTPUT @Cost dan @ComparePrices digunakan dengan bahasa kontrol aliran untuk mengembalikan pesan di jendela Pesan .

Catatan

Variabel OUTPUT harus didefinisikan ketika prosedur dibuat dan juga ketika variabel digunakan. Nama parameter dan nama variabel tidak harus cocok; namun, jenis data dan penempatan parameter harus cocok, kecuali @ListPrice = variabel digunakan.

DECLARE @ComparePrice MONEY, @Cost MONEY;
EXECUTE Production.uspGetList '%Bikes%', 700,
    @ComparePrice OUT,
    @Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
    PRINT 'These products can be purchased for less than
    $'+RTRIM(CAST(@ComparePrice AS VARCHAR(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed
    $'+ RTRIM(CAST(@ComparePrice AS VARCHAR(20)))+'.';

Berikut adalah kumpulan hasil parsial:

Product                     List Price
--------------------------  ----------
Road-750 Black, 58          539.99
Mountain-500 Silver, 40     564.99
Mountain-500 Silver, 42     564.99
...
Road-750 Black, 48          539.99
Road-750 Black, 52          539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

G. Menggunakan parameter bernilai tabel

Contoh berikut menggunakan jenis parameter bernilai tabel untuk menyisipkan beberapa baris ke dalam tabel. Contoh membuat jenis parameter, mendeklarasikan variabel tabel untuk mereferensikannya, mengisi daftar parameter, lalu meneruskan nilai ke prosedur tersimpan. Prosedur tersimpan menggunakan nilai untuk menyisipkan beberapa baris ke dalam tabel.

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2022].[Production].[Location]
       ([Name]
       , [CostRate]
       , [Availability]
       , [ModifiedDate])
    SELECT *, 0, GETDATE()
    FROM @TVP;
GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM
    [AdventureWorks2022].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

H. Menggunakan parameter kursor OUTPUT

Contoh berikut menggunakan parameter kursor OUTPUT untuk meneruskan kursor yang bersifat lokal ke prosedur kembali ke batch, prosedur, atau pemicu panggilan.

Pertama, buat prosedur yang mendeklarasikan lalu buka kursor pada Currency tabel:

CREATE PROCEDURE dbo.uspCurrencyCursor
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

Selanjutnya, jalankan batch yang mendeklarasikan variabel kursor lokal, jalankan prosedur untuk menetapkan kursor ke variabel lokal, lalu mengambil baris dari kursor.

DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
    FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

Mengubah data dengan menggunakan prosedur tersimpan

Contoh di bagian ini menunjukkan cara menyisipkan atau memodifikasi data dalam tabel atau tampilan dengan menyertakan pernyataan Bahasa Manipulasi Data (DML) dalam definisi prosedur.

I. Menggunakan UPDATE dalam prosedur tersimpan

Contoh berikut menggunakan pernyataan UPDATE dalam prosedur tersimpan. Prosedur ini mengambil satu parameter input, @NewHours dan satu parameter @RowCountoutput . Nilai @NewHours parameter digunakan dalam pernyataan UPDATE untuk memperbarui kolom VacationHours dalam tabel HumanResources.Employee. Parameter @RowCount output digunakan untuk mengembalikan jumlah baris yang terpengaruh ke variabel lokal. Ekspresi CASE digunakan dalam klausul SET untuk menentukan nilai yang diatur secara kondisional untuk VacationHours. Ketika karyawan dibayar per jam (SalariedFlag = 0), VacationHours diatur ke jumlah jam saat ini ditambah nilai yang ditentukan dalam @NewHours; jika tidak, VacationHours diatur ke nilai yang ditentukan dalam @NewHours.

CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours SMALLINT, @Rowcount INT OUTPUT
AS
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours =
    ( CASE
        WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
        ELSE @NewHours
        END
    )
WHERE CurrentFlag = 1;
SET @Rowcount = @@rowcount;

GO
DECLARE @Rowcount INT
EXEC HumanResources.Update_VacationHours 40, @Rowcount OUTPUT
PRINT @Rowcount;

Penanganan kesalahan

Contoh di bagian ini menunjukkan metode untuk menangani kesalahan yang mungkin terjadi ketika prosedur tersimpan dijalankan.

j. Gunakan TRY... MENANGKAP

Contoh berikut menggunakan TRY... Konstruksi CATCH untuk mengembalikan informasi kesalahan yang tertangkap selama eksekusi prosedur tersimpan.

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID INT )
AS
SET NOCOUNT ON;
BEGIN TRY
  BEGIN TRANSACTION
  -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
    DELETE FROM Production.WorkOrderRouting
    WHERE WorkOrderID = @WorkOrderID;
  -- Delete the rows from the parent table, WorkOrder, for the specified work order.
    DELETE FROM Production.WorkOrder
    WHERE WorkOrderID = @WorkOrderID;
  COMMIT
END TRY

BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
    ROLLBACK

  -- Return the error information.
  DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

GO
EXEC Production.uspDeleteWorkOrder 13;
GO
/* Intentionally generate an error by reversing the order in which rows
   are deleted from the parent and child tables. This change does not
   cause an error when the procedure definition is altered, but produces
   an error when the procedure is executed.
*/
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID INT )
AS

BEGIN TRY
  BEGIN TRANSACTION
  -- Delete the rows from the parent table, WorkOrder, for the specified work order.
    DELETE FROM Production.WorkOrder
    WHERE WorkOrderID = @WorkOrderID;

  -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
    DELETE FROM Production.WorkOrderRouting
    WHERE WorkOrderID = @WorkOrderID;
  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION

  -- Return the error information.
  DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Execute the altered procedure.
EXEC Production.uspDeleteWorkOrder 15;
GO
DROP PROCEDURE Production.uspDeleteWorkOrder;

Mengaburkan definisi prosedur

Contoh di bagian ini menunjukkan cara mengaburkan definisi prosedur tersimpan.

K. Gunakan opsi WITH ENCRYPTION

Contoh berikut membuat HumanResources.uspEncryptThis prosedur.

Berlaku untuk: SQL Server 2008 (10.0.x) dan yang lebih baru, dan Azure SQL Database.

CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber,
        VacationHours, SickLeaveHours
    FROM HumanResources.Employee;
GO

Opsi WITH ENCRYPTION mengaburkan definisi prosedur saat mengkueri katalog sistem atau menggunakan fungsi metadata, seperti yang ditunjukkan oleh contoh berikut.

Jalankan sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Berikut set hasilnya.

The text for object 'HumanResources.uspEncryptThis' is encrypted.

Langsung mengkueri sys.sql_modules tampilan katalog:

SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

Berikut set hasilnya.

definition
--------------------------------
NULL

Catatan

Prosedur sp_helptext tersimpan sistem tidak didukung di Azure Synapse Analytics. Sebagai gantinya sys.sql_modules , gunakan tampilan katalog objek.

Paksa prosedur untuk kompilasi ulang

Contoh di bagian ini menggunakan klausa WITH RECOMPILE untuk memaksa prosedur kompilasi ulang setiap kali dijalankan.

L. Gunakan opsi WITH RECOMPILE

Klausul WITH RECOMPILE ini berguna ketika parameter yang disediakan ke prosedur tidak khas, dan ketika rencana eksekusi baru tidak boleh di-cache atau disimpan dalam memori.

IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name VARCHAR(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v
    JOIN Purchasing.ProductVendor AS pv
      ON v.BusinessEntityID = pv.BusinessEntityID
    JOIN Production.Product AS p
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;

Mengatur konteks keamanan

Contoh di bagian ini menggunakan klausul EXECUTE AS untuk mengatur konteks keamanan tempat prosedur tersimpan dijalankan.

M. Menggunakan klausa EXECUTE AS

Contoh berikut menunjukkan penggunaan klausul EXECUTE AS untuk menentukan konteks keamanan di mana prosedur dapat dijalankan. Dalam contoh, opsi CALLER menentukan bahwa prosedur dapat dijalankan dalam konteks pengguna yang memanggilnya.

CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name',
      v.CreditRating AS 'Rating',
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID
    ORDER BY v.Name ASC;
GO

N. Membuat set izin kustom

Contoh berikut menggunakan EXECUTE AS untuk membuat izin kustom untuk operasi database. Beberapa operasi seperti TRUNCATE TABLE, tidak memiliki izin yang dapat diberikan. Dengan menggabungkan pernyataan TRUNCATE TABLE dalam prosedur tersimpan dan menentukan prosedur tersebut dijalankan sebagai pengguna yang memiliki izin untuk mengubah tabel, Anda dapat memperluas izin untuk memotong tabel kepada pengguna yang Anda berikan izin EXECUTE pada prosedur.

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

O. Membuat prosedur tersimpan yang menjalankan pernyataan SELECT

Contoh ini memperlihatkan sintaks dasar untuk membuat dan menjalankan prosedur. Saat menjalankan batch, CREATE PROCEDURE harus menjadi pernyataan pertama. Misalnya, untuk membuat prosedur tersimpan berikut di AdventureWorksPDW2022, atur konteks database terlebih dahulu, lalu jalankan pernyataan CREATE PROCEDURE.

-- Uses AdventureWorksDW database

--Run CREATE PROCEDURE as the first statement in a batch.
CREATE PROCEDURE Get10TopResellers
AS
BEGIN
    SELECT TOP (10) r.ResellerName, r.AnnualSales
    FROM DimReseller AS r
    ORDER BY AnnualSales DESC, ResellerName ASC;
END
;
GO

--Show 10 Top Resellers
EXEC Get10TopResellers;

Lihat juga

• ALTER PROCEDURE (Transact-SQL)
• Bahasa Kontrol Aliran (Transact-SQL)
• Kursor
• Tipe Data (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• DROP PROCEDURE (Transact-SQL)
• EXECUTE (Transact-SQL)
• EXECUTE AS (T-SQL)
• Prosedur Tersimpan (Mesin Database)
• sp_procoption (T-SQL)
• sp_recompile (T-SQL)
• sys.sql_modules (Transact-SQL)
• sys.parameters (Transact-SQL)
• sys.procedures (Transact-SQL)
• sys.sql_expression_dependencies (Transact-SQL)
• sys.assembly_modules (T-SQL)
• sys.numbered_procedures (T-SQL)
• sys.numbered_procedure_parameters (T-SQL)
• OBJECT_DEFINITION (T-SQL)
• Membuat Prosedur Tersimpan
• Gunakan Parameter Bernilai Tabel (Mesin Database)
• sys.dm_sql_referenced_entities (T-SQL)
• sys.dm_sql_referencing_entities (T-SQL)