Mengembalikan data dari prosedur tersimpan

2025-03-05

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL database di Microsoft Fabric

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

Sampel kode dalam artikel ini menggunakan database sampel AdventureWorks2022 atau AdventureWorksDW2022, yang dapat Anda unduh dari halaman beranda Sampel dan Proyek Komunitas Microsoft SQL Server.

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 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 disiapkan untuk dikirim kembali ke klien dan eksekusi berlanjut. Jika beberapa pernyataan SELECT tersebut dijalankan selama eksekusi prosedur tersimpan, beberapa set hasil dikirim ke klien. Perilaku ini juga berlaku untuk batch Transact-SQL berlapis, prosedur tersimpan berlapis, dan batch Transact-SQL tingkat atas.

Contoh pengembalian data menggunakan set hasil

Contoh ini memperlihatkan prosedur tersimpan yang mengembalikan nilai LastName dan SalesYTD untuk semua baris SalesPerson yang juga muncul dalam pandangan vEmployee.

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
     INNER JOIN HumanResources.vEmployee AS e
         ON e.BusinessEntityID = sp.BusinessEntityID;
RETURN;
GO

Mengembalikan data menggunakan parameter keluaran

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.

Contoh-contoh parameter keluaran

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 SalesYTD yang benar. Pernyataan SELECT juga menetapkan nilai ke parameter output @SalesYTD, 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
     INNER 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 Sales.uspGetEmployeeSalesYTD yang menentukan nama keluarga untuk parameter input. Simpan nilai output dalam variabel @SalesYTDBySalesPerson.
Memanggil PRINT untuk menampilkan nilai yang disimpan ke @SalesYTDBySalesPerson.

DECLARE @SalesYTDBySalesPerson AS 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 yang dijalankan akan meneruskan nilai variabel @SalesYTDBySalesPerson ke dalam parameter output @SalesYTD. 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 pass-by-reference.

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 yang dinamai RS dengan 100 baris.
- Prosedur ini mengambil lima baris pertama dari set hasil RS.
- Prosedur kembali ke pemanggilnya.
- Set hasil RS yang dikembalikan ke pemanggil terdiri dari baris 6 hingga 100 di RS, dan kursor di pemanggil diposisikan sebelum baris pertama RS.
Untuk kursor maju-saja, jika kursor diposisikan sebelum baris pertama ketika prosedur selesai, seluruh tataan hasil diberikan ke batch pemanggil, prosedur, atau pemicu. Saat dikembalikan, posisi kursor diatur sebelum baris pertama.
Untuk kursor satu arah, jika kursor diposisikan setelah baris terakhir saat prosedur keluar, set hasil kosong dikembalikan ke batch yang memanggil, prosedur, atau pemicu.

Catatan

Kumpulan hasil kosong tidak sama dengan nilai null.
Untuk kursor yang dapat digulir, semua baris dalam set hasil dikembalikan ke batch, prosedur, atau pemicu yang memanggil ketika prosedur berakhir. Ketika dikembalikan, posisi kursor tetap pada posisi pelaksanaan pengambilan terakhir dalam prosedur.
Untuk semua jenis kursor, jika kursor ditutup, maka nilai null dikembalikan ke batch, prosedur, atau pemicu pemanggil. Ini juga terjadi jika kursor ditetapkan ke parameter, tetapi kursor tersebut tidak pernah dibuka.

Catatan

Status tertutup hanya penting pada waktu pengembalian. Misalnya, diperbolehkan untuk menutup kursor di sepanjang prosedur, membukanya kembali nanti dalam prosedur, dan mengembalikan set hasil kursor tersebut ke batch pemanggil, prosedur, atau pemicu.

Contoh parameter keluaran kursor

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

Pertama, buat prosedur yang mendeklarasikan dan kemudian membuka kursor pada tabel Currency.

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 AS CURSOR;

EXECUTE 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 RETURN. Seperti parameter output, Anda harus menyimpan kode pengembalian dalam variabel ketika prosedur dijalankan untuk menggunakan nilai kode pengembalian dalam program panggilan. Misalnya, variabel penugasan @result jenis data int digunakan untuk menyimpan kode pengembalian dari prosedur my_proc, seperti:

DECLARE @result AS 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/CATCH/THROW 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`	`NULL` nilai penjualan yang 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 tenaga penjual.
Memvalidasi @SalesPerson parameter.
- Jika @SalesPerson adalah NULL, prosedur mencetak pesan dan mengembalikan kode pengembalian 1.
- Jika parameter @SalesPerson tidak sama dengan NULL, prosedur akan memeriksa jumlah baris dalam tabel HumanResources.vEmployee yang memiliki nama keluarga sama dengan nilai @SalesPerson. Jika jumlahnya nol, prosedur mengembalikan kode 2 pengembalian.
Meminta data penjualan tahun hingga saat ini untuk tenaga penjualan dengan nama belakang yang ditentukan dan menetapkannya ke parameter output @SalesYTD.
Memeriksa kesalahan SQL Server dengan menguji @@ERROR.
- Jika @@ERROR tidak sama dengan nol, prosedur mengembalikan kode pengembalian 3.
- Jika @@ERROR sama dengan nol, prosedur memeriksa untuk melihat apakah nilai parameter @SalesYTDNULL. Jika tidak ada penjualan tahun berjalan yang ditemukan, prosedur mengembalikan kode 4 pengembalian.
- Jika tidak ada kondisi sebelumnya yang benar, prosedur mengembalikan kode pengembalian 0.
Jika tercapai, pernyataan akhir dalam prosedur tersimpan memanggil prosedur tersimpan secara rekursif tanpa menentukan nilai input.

Di akhir contoh, kode disediakan untuk menjalankan prosedur Sales.usp_GetSalesYTD sambil menentukan nama keluarga 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
     INNER 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
EXECUTE Sales.usp_GetSalesYTD;
GO

DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS 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 yang dikembalikan dari prosedur usp_GetSalesYTD.