Share via

Mengembalikan data dari prosedur tersimpan

  • Artikel

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

Ada tiga cara mengembalikan data dari prosedur ke program panggilan: kumpulan hasil, parameter output, dan kode pengembalian. Artikel ini menyediakan informasi tentang tiga pendekatan.

Mengembalikan data menggunakan tataan hasil

Jika Anda menyertakan pernyataan SELECT dalam isi prosedur tersimpan (tetapi bukan SELECT ... INTO atau INSERT ... SELECT), baris yang ditentukan oleh pernyataan SELECT akan dikirim langsung ke klien. Untuk kumpulan hasil besar, eksekusi prosedur tersimpan tidak akan berlanjut ke pernyataan berikutnya sampai kumpulan hasil telah sepenuhnya dikirim ke klien. Untuk kumpulan hasil kecil, hasilnya ditampung untuk kembali ke klien dan eksekusi akan berlanjut. Jika beberapa pernyataan SELECT tersebut dijalankan selama eksekusi prosedur tersimpan, beberapa set hasil akan dikirim ke klien. Perilaku ini juga berlaku untuk batch Transact-SQL berlapis, prosedur tersimpan berlapis, dan batch Transact-SQL tingkat atas.

Contoh mengembalikan data menggunakan tataan hasil

Contoh berikut menggunakan AdventureWorks2022database sampel. Contoh ini memperlihatkan prosedur tersimpan LastName yang mengembalikan nilai dan SalesYTD untuk semua SalesPerson baris yang juga muncul dalam vEmployee tampilan.

USE AdventureWorks2022;  
GO

IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL  
   DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;  
GO  

CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD  
AS    
   SET NOCOUNT ON;

   SELECT LastName, SalesYTD  
   FROM Sales.SalesPerson AS sp  
   JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID;
   
   RETURN;
GO

Mengembalikan data menggunakan parameter output

Jika Anda menentukan kata kunci output untuk parameter dalam definisi prosedur, prosedur dapat mengembalikan nilai parameter saat ini ke program panggilan saat prosedur keluar. Untuk menyimpan nilai parameter dalam variabel yang dapat digunakan dalam program panggilan, program panggilan harus menggunakan kata kunci output saat menjalankan prosedur. Untuk informasi selengkapnya tentang jenis data apa yang dapat digunakan sebagai parameter output, lihat CREATE PROCEDURE (Transact-SQL).

Contoh parameter output

Contoh berikut menunjukkan prosedur dengan parameter input dan output. Parameter @SalesPerson akan menerima nilai input yang ditentukan oleh program panggilan. Pernyataan SELECT menggunakan nilai yang diteruskan ke parameter input untuk mendapatkan nilai yang benar SalesYTD . Pernyataan SELECT juga menetapkan nilai ke @SalesYTD parameter output, yang mengembalikan nilai ke program panggilan saat prosedur keluar.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;  
GO  
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD  
    @SalesPerson nvarchar(50),  
    @SalesYTD money OUTPUT  
AS    
  
    SET NOCOUNT ON;

    SELECT @SalesYTD = SalesYTD  
    FROM Sales.SalesPerson AS sp  
    JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
    WHERE LastName = @SalesPerson;

    RETURN;
GO

Contoh berikut memanggil prosedur yang dibuat dalam contoh pertama dan menyimpan parameter @SalesYTD output yang dikembalikan dari prosedur yang disebut ke dalam @SalesYTDBySalesPerson variabel.

Contohnya:

  • Menyatakan variabel @SalesYTDBySalesPerson untuk menerima nilai output prosedur.
  • Menjalankan prosedur yang Sales.uspGetEmployeeSalesYTD menentukan nama belakang untuk parameter input. Simpan nilai output dalam variabel @SalesYTDBySalesPerson.
  • Memanggil PRINT untuk menampilkan nilai yang disimpan ke @SalesYTDBySalesPerson.
DECLARE @SalesYTDBySalesPerson money;

EXECUTE Sales.uspGetEmployeeSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;  

PRINT 'Year-to-date sales for this employee is ' +   
    CONVERT(varchar(10),@SalesYTDBySalesPerson);  
GO

Nilai input juga dapat ditentukan untuk parameter output saat prosedur dijalankan. Ini memungkinkan prosedur untuk menerima nilai dari program panggilan, mengubah atau melakukan operasi dengan nilai , lalu mengembalikan nilai baru ke program panggilan. Dalam contoh sebelumnya, @SalesYTDBySalesPerson variabel dapat diberi nilai sebelum program memanggil Sales.uspGetEmployeeSalesYTD prosedur. Pernyataan eksekusi akan meneruskan @SalesYTDBySalesPerson nilai variabel ke @SalesYTD dalam parameter output. Kemudian dalam isi prosedur, nilai dapat digunakan untuk perhitungan yang menghasilkan nilai baru. Nilai baru akan diteruskan kembali dari prosedur melalui parameter output, memperbarui nilai dalam @SalesYTDBySalesPerson variabel ketika prosedur keluar. Ini sering disebut sebagai "kemampuan referensi pass-by-."

Jika Anda menentukan output untuk parameter saat memanggil prosedur dan parameter tersebut tidak ditentukan dengan menggunakan output dalam definisi prosedur, Anda mendapatkan pesan kesalahan. Namun, Anda dapat menjalankan prosedur dengan parameter output dan tidak menentukan output saat menjalankan prosedur. Tidak ada kesalahan yang dikembalikan, tetapi Anda tidak dapat menggunakan nilai output dalam program panggilan.

Menggunakan jenis data kursor dalam parameter output

Prosedur T-SQL dapat menggunakan jenis data kursor hanya untuk parameter output. Jika jenis data kursor ditentukan untuk parameter, kata kunci yang bervariasi dan output harus ditentukan untuk parameter tersebut dalam definisi prosedur. Parameter dapat ditentukan sebagai hanya output, tetapi jika berbagai kata kunci ditentukan dalam deklarasi parameter, jenis data harus kursor dan kata kunci output juga harus ditentukan.

Catatan

Jenis data kursor tidak dapat terikat ke variabel aplikasi melalui API database seperti OLE DB, ODBC, ADO, dan DB-Library. Karena parameter output harus terikat sebelum aplikasi dapat menjalankan prosedur, prosedur dengan parameter output kursor tidak dapat dipanggil dari API database. Prosedur ini dapat dipanggil dari batch Transact-SQL, prosedur, atau pemicu hanya ketika variabel output kursor ditetapkan ke variabel kursor lokal Transact-SQL.

Aturan untuk parameter output kursor

Aturan berikut berkaitan dengan parameter output kursor saat prosedur dijalankan:

  • Untuk kursor hanya maju, baris yang dikembalikan dalam tataan hasil kursor hanyalah baris di dan di luar posisi kursor pada akhir eksekusi prosedur, misalnya:

    • Kursor yang tidak dapat digulir dibuka dalam prosedur pada kumpulan hasil bernama RS 100 baris.

    • Prosedur ini mengambil lima baris pertama dari tataan RShasil .

    • Prosedur kembali ke pemanggilnya.

    • Tataan RS hasil yang dikembalikan ke pemanggil terdiri dari baris dari 6 hingga 100 , RSdan kursor di pemanggil diposisikan sebelum baris RSpertama .

  • Untuk kursor hanya-terusan, jika kursor diposisikan sebelum baris pertama saat prosedur keluar, seluruh tataan hasil dikembalikan ke batch panggilan, prosedur, atau pemicu. Saat dikembalikan, posisi kursor diatur sebelum baris pertama.

  • Untuk kursor hanya-terusan, jika kursor diposisikan di luar akhir baris terakhir saat prosedur keluar, tataan hasil kosong dikembalikan ke batch, prosedur, atau pemicu panggilan.

    Catatan

    Tataan hasil kosong tidak sama dengan nilai null.

  • Untuk kursor yang dapat digulir, semua baris dalam tataan hasil dikembalikan ke batch panggilan, prosedur, atau pemicu saat prosedur keluar. Ketika dikembalikan, posisi kursor dibiarkan pada posisi pengambilan terakhir yang dijalankan dalam prosedur.

  • Untuk semua jenis kursor, jika kursor ditutup, maka nilai null diteruskan kembali ke batch, prosedur, atau pemicu panggilan. Ini juga akan terjadi jika kursor ditetapkan ke parameter, tetapi kursor tersebut tidak pernah dibuka.

    Catatan

    Status tertutup hanya penting pada waktu pengembalian. Misalnya, valid untuk menutup bagian kursor dari cara melalui prosedur, untuk membukanya lagi nanti dalam prosedur, dan mengembalikan hasil kursor yang diatur ke batch panggilan, prosedur, atau pemicu.

Contoh parameter output kursor

Dalam contoh berikut, prosedur dibuat yang menentukan parameter output, @CurrencyCursor menggunakan jenis data kursor. Prosedur kemudian dipanggil dalam batch.

Pertama, buat prosedur yang menyatakan lalu buka kursor pada Currency tabel.

USE AdventureWorks2022;  
GO

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL  
    DROP PROCEDURE dbo.uspCurrencyCursor;  
GO

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.

USE AdventureWorks2022;  
GO

DECLARE @MyCursor CURSOR;  

EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  

WHILE (@@FETCH_STATUS = 0)  
    BEGIN;  
         FETCH NEXT FROM @MyCursor;  
    END;  

CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO

Mengembalikan data menggunakan kode pengembalian

Prosedur dapat mengembalikan nilai bilangan bulat yang disebut kode pengembalian untuk menunjukkan status eksekusi prosedur. Anda menentukan kode pengembalian untuk prosedur menggunakan pernyataan RETURN. Seperti parameter output, Anda harus menyimpan kode pengembalian dalam variabel ketika prosedur dijalankan untuk menggunakan nilai kode pengembalian dalam program panggilan. Misalnya, variabel @result penugasan jenis int data digunakan untuk menyimpan kode pengembalian dari prosedur my_proc, seperti:

DECLARE @result int;

EXECUTE @result = my_proc;
GO

Kode pengembalian umumnya digunakan dalam blok kontrol aliran dalam prosedur untuk mengatur nilai kode pengembalian untuk setiap kemungkinan situasi kesalahan. Anda dapat menggunakan @@ERROR fungsi setelah pernyataan Transact-SQL untuk mendeteksi apakah terjadi kesalahan selama eksekusi pernyataan. Sebelum pengenalan TRY//CATCHTHROW penanganan kesalahan dalam kode pengembalian Transact-SQL terkadang diperlukan untuk menentukan keberhasilan atau kegagalan prosedur tersimpan. Prosedur tersimpan harus selalu menunjukkan kegagalan dengan kesalahan (dihasilkan dengan THROW/RAISERROR jika perlu), dan tidak mengandalkan kode pengembalian untuk menunjukkan kegagalan. Anda juga harus menghindari penggunaan kode pengembalian untuk mengembalikan data aplikasi.

Contoh kode pengembalian

Contoh berikut menunjukkan usp_GetSalesYTD prosedur dengan penanganan kesalahan yang menetapkan nilai kode pengembalian khusus untuk berbagai kesalahan. Tabel berikut ini memperlihatkan nilai bilangan bulat yang ditetapkan oleh prosedur untuk setiap kemungkinan kesalahan, dan arti yang sesuai untuk setiap nilai.

Mengembalikan nilai kode Makna
0 Eksekusi berhasil.
1 Nilai parameter yang diperlukan tidak ditentukan.
2 Nilai parameter yang ditentukan tidak valid.
3 Terjadi kesalahan saat mendapatkan nilai penjualan.
4 Nilai penjualan NULL ditemukan untuk tenaga penjual.

Contoh membuat prosedur bernama Sales.usp_GetSalesYTD, yang:

  • Mendeklarasikan @SalesPerson parameter dan mengatur nilai defaultnya ke NULL. Parameter ini dimaksudkan untuk mengambil nama belakang orang penjualan.
  • Memvalidasi @SalesPerson parameter.
    • Jika @SalesPerson NULL, prosedur mencetak pesan dan mengembalikan kode 1pengembalian .
    • Jika tidak, jika @SalesPerson parameter bukan NULL, prosedur memeriksa jumlah baris dalam HumanResources.vEmployee tabel dengan nama belakang yang sama dengan nilai @SalesPerson. Jika jumlahnya nol, prosedur mengembalikan kode 2pengembalian .
  • Mengkueri penjualan tahun ke tanggal untuk sales person dengan nama belakang yang ditentukan dan menetapkannya ke @SalesYTD parameter output.
  • Memeriksa kesalahan SQL Server dengan menguji @@ERROR (Transact-SQL).
    • Jika @@ERROR tidak sama dengan nol, prosedur mengembalikan kode 3pengembalian .
    • Jika @@ERROR sama dengan nol, prosedur memeriksa untuk melihat apakah @SalesYTD nilai parameter adalah NULL. Jika tidak ada penjualan tahun ke tanggal yang ditemukan, prosedur mengembalikan kode 4pengembalian .
    • Jika tidak ada kondisi sebelumnya yang benar, prosedur mengembalikan kode 0pengembalian .
  • Jika tercapai, pernyataan akhir dalam prosedur tersimpan memanggil prosedur tersimpan secara rekursif tanpa menentukan nilai input.

Di akhir contoh, kode disediakan untuk menjalankan Sales.usp_GetSalesYTD prosedur sambil menentukan nama belakang untuk parameter input dan menyimpan nilai output dalam variabel @SalesYTD.

USE AdventureWorks2022;  
GO
  
CREATE PROCEDURE Sales.usp_GetSalesYTD 
    @SalesPerson NVARCHAR(50) = NULL, 
    @SalesYTD MONEY=NULL OUTPUT
AS
    IF @SalesPerson IS NULL 
    BEGIN
        PRINT 'ERROR: You must specify a last name for the sales person.'
        RETURN (1)
    END
    ELSE 
    BEGIN
        IF(SELECT COUNT(*)FROM HumanResources.vEmployee WHERE LastName=@SalesPerson)=0
            RETURN (2)
    END

    SELECT @SalesYTD=SalesYTD
    FROM Sales.SalesPerson AS sp
         JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID=sp.BusinessEntityID
    WHERE LastName=@SalesPerson;

    IF @@ERROR<>0 
    BEGIN
        RETURN (3)
    END 
    ELSE 
    BEGIN
        IF @SalesYTD IS NULL 
            RETURN (4)
        ELSE 
            RETURN (0)
    END

    EXEC Sales.usp_GetSalesYTD;
GO


DECLARE @SalesYTDForSalesPerson money, @ret_code int;  

EXECUTE Sales.usp_GetSalesYTD  N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  

PRINT N'Year-to-date sales for this employee is ' +  
    CONVERT(varchar(10), @SalesYTDForSalesPerson);  
GO

Contoh berikut membuat program untuk menangani kode pengembalian yang dikembalikan dari usp_GetSalesYTD prosedur.

Contohnya:

  • Mendeklarasikan variabel @SalesYTDForSalesPerson dan @ret_code untuk menerima nilai output dan mengembalikan kode prosedur.
  • Sales.usp_GetSalesYTD Menjalankan prosedur dengan nilai input yang ditentukan untuk @SalesPerson dan menyimpan nilai output dan mengembalikan kode dalam variabel.
  • Memeriksa kode pengembalian di @ret_code dan memanggil PRINT (Transact-SQL) untuk menampilkan pesan yang sesuai.

DECLARE @SalesYTDForSalesPerson money, @ret_code int;  
  
EXECUTE @ret_code = Sales.usp_GetSalesYTD  
    N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;  

IF @ret_code = 0  
    BEGIN  
        PRINT 'Procedure executed successfully';
        PRINT 'Year-to-date sales for this employee is ' + CONVERT(varchar(10),@SalesYTDForSalesPerson);
    END  
ELSE IF @ret_code = 1  
   PRINT 'ERROR: You must specify a last name for the sales person.';
ELSE IF @ret_code = 2   
   PRINT 'ERROR: You must enter a valid last name for the sales person.';
ELSE IF @ret_code = 3  
   PRINT 'ERROR: An error occurred getting sales value.';
ELSE IF @ret_code = 4  
   PRINT 'ERROR: No sales recorded for this employee.';
GO

Konten terkait

Untuk informasi selengkapnya tentang prosedur tersimpan dan konsep terkait, lihat artikel berikut ini: