Cara mendapatkan nilai dalam prosedur tersimpan SQL Server dengan ADO

Ringkasan

Ada masalah penting untuk dipertimbangkan saat mencoba mengambil nilai RAISERROR/cetak/dikembalikan dari prosedur tersimpan SQL Server melalui objek data ActiveX (ADO). Berikut adalah tiga masalah:

• Pernyataan RAISERROR di SQL Server harus tingkat keparahan 11-18.

• MENCETAK pernyataan di SQL Server juga bisa mengisi kumpulan kesalahan ADO. Namun, pernyataan cetak adalah tingkat keparahan nol (0) sehingga, setidaknya satu pernyataan RAISERROR diperlukan dalam prosedur yang disimpan untuk mengambil pernyataan cetak dengan ADO melalui Kumpulan kesalahan.

• Nilai yang dikembalikan dalam prosedur yang disimpan harus dikaitkan dengan setidaknya satu resultset.

Informasi Selengkapnya

Sampel kode berikut ini memperlihatkan penjelajahan kumpulan kesalahan ADO untuk mengakses detail RAISERROR/PRINT/RETURN dari prosedur SQL Server yang disimpan mengembalikan beberapa resultsets:

1. Tempelkan dan jalankan kode berikut di jendela ISQL_W untuk membuat prosedur tersimpan yang digunakan untuk sampel ADO di langkah 4:

         use pubs      GO      if exists (select * from sysobjects where id =        object_id('dbo.ADOTestRPE') and sysstat & 0xf = 4)      drop procedure dbo.ADOTestRPE      GO      create procedure ADOTestRPE      (      @SetRtn  INT=0 OUTPUT,      @R1Num   INT=1,      @P1Num   INT=1,      @E1Num   INT=1,      @R2Num   INT=2,      @P2Num   INT=2,      @E2Num   INT=2      )      AS      DECLARE @iLoop     INT      DECLARE @PrintText VARCHAR(255)      DECLARE @iErrNum   INT      /*   Check for no Resultsets - needed to get the RETURN value back */       IF @R1Num + @R2Num = 0 SELECT NULL      /*   Resultset 1  ******************************* */       IF @R1Num > 0      BEGIN         SET ROWCOUNT @R1Num         SELECT 'Resultset 1' RsNum, Title         FROM Pubs..Titles         SET ROWCOUNT 0      END         /* Must raise a default error context in which to return the PRINT */         /*  statement */        /* (if none present) since PRINT statements are a severity level of */       /*0. */       IF (@P1Num > 0) AND (@E1Num = 0) RAISERROR ("RAISERROR.PError1",         11, 2)      IF @P1Num > 0      BEGIN         SELECT @iLoop = 0         WHILE @iLoop < @P1Num         BEGIN            SELECT @iLoop = @iLoop + 1            SELECT @PrintText = 'PRINT.Resultset.1: Line ' +      CONVERT(char(2), @iLoop)         PRINT @PrintText        END      END      IF @E1Num > 0      BEGIN         SELECT @iLoop = 0         WHILE @iLoop < @E1Num         BEGIN            SELECT @iLoop = @iLoop + 1            SELECT @iErrNum = @iLoop + 201000            RAISERROR ("RAISERROR.Resultset.1", 11, 2)         END      END      /*   Resultset 2  ******************************* */       IF @R2Num > 0      BEGIN         SET ROWCOUNT @R2Num         SELECT 'Resultset 2' RsNum, Title         FROM Pubs..Titles         SET ROWCOUNT 0      END      /* Must raise a default error context in which to return the PRINT */       /*  statement */       /* (if none present) since PRINT statements are a severity level of */       /*  0. */       IF (@P2Num > 0) AND (@E2Num = 0) RAISERROR ("RAISERROR.PError2",      11, 2)      IF @P2Num > 0      BEGIN         SELECT @iLoop = 0         WHILE @iLoop < @P2Num         BEGIN            SELECT @iLoop = @iLoop + 1            SELECT @PrintText = 'PRINT.Resultset.2: Line ' +       CONVERT(char(2), @iLoop)            PRINT @PrintText         END      END      IF @E2Num > 0      BEGIN         SELECT @iLoop = 0         WHILE @iLoop < @E2Num         BEGIN            SELECT @iLoop = @iLoop + 1            SELECT @iErrNum = @iLoop + 202000            RAISERROR ("RAISERROR.Resultset.2", 11, 2)         END      END      /*   Return & Output ************************************ */             select @SetRtn = -1      RETURN @SetRtn      GO

2. Membuat standar. Proyek EXE dalam Visual Basic. Form1 dibuat secara default.

3. Dari menu Project, pilih referensi dan pilih pustaka objek data ActiveX Microsoft. Catatan: Anda harus menggunakan ADO versi 2,0 atau yang lebih baru agar kode berfungsi dengan benar. Anda dapat memperoleh komponen Microsoft Data Access Components (MDAC) terbaru di web di URL berikut ini:

4. Tempatkan tombol perintah pada formulir, lalu tempelkan kode berikut di bagian Deklarasi umum dari formulir: catatan: Anda mungkin perlu mengubah string koneksi database untuk lingkungan Anda.

         'This Code demonstrates RAISERROR/PRINT/RETURN values with ADO and      'multiple resultsets.      Sub CreateParms()      Dim ADOCmd As New ADODB.Command      Dim ADOPrm As New ADODB.Parameter      Dim ADOCon As ADODB.Connection      Dim ADORs As ADODB.Recordset      Dim sParmName As String      Dim strConnect As String      Dim rStr As String      On Error GoTo ErrHandler      strConnect = "driver={SQL        Server};server=(local);uid=sa;pwd=;database=pubs"      Set ADOCon = New ADODB.Connection      With ADOCon          .Provider = "MSDASQL"          .CursorLocation = adUseServer  'Must use Server side cursor.          .ConnectionString = strConnect          .Open      End With      Set ADOCmd.ActiveConnection = ADOCon      With ADOCmd          .CommandType = adCmdStoredProc          .CommandText = "ADOTestRPE"      End With      'Parameter 0 is the stored procedure Return code.      sParmName = "Return"      Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _        adParamReturnValue, , 0)      ADOCmd.Parameters.Append ADOPrm      ADOCmd.Parameters(sParmName).Value = -1      'Parameter 1 is the setting for the stored procedure Output      ' parameter.      sParmName = "Output"      Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _        adParamOutput)      ADOCmd.Parameters.Append ADOPrm      ADOCmd.Parameters(sParmName).Value = 999      'Parameter 2      sParmName = "R1Num"     'Number of rows to return in Resultset 1.      Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _        adParamInput)      ADOCmd.Parameters.Append ADOPrm      ADOCmd.Parameters(sParmName).Value = 1      'Parameter 3      sParmName = "P1Num"     'Number of PRINT statements in Resultset 1.      Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _       adParamInput)      ADOCmd.Parameters.Append ADOPrm      ADOCmd.Parameters(sParmName).Value = 0      'Parameter 4      sParmName = "E1Num"     'Number of RAISERROR statements in Resultset                              '1.      Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _        adParamInput)      ADOCmd.Parameters.Append ADOPrm      ADOCmd.Parameters(sParmName).Value = 0      'Parameter 5      sParmName = "R2Num"     'Number of rows to return in Resultset 2.      Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _        adParamInput)      ADOCmd.Parameters.Append ADOPrm      ADOCmd.Parameters(sParmName).Value = 2      'Parameter 6      sParmName = "P2Num"     'Number of PRINT statements in Resultset 2.      Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _        adParamInput)      ADOCmd.Parameters.Append ADOPrm      ADOCmd.Parameters(sParmName).Value = 0      'Parameter 7      sParmName = "E2Num"     'Number of RAISERROR statements in Resultset                              ' 2.      Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _       adParamInput)      ADOCmd.Parameters.Append ADOPrm      ADOCmd.Parameters(sParmName).Value = 0      Set ADORs = ADOCmd.Execute      Do While (Not ADORs Is Nothing)          If ADORs.State = adStateClosed Then Exit Do          While Not ADORs.EOF              For i = 0 To ADORs.Fields.Count - 1                  rStr = rStr & " : " & ADORs(i)              Next i              Debug.Print Mid(rStr, 3, Len(rStr))              ADORs.MoveNext              rStr = ""          Wend          Debug.Print "----------------------"          Set ADORs = ADORs.NextRecordset      Loop      Debug.Print "Return: " & ADOCmd.Parameters("Return").Value      Debug.Print "Output: " & ADOCmd.Parameters("Output").Value      GoTo Shutdown      ErrHandler:          Call ErrHandler(ADOCon)          Resume Next      Shutdown:          Set ADOCmd = Nothing          Set ADOPrm = Nothing          Set ADORs = Nothing          Set ADOCon = Nothing      End Sub      Private Sub Command1_Click()      Call CreateParms      End Sub      Sub ErrHandler(objCon As Object)      Dim ADOErr As ADODB.Error      Dim strError As String      For Each ADOErr In objCon.Errors       strError = "Error #" & ADOErr.Number & vbCrLf & ADOErr.Description _          & vbCr & _           "   (Source: " & ADOErr.Source & ")" & vbCr & _           "   (SQL State: " & ADOErr.SQLState & ")" & vbCr & _           "   (NativeError: " & ADOErr.NativeError & ")" & vbCr       If ADOErr.HelpFile = "" Then           strError = strError & "   No Help file available" & vbCr & vbCr       Else           strError = strError & "   (HelpFile: " & ADOErr.HelpFile & ")" _       & vbCr & _                       "   (HelpContext: " & ADOErr.HelpContext & ")" & _       vbCr & vbCr       End If       Debug.Print strError      Next      objCon.Errors.Clear      End Sub

5. Ubah nilai parameter dua sampai tujuh untuk mengubah jumlah pernyataan cetak dan/atau pernyataan RAISERROR yang dihasilkan oleh prosedur yang disimpan dan dikembalikan melalui ADO. Jalankan sampel kode Visual Basic lagi dan perhatikan bahwa pernyataan RAISERROR dan cetak dikembalikan melalui Kumpulan kesalahan ADO. Ubah nilai untuk bereksperimen dengan kombinasi berbeda dari pernyataan cetak/RAISERROR dengan resultsets yang berbeda. Silakan lihat prosedur yang disimpan SQL untuk solusi tertentu untuk kasus khusus. Catatan: untuk mengambil nilai yang dikembalikan dalam ADO dengan prosedur yang disimpan, setidaknya ada satu resultset. Untuk mengatasi masalah ini, ketika tidak ada resultsets yang ditentukan (dalam kode sampel ADO) prosedur yang disimpan mengeksekusi NULL SELECT untuk mengembalikan resultset ke ADO dengan demikian mengisi nilai yang dikembalikan. Selain itu, untuk mengatasi masalah dalam menentukan pernyataan RAISERROR dan kombinasi dari pernyataan cetak, laporan default RAISERROR dihasilkan untuk menyediakan konteks untuk mengembalikan pernyataan cetak melalui ADO. Anda harus memkode pernyataan RAISERROR dalam format yang diperlihatkan dalam prosedur yang disimpan karena hanya tingkat keparahan 11-18 yang dikembalikan melalui Kumpulan kesalahan ADO.

Referensi

Bantuan Transact-SQL: pencarian pada: "cetak/RAISERROR statement" untuk informasi selengkapnya, lihat artikel berikut ini di Pangkalan Pengetahuan Microsoft:

Cara membuka kumpulan data ADO dengan asinkron menggunakan WithEvents