Menjalankan prosedur tersimpan

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

Artikel ini menjelaskan cara menjalankan prosedur tersimpan di SQL Server dengan menggunakan SQL Server Management Studio atau Transact-SQL.

Ada berbagai cara untuk menjalankan prosedur tersimpan. Pendekatan pertama dan paling umum adalah bagi aplikasi atau pengguna untuk memanggil prosedur. Pendekatan lain adalah mengatur prosedur tersimpan untuk berjalan secara otomatis saat instans SQL Server dimulai.

Ketika prosedur dipanggil oleh aplikasi atau pengguna, kata kunci TRANSACT-SQL EXECUTE atau EXEC secara eksplisit dinyatakan dalam panggilan. Prosedur ini dapat dipanggil dan dijalankan tanpa kata kunci EXEC jika prosedur adalah pernyataan pertama dalam batch Transact-SQL.

Pembatasan dan batasan

Kolase database panggilan digunakan saat mencocokkan nama prosedur sistem. Untuk alasan ini, selalu gunakan kasus nama prosedur sistem yang tepat dalam panggilan prosedur. Misalnya, kode ini gagal jika dijalankan dalam konteks database yang memiliki kolatasi peka huruf besar/kecil:

EXEC SP_heLP; -- Fails to resolve because SP_heLP doesn't equal sp_help

Untuk menampilkan nama prosedur sistem yang tepat, kueri tampilan katalog sys.system_objects dan sys.system_parameters .

Jika prosedur yang ditentukan pengguna memiliki nama yang sama dengan prosedur sistem, prosedur yang ditentukan pengguna mungkin tidak pernah dijalankan.

Rekomendasi

Gunakan rekomendasi berikut untuk menjalankan prosedur tersimpan.

Prosedur tersimpan sistem

Prosedur sistem dimulai dengan awalan sp_. Karena secara logis muncul di semua database yang ditentukan pengguna dan sistem, prosedur sistem dapat dijalankan dari database apa pun tanpa harus sepenuhnya memenuhi syarat nama prosedur. Namun, yang terbaik adalah memenuhi syarat skema semua nama prosedur sistem dengan sys nama skema untuk mencegah konflik nama. Contoh berikut menunjukkan metode yang direkomendasikan untuk memanggil prosedur sistem.

EXEC sys.sp_who;

Prosedur tersimpan yang ditentukan pengguna

Saat menjalankan prosedur yang ditentukan pengguna, yang terbaik adalah memenuhi syarat nama prosedur dengan nama skema. Praktik ini memberikan peningkatan performa kecil karena Mesin Database tidak perlu mencari beberapa skema. Menggunakan nama skema juga mencegah menjalankan prosedur yang salah jika database memiliki prosedur dengan nama yang sama dalam beberapa skema.

Contoh berikut menunjukkan metode yang direkomendasikan untuk menjalankan prosedur yang ditentukan pengguna. Prosedur ini menerima dua parameter input. Untuk informasi tentang menentukan parameter input dan output, lihat Menentukan parameter dalam prosedur tersimpan.

EXECUTE SalesLT.uspGetCustomerCompany @LastName = N'Cannon', @FirstName = N'Chris';
GO

Atau:

EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
GO

Jika prosedur yang ditentukan pengguna yang tidak memenuhi syarat ditentukan, Mesin Database mencari prosedur dalam urutan berikut:

  1. sys Skema database saat ini.

  2. Skema default pemanggil jika prosedur dijalankan dalam batch atau di SQL dinamis. Jika nama prosedur yang tidak memenuhi syarat muncul di dalam isi definisi prosedur lain, skema yang berisi prosedur lain ini akan dicari berikutnya.

  3. dbo Skema dalam database saat ini.

Keamanan

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

Izin

Untuk informasi izin, lihat Izin di EXECUTE (Transact-SQL).

Eksekusi prosedur tersimpan

Anda dapat menggunakan antarmuka pengguna SQL Server Management Studio (SSMS) atau Transact-SQL di jendela kueri SSMS untuk menjalankan prosedur tersimpan. Selalu gunakan versi terbaru SSMS.

Menggunakan SQL Server Management Studio

  1. Di Object Explorer, sambungkan ke instans SQL Server atau Azure SQL Database, perluas instans tersebut, lalu perluas Database.

  2. Perluas database yang Anda inginkan, perluas Keterprograman, lalu perluas Prosedur Tersimpan.

  3. Klik kanan prosedur tersimpan yang ingin Anda jalankan dan pilih Jalankan Prosedur Tersimpan.

  4. Dalam kotak dialog Jalankan Prosedur , Parameter menunjukkan nama setiap parameter, Jenis Data menunjukkan jenis datanya, dan Parameter Output menunjukkan apakah itu parameter output.

    Untuk setiap parameter:

    • Di bawah Nilai, ketik nilai yang akan digunakan untuk parameter .
    • Di bawah Teruskan Nilai Null, pilih apakah akan meneruskan NULL sebagai nilai parameter.

  5. Pilih OK untuk menjalankan prosedur tersimpan. Jika prosedur tersimpan tidak memiliki parameter apa pun, cukup pilih OK.

    Prosedur tersimpan berjalan, dan hasilnya muncul di panel Hasil .

    Misalnya, untuk menjalankan SalesLT.uspGetCustomerCompany prosedur tersimpan dari artikel Buat prosedur tersimpan, masukkan Meriam untuk parameter @LastName dan Chris untuk parameter @FirstName, dan pilih OK. Prosedur ini mengembalikan FirstNameChris, LastNameCannon, dan CompanyNameOutdoor Sporting Goods.

Menggunakan Transact-SQL di jendela kueri

  1. Di SQL Server, sambungkan ke instans SQL Server atau Azure SQL Database.

  2. Dari toolbar, pilih Kueri Baru.

  3. Masukkan pernyataan EXECUTE dengan sintaks berikut ke dalam jendela kueri, yang menyediakan nilai untuk semua parameter yang diharapkan:

    EXECUTE <ProcedureName> N'<Parameter 1 value>, N'<Parameter x value>;  
GO

    Misalnya, pernyataan Transact-SQL berikut menjalankan uspGetCustomerCompany prosedur tersimpan dan dengan Cannon sebagai @LastName nilai parameter dan Chris sebagai @FirstName nilai parameter:

    EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
GO

  4. Dari toolbar, pilih Jalankan. Prosedur tersimpan berjalan.

Opsi untuk nilai parameter

Ada beberapa cara untuk memberikan parameter dan nilai dalam pernyataan EXECUTE prosedur tersimpan. Contoh berikut menunjukkan beberapa opsi berbeda untuk pernyataan EXECUTE.

  • Jika Anda memberikan nilai parameter dalam urutan yang sama seperti yang ditentukan dalam prosedur tersimpan, Anda tidak perlu menyatakan nama parameter. Contohnya:

    EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';

  • Jika Anda memberikan nama parameter dalam @parameter_name=value pola, Anda tidak perlu menentukan nama parameter dan nilai dalam urutan yang sama seperti yang ditentukan. Misalnya, salah satu pernyataan berikut valid:

    EXEC SalesLT.uspGetCustomerCompany @FirstName = N'Chris', @LastName = N'Cannon';

    atau:

    EXEC SalesLT.uspGetCustomerCompany @LastName = N'Cannon', @FirstName = N'Chris';

  • Jika Anda menggunakan @parameter_name=value formulir untuk parameter apa pun, Anda harus menggunakannya untuk semua parameter berikutnya dalam pernyataan tersebut. Misalnya, Anda tidak dapat menggunakan EXEC SalesLT.uspGetCustomerCompany1 @FirstName = N'Chris', N'Cannon';.

Eksekusi otomatis saat startup

Berlaku untuk: SQL Server

Di SQL Server, anggota sysadmin peran server dapat menggunakan sp_procoption untuk mengatur atau menghapus prosedur untuk eksekusi otomatis saat startup. Prosedur startup harus berada dalam master database, harus dimiliki oleh sa, dan tidak dapat memiliki parameter input atau output. Untuk informasi selengkapnya, lihat sp_procoption (Transact-SQL).

Prosedur yang ditandai untuk eksekusi otomatis saat startup dijalankan setiap kali SQL Server dimulai dan database dipulihkan selama proses startup tersebut master . Menyiapkan prosedur untuk dijalankan secara otomatis dapat berguna untuk melakukan operasi pemeliharaan database atau agar prosedur berjalan terus menerus sebagai proses latar belakang.

Penggunaan lain untuk eksekusi otomatis adalah agar prosedur melakukan tugas sistem atau pemeliharaan di tempdb, seperti membuat tabel sementara global. Eksekusi otomatis memastikan bahwa tabel sementara seperti itu selalu ada ketika tempdb dibuat ulang selama startup SQL Server.

Prosedur yang dijalankan secara otomatis beroperasi dengan izin yang sama dengan anggota sysadmin peran server tetap. Setiap pesan kesalahan yang dihasilkan oleh prosedur menulis ke log kesalahan SQL Server.

Tidak ada batasan jumlah prosedur startup yang dapat Anda miliki, tetapi setiap prosedur startup menggunakan satu utas pekerja saat mengeksekusi. Jika Anda perlu menjalankan beberapa prosedur saat startup tetapi tidak perlu menjalankannya secara paralel, buat satu prosedur prosedur startup dan minta prosedur tersebut memanggil prosedur lain. Metode ini hanya menggunakan satu utas pekerja.

Tip

Jangan mengembalikan kumpulan hasil apa pun dari prosedur yang dijalankan secara otomatis. Karena prosedur sedang dijalankan oleh SQL Server alih-alih aplikasi atau pengguna, tidak ada tempat untuk set hasil yang akan dijalankan.

Catatan

Azure SQL Database dirancang untuk mengisolasi fitur dari dependensi pada master database. Dengan demikian, pernyataan Transact-SQL yang mengonfigurasi opsi tingkat server tidak tersedia di Azure SQL. Anda sering dapat menemukan alternatif yang sesuai dari layanan Azure lainnya seperti pekerjaan Elastic atau Azure Automation.

Mengatur prosedur untuk dijalankan secara otomatis saat startup

Hanya administrator sistem (sa) yang dapat menandai prosedur untuk dijalankan secara otomatis.

  1. Di SSMS, sambungkan ke Mesin Database.

  2. Dari toolbar Standar, pilih Kueri Baru.

  3. Masukkan perintah sp_procoption berikut untuk mengatur prosedur tersimpan agar dijalankan secara otomatis di startup SQL Server.

    EXEC sp_procoption @ProcName = N'<stored procedure name>'   
    , @OptionName = 'startup'   
    , @OptionValue = 'on';
GO

  4. Di toolbar, pilih Jalankan.

Menghentikan prosedur agar tidak dijalankan secara otomatis saat startup

dapat sysadmin menggunakan sp_procoption untuk menghentikan prosedur agar tidak dijalankan secara otomatis di startup SQL Server.

  1. Di SSMS, sambungkan ke Mesin Database.

  2. Dari toolbar Standar, pilih Kueri Baru.

  3. Masukkan perintah berikut ke dalam jendela kueri.

    EXEC sp_procoption @ProcName = N'<stored procedure name>'      
    , @OptionName = 'startup'
    , @OptionValue = 'off';
GO

  4. Di toolbar, pilih Jalankan.

Konten terkait