Setuju!!! :))
Utk artikel dengan kualitas seperti ini terlalu sayang untuk tidak dibukukan.
Salam,
Andreas.
Membuat Program Multiuser Dengan Front-End Microsoft Access dan Back-End Microsoft SQL Server (Bagian 3)
STORED PROCEDURES
SQL Server ibarat lemari baju, kemudian stored procedures ibarat robot canggih yang berdiri disebelahnya. Robot canggih ini dapat diberi perintah secara remote dari jarak yang sangat jauh, yakni perintah apa saja yang berkaitan dengan penyimpanan pakaian kedalam lemari baju. Semua pakaian berantakan yang terdapat didepan lemari pakaian, dapat ia susun secara cepat kemudian disimpan kedalam lemari. Pakaian anak ke rak anak, baju dan sarung ke rak yang satu, dsb. Dia dapat menyusun dengan sangat cepat, karena dia BERDIRI DEKAT DISAMPING LEMARI! Pemilik lemari cukup me-remote dari jarak yang jauh, memberi perintah, sehingga dia bisa bekerja secara otomatis. Tanpa diremote dan diberi perintah, maka robot canggih ini hanya diam berdiri disamping lemari.
Stored Procedures adalah "VBA" nya SQL Server. Ia adalah "ACTION QUERY" nya SQL Server. Dengan demikian, setiap trigger adalah pasti stored procedures, namun tidak semua stored procedures adalah trigger.
Maka jika sahabat hendak membuat program multiuser dengan front-end Microsoft Access dan back-end Microsoft SQL Server, sebaiknya (sangat dianjurkan) mempelajari dengan detail stored procedures ini. Memang dia tidak digunakan pun tidak apa-apa. Program tetap bisa jalan dengan baik. Namun masalahnya, sahabat harus mengirim kurir dari jarak yang jauh, hanya untuk menyimpan baju anak ke rak anak. Karena robot canggih tidak diberi perintah, sehingga sahabat memboroskan waktu sedikit untuk pelaksanaan perintah yang sebenarnya bisa dilakukan oleh robot yang sedang berdiri disebelah lemari baju.
Kemudian, bahasa yang dapat dimengerti oleh "robot" ini hanya bahasa "Transact SQL", sehingga bila sahabat hanya mengerti bahasa Indonesia dan Inggris, belumlah mencukupi. Dia tidak dapat memahami perintah selain Transact SQL. Begitu pula komunikasi dengan "lemari", sahabat harus menggunakan bahasa Transact SQL. Ia adalah bahasa sehari-harinya SQL Server, sehingga seluruh komponen yang berada didalam SQL Server, baik view, trigger, stored procedures, job, dll haruslah menggunakan bahasa Transact SQL.
MEMBUAT STORED PROCEDURES SEDERHANA
Marilah kita belajar dasar-dasar pembuatan stored procedures. Misal, kita hendak output data address pada table Person dalam database AdventureWorks. Maka kode T-SQL nya adalah sbb:
SELECT * FROM AdventureWorks.Person.Address
Maka buatlah procedure baru pada dalam database tsb, secara default tampilan awalnya adalah sbb:
CREATE PROCEDURE [PROCEDURE NAME] AS
Modif kode T-SQL diatas, menjadi:
CREATE PROCEDURE AmbilAlamat
ASSELECT * FROM AdventureWorks.Person.AddressGOKemudian pada Query Analyzer, run dng mengetikan:
EXEC AmbilAlamat
--atau ketikan
AmbilAlamat
MEMBUAT STORED PROCEDURES DENGAN PARAMATER
Procedure diatas mengambil data alamat untuk seluruh kota, padahal saya ingin hanya kota tertentu saja yang di-output. Maka kita harus menambahkan parameter pada stored proceduresnya, menjadi:
CREATE PROCEDURE AmbilAlamat @City nvarchar(30)
ASSELECT *FROM AdventureWorks.Person.AddressWHERE City = @CityGOPerhatikan kode pada baris pertama. Mengingatkan kita pada pembuatan suatu prrocedure didalam VBA, misalnya:
Public Function Alamat ( NamaKota As String ) As String
End
Sedangkan GO, mengingatkan kita pada kode "END" didalam VBA.
Ok, kita lupakan VBA, kembali ke Stored Procedures. Run stored procedures diatas, dengan mengetikan:
EXEC AmbilAlamat @City = 'New York'
Procedure diatas menghasilkan data alamat dengan kota hanya New York saja. Bagaimana bila hendak melihat data alamat dengan kota dengan diawali dari kata BAN, misal Bandung, Banten, Banjar, dll. Maka stored procedures diatas harus dimodif lagi, menjadi:
CREATE PROCEDURE AmbilAlamat @City nvarchar(30)
ASSELECT *FROM AdventureWorks.Person.AddressWHERE City LIKE @City + '%'GOPerhatikan pada clausa WHERE. Terdapat kata "LIKE" dan simbol '%'. Persis seperti Access yang menggunakan kriteria LIKE dan tanda bintang (*).
MEMBUAT DEFAULT VALUE PADA PARAMATER STORED PROCEDURES
Jika Sahabat menjalankan procedure diatas tanpa memberikan paramater, maka akan mengakibatkan error, yakni:
Msg 201, Level 16, State 4, Procedure AmbilNama, Line 0
Procedure or function 'AmbilNama' expects parameter '@City', which was not supplied.
Karena itu, sahabat harus memberikan default value NULL diawal deklarasi stored procedure, yakni:
CREATE PROCEDURE AmbilAlamat @City nvarchar(30) = NULLASSELECT *FROM AdventureWorks.Person.AddressWHERE City = ISNULL(@City,City)GOMEMBUAT STORED PROCEDURES DENGAN PARAMATER LEBIH DARI SATU (BANYAK/MULTIPLE PARAMETER)
Sebagaimana dalam VBA, kita bisa membuat:
Public Function Alamat ( NamaKota As String, NamaDesa As String ) As String
End
Maka kitapun bisa membuat stored procedure sbb:
CREATE PROCEDURE AmbilAlamat @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULLASSELECT *FROM AdventureWorks.Person.AddressWHERE City = ISNULL(@City,City)AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%'GOUntuk menjalankannya, sahabat bisa Run dengan berbagai alternatif, misal:
EXEC AmbilAlamat @City = 'Calgary'--atau
EXEC AmbilAlamat @City = 'Calgary', @AddressLine1 = 'A'--atau
EXEC AmbilAlamat @AddressLine1 = 'Acardia'-- dsb...
MENGAMBIL OUTPUT SUATU STORED PROCEDURES BERPARAMATER
Barangkali sahabat pernah membuat procedure sbb didalam VBA:
Public Function Nilai()
Nilai = 10
End
Maka kita bisa manfaatkan output nilai diatas kedalam procedure lainnya, misal:
Public Function Nilai2()
Nilai2 = Nilai + 10
End
Begitu pula dengan stored procedure, kita bisa manfaatkan dengan syntax OUTPUT atau OUT. Misal:
CREATE PROCEDURE JmlhAlamat @City nvarchar(30), @AddressCount int OUTPUTASSELECT @AddressCount = count(*)FROM AdventureWorks.Person.AddressWHERE City = @CityKemudian untuk mengambil nilai dari output AddressCount, bisa menggunakan stored procedure sbb:
DECLARE @AddressCount intEXEC JmlhAlamat @City = 'Calgary', @AddressCount = @AddressCount OUTPUTSELECT @AddressCountAtau bisa juga dengan cara tanpa menuliskan nama parameternya, yakni sbb:
DECLARE @AddressCount intEXEC JmlhAlamat 'Calgary', @AddressCount OUTPUTSELECT @AddressCountSekedar info, DECLARE didalam VBA persis seperti DIM, misal:
Dim X As String
Sedangkan EXEC didalam VBA persis seperti CALL, misal:
Call NamaProcedure (NamaParameter)
ERROR HANDLE DI DALAM STORED PROCEDURES
Terkadang, cukup sulit mengetahui persis penyebab error didalam stored procedures. Alhamdulillah, pada SQL Server 2005, sudah disertakan fasilitas "coba-coba", dan menariknya, ditampilkan juga "pesan error" nya seandainya terdapat error. Hal ini disebut juga "Try Catch". Misalnya:
CREATE PROCEDURE uspTryCatchTestASBEGIN TRYSELECT 1/0END TRYBEGIN CATCHSELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;END CATCHPersis seperti didalam VBA, terdapat On Error Goto MsgErr, dimana pada MsgErr, bisa kita tambahkan informasi errornya, misal Err.Number, Err.Description, dsb.
MEMBUAT KOMENTAR DI DALAM STORED PROCEDURES
Mungkin saat ini sahabat ingat, procedure ini digunakan untuk apa, kode ini untuk apa, dsb. Namun setelah berlalunya waktu, misal 1 tahun mendatang, bisa jadi sahabat lupa, apa fungsi dari stored procedures ini, kenapa menggunakan kode ini, dsb. Karena itu, buatlah komentar (comments) pada stored procedures yang sahabat buat. Pembuatan komentar ditandai dengan simbol "--" atau bisa juga dengan "/* */". Terdapat 3 macam variasi komentar (comments) yang dapat dibuat, yakni:
1. Komentar Model Baris, misal:
-- Procedure ini digunakan untuk mengambil data alamat-- pada suatu kotaCREATE PROCEDURE AmbilAlamat @City nvarchar(30)ASSELECT *FROM AdventureWorks.Person.AddressWHERE City = @CityGO
2. Sekumpulan Komentar, misal:
/*
Procedure ini digunakan
untuk mengambil data alamatpada suatu kota
*/
CREATE PROCEDURE AmbilAlamat @City nvarchar(30)ASSELECT *FROM AdventureWorks.Person.AddressWHERE City = @CityGO
3. Komentar Model Baris dan Sekumpulan Komentar, misal:
/*
Procedure ini digunakan
untuk mengambil data alamatpada suatu kota
*/
CREATE PROCEDURE AmbilAlamat @City nvarchar(30)ASSELECT *FROM AdventureWorks.Person.AddressWHERE City = @City -- Parameter @City diisi ya nanti saat eksekusinya :)GOMINIMALISASI PENGGUNAAN LALU LINTAS NETWORK
Sebagaimana yang dijalaskan Microsoft MSDN pada situsnya di: http://msdn.microsoft.com/en-us/library/aa259204%28v=sql.80%29.aspx , untuk meminimalisasi penggunaan lalu lintas network didalam stored procedure, gunakan syntax "SET NOCOUNT ON", misal:
CREATE PROCEDURE AmbilAlamat @City nvarchar(30)ASSET NOCOUNT ON
SELECT *FROM AdventureWorks.Person.AddressWHERE City = @CityGOUntuk mengetahui jumlah record yang dihasilkan oleh procedure diatas, gunakan perintah PRINT @@ROWCOUNT. Misal:
CREATE PROCEDURE AmbilAlamat @City nvarchar(30)
ASSET NOCOUNT ON
SELECT *FROM AdventureWorks.Person.AddressWHERE City = @CityPRINT @@ROWCOUNT
GOBERBAGAI VARIASI PENGGUNAAN STORED PROCEDURES
Sebagaimana pada VBA, sebenarnya stored procedure bisa digunakan untuk berbagai macam keperluan, diantaranya insert data, reindex database, backup, dsb. Namun, yang paling sering digunakan adalah:
· Insert
· Delete
· Update
· Select
· Get
· Validate
BEBERAPA PERKIRAAN FAQ
1. Saya belum banyak paham stored procedure, apakah tidak masalah? Tidak apa-apa. Gunakan saja query ataupun action query di dalam Access, kemudian padukan dengan VBA. Sambil jalan, kedepannya sahabat belajar dan mencoba membuat stored procedure sedikit demi sedikit untuk aplikasi multiuser yang sahabat buat.
2. Apa yang semestinya dipelajari dalam membuat stored procedure? Seorang anak bayi yang baru lahir, tentulah sangat asing baginya dunia fana yang baru ia lihat ini. Ia awalnya tidak mengetahui, lalu ia mulai memperhatikan sekitarnya, kemudian setelah itu, diajari oleh ayah ibunya kosa kata berbagai benda. Maka Subhanallah, tiba-tiba ia menjadi banyak mengetahui berbagai nama dari benda-benda. Maka sahabat harus belajar "vocabulary" dari stored procedure SQL Server ini, yakni bahasa "Transact-SQL". Semuanya tidak bisa instant, sedikit demi sedikit.
3. Berapa lama saya bisa menguasai stored procedure ataupun Transact-SQL ini? Berbanding lurus dengan SEMANGAT sahabat dalam mempelajarinya. Bukankah seorang bayi harus mencoba merangkak berbulan-bulan agar bisa jalan?
Insya Allah nanti disambung kembali...
/* --------------------------------------------------------------- */
Penyusun: Sofyan Efendi. Salah seorang pengguna Microsoft Access sebagai front-end dan Microsoft SQL Server sebagai back-end. Sahabat bisa mengunjungi blog penyusun di: http://imopi.wordpress.com/. Terima kasih.
/* --------------------------------------------------------------- */
Wassalamu'alaikum Warahmatullahi Wabarakatuh,
Sofyan Efendi
Access DVD Collections ; Access Course by Request
--
Email: harry.siswanto@gmail.com
http://www.facebook.com/harry.siswanto
http://www.harry-siswanto.blogspot.com/?view=classic
http://accessindonesia.blogspot.com/
Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar