Tag: tsql

Akses user SqlServer pada linked server

jika anda pernah mengalami kasus seperti :

image_thumb26

maka user yang anda pakai belum diberi grant untuk mengakses linked server ,

untuk mengatasi hal ini, diperlukan hanya 2 langkah saja :

Tambahkan user anda pada database master

USE [master]
GO
CREATE USER user_login_anda FOR LOGIN user_login_anda;

Tambahkan hak mengeksekusi linked server pada user

USE [master]
GO
GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO user_login_anda;

*jika anda menggunakan domain user maka user_login_anda menjadi [DOMAIN\user_login_anda]

ok itu saja, semoga banyak membantu 😀

Tentang dia , kamu dan modularisasi pada sqlserver

Kamu siapa kamu ?  dia siapa dia ?

langsung saja gaiss.. jangan melihat judul itu, langsung saja yang ingin saya bahas adalah :

  1. Stored Procedure
  2. Function

di environtment sqlserver.  tentunya setelah belajar mengenai TSQL , kita harus move on kan ? hehehe.

alasan utama kenapa harus membuat stored procedure dan function adalah modularity yang berujung pada reuse dari stored procedure dan function tersebut.

Stored Procedure

Dengan stored procedure, kita dapat menyimpan sekumpulan TSQL yang kita buat ke dalam sebuah procedure yang tersimpan rapi di hati sqlserver. Dengan tersimpan, kita dapat memanggil manggilnya sesuka hati kita, kapan saja dia siap membantu kita…

sungguh setia bukan !

mari kita mulai dari yang paling simple saja, seperti :

Pada baris 9 dapat dilihat sintaks untuk membuat simple procedure, dimana terdiri dari nama procedure lalu block procedure dimulai dari begin dan diakhiri sampai end, yang mana dalam kasus yang ada di atas kita akan menghitung jumlah baris dari table person yang ada pada database adventure works 2012. Untuk menjalankan stored procedure ini kita dapat menggunakan perintah exec seperti pada baris 21.

Stored procedure dengan parameter

nah yang simple memang sesuai namanya, simple, bagaimana jika jumlah baris yang kita hitung tidak semua person, namun person dengan kriteria tertentu, untuk itu kita dapat menambahkan parameter seperti pada cuplikan berikut :

seperti pada kode baris 9 , kita dapat menambahkan varibael parameter @min_len_middle_name yang bertipe int dengan nilai default 3, sehingga untuk memanggilnya kita dapat menggunakan dengan berbagai cara seperti pada

  • baris 21 tanpa parameter yang berarti memberi nilai default yaitu 3
  • baris 23 dan 25 dengan memberi nilai pada parameter

Stored Procedure dengan nilai kembalian

kedua contoh stored procedure di atas menggunakan tabel sebagai tempat untuk melihat jalannya stored procedure, jika kita tidak menginginkan cara itu, kita dapat membuat stored procedure dengan mempunyai nilai kembalian seperti :

dari kode, saya membuat modifikasi, yaitu sekarang parameter tidak hanya 1 variabel, namun menjadi 3 variabel, dimana 2 variabel pertama adalah varibel input dan  varibel terakhir sebagai variabel output. Perbedaaannya adalah terletak pada keyword OUT yang ada di akhir variabel output.

untuk menjalankan stored procedure ini juga cukup mudah yaitu dengan membuat dulu variabel yang akan kita jadikan sebagai variabel output dan menggunakannya pada EXEC seperti pada 18,23,28.

cukup mudah bukan…

Function

Function itu mirip dengan storedprocedure , bedanya dia pasti punya nilai kembalian dan dijalankannya pada level query. Bagaimana maksudnya, maksudnya seperti :

dimana kita mengguakan pre defined function yaitu AVG untuk menghitung rata – rata pada kolom totaldue pada tabel salesorderheader.

function yang sudah pre defined disebut juga sebagai system functions dan untuk function buatan kita (user) disebut sebagai userd defined function atau disingkat sebagai UDF. contohnya adalah function berikut :

di contoh tersebut fungsi yang kita buat adalah mencari nilai minimum dari 2 variabel,  dan pada baris 22 , fungsi itu digunakan untuk mencari nilai minimum antara kolom freight dan subtotal pada tiap barisnya.

kegunaan lain dari function adalah untuk menghasilkan table , seperti pada contoh berikut :

di mana terlihat bahwa fungsi akan mengembalikan table yang merupakan hasil query. Perbedaannya adalah di sini fungsi tidak membutuhkan begin end melainkan langsung melakukan return table. Untuk memanggil fungsi jenis ini adalah dengan menganggap bahwa fungsi ini sebagai table, seperti pada baris 19.

so mudah bukan melakukan pembuatan fungsi..

happy coding all..

Gentle Introduction to TSQL

Di dunia per basis dataan kita mengenal sebuah bahasa pemrograman deklaratif [1] yaitu SQL. Berbeda sekali yang mungkin suka bergulat di dunia pemrograman ria.. yang mana bahasa yang lebih populer yaitu bahasa dengan paradigma pemrograman imperatif [2] seperti Java, c , c++, c# dan lainnya, walaupun ada juga bahasa yang sifatnya deklaratif misalnya saja haskel.

Sqlserver merupakan salah satu produk dari microsoft yang gencar sekali mengadopsi sql ini,selain linQ [3]. Namun dalam perkembangannya microsoft mungkin melihat dengan sql saja (dan sql spesific sqlserver) belum dapat menyelesaikan semua problem yang bisa saja baru dan kompleks, maka mereka menambahkan fitur baru di implementasi sql mereka yang mereka namakan TSQL (Transact-SQL) [4].

Dengan menggunakan TSQL, kita dapat melakukan pemrograman secara imperatif di environtment sqlserver yang tentunya hal ini akan memperkaya kemampuan sqlserver itu sendiri.

Memulai dengan santai

mari kita lihat cuplikan kode berikut :

Anda dapat mengetikkan cuplikan kode itu langsung di sqlserver management studio atau apapun yang menjadi sql client anda, anggap saja itu bagian dari query , mudah saja.

jika anda lihat pada baris 1, terdapat sebuah deklarasi variabel yang kita beri nama @vars yang akan bertipe float. Untuk tipe tipe lainnya dapat anda lihat di website microsoft [5].

lalu pada baris 3 akan terlihat operasi untuk memberi nilai pada variabel @vars dengan nilai 1, dan akhirnya kita akan tampilkan dalam baris ke 5.

cukup mudah bukan.. apalagi jika anda adalah seseorang dengan basic pemrograman imperatif yang cukup mumpuni pasti akan mudah mengikuti alur nya.

Mengambil nilai langsung dari data

pada cuplikan sebelumnya pada baris ke 3 kita memberi nilai adhoc yang merupakan contoh operasi pemberikan nilai, nah sekarang bagaimana jika kita menginginkan nilai dari variabel @vars berupa nilai yang kita ambil berasal dari data ?, cukup mudah rupanya mari kita lihat cuplikan berikut :

anda dapat lihat caranya benar benar mudah yaitu dengan yang saya lakukan di baris ke 6. Maka kita dapat lihat hasilnya dengan melakukan print di baris ke 8. Untuk melihat nilai variabel anda dapat menggunakan perintah print atau perintah select tergantung pada apa yang ingin anda hasilkan, jika ingin menghasilkan nilai berupa text saja ( akan tampil di tab message) maka anda cukup menggunakan print, namun jika ingin dijadikan sebuah tabel maka anda bisa menggunakan keyword select.

Percabangan itu mudah

Untuk melakukan percabangan caranya juga sangat mudah , seperti pada cuplikan berikut :

dimulai dari baris ke 9 untuk mengisi kondisi dari percabangan dan dilanjutkan dengan membuat begin – end pada baris 10 dan 12, hal ini juga sama dengan kondisi ‘else’ nya.

dalam banyak kasus saya memudahkan pengertian saya mengenai begin  – end dengan menganalogikannya dengan ‘{‘ ‘}’ pada pemrograman berbasis ‘{‘ seperti java , php .

contoh yang lain dapat kita lihat pada cuplikan berikut :

kali ini kita membuat 2 variabel, yang mana keduanya diambil dari data. pada baris ke 12 dan 16 dapat dilihat statement conditional yang menarik, kenapa menarik, karena mungkin tidak terbiasa bagi yang terbiasa menggunakan java atau php.

  1. dimana jika kita ingin melakukan conditional checking pada 2 kondisi dapat menggunakan keyword and
  2. untuk melakukan checking ‘!=’ kita dapat menggunakan ‘<>’
  3. untuk melakukan checking ‘==’ kita dapat menggunakan ‘=’

Perulangan yang juga mudah dilupakan

Pemrograman tanpa perulangan seperti sayur tanpa garam.. kurang mantap goyangannya..

mari kita lihat cuplikan berikut :

cara nya cukup mudah bukan ? , saya menggunakan metode paling simple dalam perulangan yaitu metode while do.

untuk baris 12 anda dapat melihat sebuah operasi baru yaitu cast. Dengan cast saya dapat mengubah sebuah tipe data variabel ke tipe data tertentu, dan yang saya lakukan adalah merubah tipe data variable tsb (int) ke dalam varchar agar dapat dilakukan proses concatenate dengan operator +. Sebenarnya kalo dilihat lagi mirip dan tidak mirip dengan java, di java kita dapat melakukan System.out.print(“Halo umur saya “+umur); , bedanya adalah di java terjadi automatic conversion antara tipe data tsb, dimana tipe data primiritf akan di ubah menjadi String, sedangkan jika tipe data referensional akan digunakan method toString().

Sepertinya contoh di atas terlalu simple, bagaiamana jika ingin melihat atau melakukan operasi yang langsung melibatkan data dari table ? mari kita lihat contoh yang lebih kompleks :

tujuan dari kode tsb adalah untuk menampilkan totaldue dari 10 transaksi terakhir.tahap pertama dapat anda lihat di baris 8 , saya menggunakan tipe data table dengan kolom yaitu nomor(int) dan id(int), yang bertujuan menyimpan nomor urut dan id dari salesorder. Untuk mengisi variabel ini, operasi insert into select digunakan, seperti pada baris 15, dan untuk mengisi nomor urut digunakan function row_number() yang sudah saya bahas pada postingan membuat query paging di sqlserver sebelumnya.  Dan proses utama terdapat di baris 25 – 29.

Perulangan menggunakan cursor

di Sqlserver kita dapat menggunakan cursor untuk melakukan perulangan seperti kode terakhir yang kita lihat. Contohnya sebagai berikut :

kita dapat mendefinisikan cursor seperti pada baris 7 dan 9, lalu dilanjutkan pada proses penggunaan cursor pada baris 16 yang sebelumnya kita buka pada baris 15. Proses tersebut akan memindahkan nilai query pada cursor dan disimpan pada sebuah variabel yang nanti akan kita tampilkan pada baris 20.

Beberapa catatan untuk penggunaan cursor ini adalah seringkali programmer lupa untuk menambahkan perintah yang sama pada baris 16 ke baris 22 (sebelum end) , hal itu dibutuhkan untuk cursor tetap berjalan.

catatan lainnya adalah jangan lupa untuk menutup dan melakukan deallocate pada variabel cursor tersebut.

mudah bukan TSQL.. , jadi gak perlu dikit dikit php , dikit dikit java …….

happy coding semua…

referensi

[1] https://en.wikipedia.org/wiki/Declarative_programming

[2] https://en.wikipedia.org/wiki/Imperative_programming

[3] https://msdn.microsoft.com/en-us/library/bb397926.aspx

[4] https://en.wikipedia.org/wiki/Transact-SQL

[5] https://msdn.microsoft.com/en-us/library/ms187752.aspx