Dalam beberapa kasus dengan SQL Server, mungkin ada contoh di mana Anda ingin mengambil data yang dihasilkan dari prosedur tersimpan dan memasukkannya ke dalam tabel sementara untuk digunakan dalam kueri lain. Menentukan bagaimana menyelesaikan tugas ini bisa agak sulit, jadi kami akan menjelaskan secara singkat beberapa opsi, tergantung pada kebutuhan spesifik dan konfigurasi database Anda
Sebelum kita memeriksa metode spesifik, mari kita buat sebuah contoh prosedur. Meskipun tidak terlalu berguna, mari buat prosedur BooksByPrimaryAuthor, yang menerima parameter @PrimaryAuthor dan mengambil catatan dari tabel books kita di mana @PrimaryAuthor cocok. Pernyataan pembuatan prosedur mungkin terlihat seperti ini
CREATE PROC BooksByPrimaryAuthor @PrimaryAuthor nvarchar(100) AS BEGIN SELECT * FROM books WHERE primary_author = @PrimaryAuthor; END GO
Idealnya, yang ingin kami lakukan adalah sesuatu seperti ini, di mana kami SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' 1 data yang dihasilkan dari prosedur kami dan memasukkannya ke dalam tabel sementara
SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' _
Masalahnya adalah sintaks di atas tidak tepat dan tidak akan berfungsi. Kami membutuhkan metode baru
Menggunakan Pernyataan OPENROWSET
Salah satu kemungkinannya adalah menggunakan pernyataan SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' _2, yang memungkinkan Anda untuk mengakses data jarak jauh dari sumber OLE DB dan dapat dijalankan langsung dari dalam pernyataan SQL lain. SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' 2 adalah koneksi satu kali dan metode pengambilan data, sehingga tidak boleh digunakan untuk koneksi yang sering (menghubungkan server lebih disukai dalam kasus itu)
SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' _2 dapat menjadi target dari pernyataan SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' 5, SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' 6, atau SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' 7, yang menjadikannya ideal untuk tujuan "menjalankan" prosedur tersimpan kami untuk kami dan mengekstrak data tersebut kembali ke tabel sementara menunggu kami
Sebelum menggunakan SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' _2, mungkin perlu mengubah beberapa opsi konfigurasi, khususnya dengan mengizinkan akses ad hoc. Ini dapat dikonfigurasi menggunakan pernyataan berikut
sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO
Sekarang kita dapat menggunakan SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' 2, yang memiliki sintaks tertentu yang harus dipatuhi
OPENROWSET( <PROVIDER_NAME>, <DATA_SOURCE>, <OPTIONS> )
Jadi, kami dapat menjalankan prosedur tersimpan kami melalui SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' 2 dan meneruskannya ke tabel sementara kami seperti itu
SELECT * INTO #tmpSortedBooks FROM OPENROWSET( 'SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;', 'EXEC BooksByPrimaryAuthor Tolkien' )
Anda mungkin perlu mengubah nilai sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO _1 dan sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO 2 untuk tujuan Anda sendiri
Menggunakan Fungsi yang Ditentukan Pengguna
Ada beberapa kelemahan metode SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' _2, yaitu memerlukan izin/konfigurasi ad hoc seperti yang kita lihat di atas, dan juga SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' 2 hanya mampu mengembalikan satu set hasil (jika beberapa set disediakan, hanya set hasil pertama yang dikembalikan
Oleh karena itu, metode lain untuk melakukan tugas ini adalah mengganti prosedur tersimpan secara efektif dengan fungsi yang ditentukan pengguna
Dari contoh kita, akan terlihat seperti ini
CREATE FUNCTION BooksByPrimaryAuthor ( @PrimaryAuthor nvarchar(100) ) RETURNS TABLE AS RETURN SELECT * FROM books WHERE primary_author = @PrimaryAuthor; GO
Fungsi ini kemudian dapat digunakan kira-kira dengan cara yang sama seperti yang diinginkan di atas menggunakan SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien' 2
SELECT * INTO #tmpSortedBooks FROM BooksByPrimaryAuthor('Tolkien')
Jika Anda benar-benar membutuhkan prosedur tersimpan, Anda juga dapat membungkus fungsi Anda di dalam prosedur tersimpan juga