Transact-SQL
Transact-SQL
Sebagian besar sistim manajemen database mengandung ekstensi yang dapat meningkatkan SQL dan menjadikannya lebih seperti bahasa pemrograman. SQL Server menyediakan sekumpulan pernyataan yang dikenal sebagai Transact-SQL (T-SQL). T-SQL mengenali pernyataan-pernyataan yang mengambil baris-baris dari satu atau lebih tabel, pernyataan alur kendali seperti IF.. ELSE dan WHILE, serta berbagai fungsi yang dapat digunakan untuk memanipulasi string, nilai numerik, tanggal, serupa dengan fungsi-fungsi Visual Basic. Dengan T-SQL anda bisa melakukan semua yang bisa dilakukan oleh SQL, serta memprogram operasi tersebut.
Kita akan menjelajahi T-SQL dan membuat prosedur tersimpan untuk melakukan tugas –tugas yang rumit pada server, hany dengan memanggil prosedur tersimpan dengan namanya dan akhirnya anda dapat melihat T-SQL secara lebih mendalam.
Klausa COMPUTE BY
Pernyataan SQL bisa menghasilkan perincian atau total, tetapi tidak keduanya, sebagai contoh kita dapat menghitung total pemesanan untuk semua pelanggan dengan klausa GROUP BY, tetapi klausa ini hanya menampilkan nilai total saja.
Misalnya, anda ingin menampilkan daftar semua pelanggan pada database northwind, melihat pesanan mereka, serta total untuk setiap pelanggan. Listing sql dibawah ini dapat mengambil informasi yang diinginkan.
--listing 1 Query OrderTotals.sql
USE NORTHWIND
SELECT CompanyName, Orders.OrderID,
SUM([Order Details].UnitPrice *
Quantity * (1 - Discount))
FROM Products, [Order Details], Customers, Orders
WHERE [Order Details].ProductID = Products.ProductID AND
[Order Details].OrderID = Orders.OrderID AND
Orders.CustomerID=Customers.CustomerID
GROUP BY CompanyName, Orders.OrderID
ORDER BY CompanyName, Orders.OrderID
Pernyataan SQL diatas menghitung total setiap pesanan, jika kita ingin melihat total per pelanggan, kita dapat memodifikasi listing sql diatas sebagai berikut :
--Listing 2 Query OrderTotals.sql tanpa field Orders.OrderID dari daftar SELECT dan klausa GROUP BY
USE NORTHWIND
SELECT CompanyName,
SUM([Order Details].UnitPrice *
Quantity * (1 - Discount))
FROM Products, [Order Details], Customers, Orders
WHERE [Order Details].ProductID = Products.ProductID AND
[Order Details].OrderID = Orders.OrderID AND
Orders.CustomerID=Customers.CustomerID
GROUP BY CompanyName
ORDER BY CompanyName
Dimana kita menghilangkan field Orders.OrderID dari daftar SELECT dan klausa GROUP BY.
Sekarang kita membutuhkan pernyataan yang dapat menghasilkan laporan perincian dengan pemenggalan total setiap pesanan dan setiap pelanggan, dengan T-SQL kita dapat melakukannya dan menyediakan solusi yang baik pada masalah yang dihadapi oleh Klausa Compute By.
Listing T-SQL untuk menghitung total setiap pesanan dan setiap pelanggan sebagai berikut :
--Listing 3 t-sql dengan Query OrderGrouped.sql
USE NORTHWIND
SELECT CompanyName, Orders.OrderID, ProductName,
UnitPrice=ROUND([Order Details].UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2)
FROM Products, [Order Details], Customers, Orders
WHERE [Order Details].ProductID = Products.ProductID And
[Order Details].OrderID = Orders.OrderID And
Orders.CustomerID=Customers.CustomerID
ORDER BY Customers.CustomerID, Orders.OrderID
COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2))
BY Customers.CustomerID, Orders.OrderID
COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2))
BY Customers.CustomerID
Klausa Compute By pertama pada baris di atas mengelompokan total baris invoice berdasarkan ID pesanan di dalam setiap pelanggan. Klausa Compute ke dua mengelompokan total yang sama berdasarkan pelanggan, yang ditunjukan pada listing 1 Query OrderTotals di atas . fungsi CONVERT() berfungsi mengubah tipe data, mirip dengan fungsi FORMAT() pada VB dan Fungsi ROUND() berfungsi membulatkan nilai bilangan pecahan.
Klausa Compute by bisa digunakan dengan semua fungsi yang telah kita pelajari. Kita akan mencoba menampilkan ID pesanan berdasarkan pelanggan dan menghitung nilai total dari invoice yang dikeluarkan ke setiap pelanggan.
--listing t-sql menampilkan ID pesanan berdasarkan pelanggan dan menghitung nilai total dari invoice yang dikeluarkan ke setiap pelanggan.
USE NORTHWIND
SELECT panyName, Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerID
COMPUTE COUNT(Orders.OrderID) BY Customers.CustomerID
SQL engine akan menghitung jumlah pesanan sementara field customerid tidak berubah. Apabila ia membaca pelanggan baru, total saat ini akan ditampilkan dan counter di reset menjadi nol untuk mengantisipasi pelanggan berikutnya
Prosedur Tersimpan
Adalah sebuah rutin yang ditulis dalam T-SQL yang memanipulasi baris-baris database. Semua pernyataan SQL yang telah anda pelajari sejauh ini di gunakan untuk memanipulasi baris (memilih, meng-update, menghapus baris), tetapi SQL tidak menyediakan cara untuk mengubah tindakan berdasarkan nilai-nilai pada field. Setelah prosedur tersimpan dimasukan ke dalam database, user dan aplikasi bisa memanggilnya seolah-olah ia merupakan prosedur tersimpan SQL atau pernyataan yang sudah built-in.
Membuat Dan Menjalankan Prosedur Tersimpan
Untuk menulis, men-debug dan menjalankan prosedur tersimpan pada database SQL Server, kita dapat menggunakan Query Analyzer. Untuk membuat sebuah prosedur tersimpan baru dan memasukannya ke dalam database , digunakan pernyataan create procedure dan sintaksnya adalah sebagai berikut:
CREATE PROCEDURE procedure_name
As
( procedure definition)
dimana procedure _name adalah nama dari prosedur tersimpan yang baru, dan blok pernyataan setelah kata kunci AS adalah badan dari prosedur tersebut. Kita akan membuat prosedur tersimpan untuk semua invoice.
--listing prosedur tersimpan AllInvoice
USE NORTHWIND
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'AllInvoices')
DROP PROCEDURE AllInvoices
GO
CREATE PROCEDURE AllInvoices
AS
SELECT CompanyName, Orders.OrderID, ProductName,
UnitPrice=ROUND([Order Details].UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2)
FROM Products, [Order Details], Customers, Orders
WHERE [Order Details].ProductID = Products.ProductID And
[Order Details].OrderID = Orders.OrderID And
Orders.CustomerID=Customers.CustomerID
ORDER BY Customers.CustomerID, Orders.OrderID
COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2))
BY Customers.CustomerID, Orders.OrderID
COMPUTE SUM(ROUND(CONVERT(money, Quantity * (1 - Discount) *
[Order Details].UnitPrice), 2))
BY Customers.CustomerID
Jalankan store prosedur tersimpan di Query Analyzer dengan melakukan :
Use Northwind
Exec AllInvoices
Mengapa menggunakan Prosedur Tersimpan ?
Prosedur Tersimpan lebih dari sekedar pemrograman biasa. Apabila ada pernyataan SQL, terutama yang rumit, disimpan dalam database, sistim manajemen database bisa menjalankannya dengan efisien. Apabila pernyataan SQL disimpan di dalam database sebagai sebuah prosedur, rencana eksekusinya akan dirancang sekali saja dan siap digunakan. Terlebih lagi, prosedur tersimpan bisa dirancang sekali, dites dan digunakan oleh lebih dari satu user dan aplikasi. Jika prosedur tersimpan yang sama digunakan oleh lebih dari satu user, DBMS hanya menyimpan satu salinan dari prosedur ini di dalam memori dan semua user membagi salinan yang sama dari prosedur tersebut
Bahasa T-SQL
Elemen dasar T-SQL sama seperti yang terdapat pada bahasa pemrograman lain : variabel, pernyataan alur kendali, serta fungsi.
□ Variabel T-SQL
T-SQL adalah bahasa bertipe, dimana setiap variabel harus dideklarasikan sebelum digunakan. T-SQL mendukung dus jenis variabel yaitu lokal dan global.
Variabel Lokal dideklarasikan di dalam kode prosedur tersimpan dan ruang lingkupnya terbatas di dalam prosedur tempat ia dideklarasikan saja. Variabel Global disebarkan oleh SQL Server dan kita bisa menggunakannya tanpa mendeklarasikannya dari dalam prosedur manapun.
□ Variabel Lokal dan Tipe Data
Variabel Lokal dideklarasikan dengan pernyataan DECLARE dan namanya harus diawali dengan karakter @. Berikut ini adalah nama-nama variabel yang sah : @CustomerID, @EmployeeID, @Avg_Discount. Sintaks dari deklarasi variabel lokal adalah :
DECLARE var_name var_type
Dimana var_name adalah nama variabel dan var_type adalah tipe data yang didukung oleh SQL Server.
• char, varchar
Variabel ini menyimpan karakter, panjangnya tidak boleh melebihi 8000 karakter. Variabel char dipenuhi dengan spasi sebanyak panjangnya, sedangkan variabel varchar tidak.
Contoh :
Declare @string1 char(20)
Declare @string2 varchar(20)
Set @string1 = ‘STRING1’
Set @string2=’STRING2’
Print ‘[‘+@string1+ ‘]’
Print ‘[‘+@string2+ ‘]’
Kedua pernyataan diatas akan menghasilkan :
[STRING1 ]
[STRING2]
• nchar, nvarchar
setara dengan char dan varchar tetapi digunakan untuk menyimpan string Unicode
• int, smallint, tinyint
Untuk menyimpan angka bulat (integer), tipe int menggunakan 4 byte dan bisa menyimpan nilai integer dari -2.147.483.648 sampai 2.147.483.647. Tipe smallint menggunakan 2 byte dan menyimpan nilai integer dari -32.768 sampai 32.767. Tipe tinyint menggunakan 1byte untuk menyimpan nilai dari 0 sampai 255
• decimal, numeric
Menyimpan nilai integer disebelah kiri titik desimal serta nilai pecahan disebelah kanan titik desimal.
Contoh
Declare @DecimalVar decimal(4, 3)
• datetime, smalldatetime
menyimpan nilai tanggal dan jam. Tipe datetima menggunakan 8 byte, dimana 4 byte untuk tanggal dan 4 byte untuk jam. Tipe smalldatetime menggunakan 4 byte, dimana 2 byte untuk menyimpan jumlah haru setelah 1 januari 1990 dan 2 byte untuk menyimpan jumlah menit setelah tengah malam
contoh pernyataan berikut yang menambahkan tanggal, jam dan menit ke dalam variabel datetime dan smalldatetime
--listing untuk menambahkan tanggal, jam dan menit ke variabel datetime dan smalldatetime
DECLARE @DateVar datetime
DECLARE @smallDateVar smalldatetime
PRINT 'datetime type'
Set @DateVar='1/1/2000 03:02.10'
PRINT @DateVar
--menambahkan hari ke variabel datetime
Set @DateVar=@DateVar +1
Print @DateVar
--Menambahkan jam ke variabel datetime
SET @DateVar=@DateVar+1.0/24.0
PRINT @DateVar
--Menambahkan menit ke variabel datetime
SET @DateVar=@DateVar+1.0/(24.0*60.0)
PRINT @DateVar
PRINT 'smalldatetime'
Set @smallDateVar='1/1/2000 03:02.10'
Print @smallDateVar
--menambahkan hari ke variabel smalldatetime
set @smallDateVar=@smallDateVar+1
print @smallDateVar
--Menambahkan jam ke variabel smalldatetime
set @smallDateVar=@smallDateVar+1.0/24.0
print @smallDateVar
• float, real
Dikenal sebagai tipe data pendekatan dan digunakan untuk menyimpan angka floating point
• money, smallmoney
gunakanlah kedua tipe ini untuk menyimpan nilai dollar. Kedua tipe ini menggunakan digit pecahan, tipe money menggunakan 8 byte dan mempunyai rentang nilai -922.337.203.685.477,5807 sampai 922.334.203.685.477,5807. tipe smallmoney mempunyai 4 byte
• text
Dapat menyimpan data non-unicode dengan panjang maksimal 2.147.483.647 karakter
• image
tipe data ini bisa menyimpan data binary sampai 2.147.483.647 byte
• binary
Variabel ini dapat menyimpan nilai binary. Kedua pernyataan dibawah ini mendeklarasikan dua variabel binary, yang satu panjangnya tetap dan lainya dengan panjang berubah-ubah
DECLARE @Var1 Binary(4), @Var2 varBinary
• bit
Untuk merepresentasikan nilai integer yang berupa 0 atau 1
• timestamp
mengandung nilai counter yang unik pada database (nilai yang menaik yang menyajikan tanggal dan jam)
• uniqueidentifier
merupakan nilai pengenal yang unik, diberikan pada sebuah kolom dengan fungsi NEWID(). Nilai ini diambil dari network card atau informasi lain yang berhubungan dengan komputer dan digunakan dalam skema replikasi untuk mengenali baris-baris
□ Variabel Global
Diawali dengan simbol @@. Nilai-nilai ini dipelihara oleh sistim dan kita bisa membacanya untuk mengambil informasi sistim.
• @@ FETCH_STATUS
Menghasilkan nilai nol jka pernyataan FETCH sukses mengambil baris dan bernilai bukan nol jika tidak. Dipasang setelah eksekusi pernyataan FETCH dan biasanya digunakan untuk mengakhiri loop WHILE yang membaca kursor. Dapat juga mengandung nilai -2 yang menunjukan bahwa baris yang ingin dibaca telah dihapus sejak kursor dibuat.
• @@CURSOR_ROWS, @@ROWCOUNT
@@CURSOR_ROWS akan mengembalikan jumlah baris pada cursor yang terakhir yang diibuka dan variabel
@@ROWCOUNT menghasilkan jumlah baris yang dipengaruhi oleh action query. Variabel @@rowcount biasanya digunakan dengan UPDATE, DELETE untuk mengetahui berapa banyak baris yang dipengaruhi oleh pernyataan SQL.
Contoh untuk mengetahui berapa banyak baris yang dipengaruhi oleh pernyataan update, cetaklah variabel @@rowcount setelah menjalankan pernyataan SQL :
Use Northwind
go
Update Customers
Set Phone='030'+ Phone
Where Country ='Germany'
Print @@ROWCOUNT
• @@ERROR
Mengembalikan nilai error untuk pernyataan SQL terakhir yang dijalankan. Jika nilai ini nol, maka pernyataan tersebut berhasil dijalankan dengan sukses
• @@IDENTITY
variabel ini mengembalikan nilai yang terakhir digunakan kolom identity
• Variabel Global Lainnya
bacalah dokumentasi online SQL Server untuk informasi lebih lanjut mengenai variabel global lainnya.
Contoh Membuat store procedure dasar untuk Shippers
--listing store procedure Shippers
use Northwind
go
create proc spShippers
as
Select * from Shippers
Jalankan dengan query analyzer :
Exec spShippers
Hasilnya adalah sebagai berikut :
ShipperID CompanyName Phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931
membuat store procedure menggunakan sedikit parameter input untuk membuat suatu record baru dalam tabel Shippers :
--listing menambahkan suatu record baru dengan peirntah insert into
use Northwind
Go
create proc spInsertShipper
@CompanyName nvarchar(40),
@Phone nvarchar(24)
as
insert into Shippers
values
(@CompanyName,@Phone)
jalankan spInsertShipper dengan query analyzer :
--listing menambahkan 1 record
exec spInsertShipper 'Speedy Shipper, Inc.','(503) 555-5566'
--Jalankan perintah ini di query analyzer
exec spShippers
hasil dari eksekusi perintah exec spShippers
ShipperID CompanyName Phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931
4 Speedy Shipper,Inc. (503)555-5566
Menyediakan Nilai Default
Kali ini kita mencoba untuk tidak memenuhi permintaan phone number dimana phone number kita berikan nilai NULL
--listing nilai default untuk spInsertShipperOptionalPhone
use Northwind
Go
create proc spInsertShipperOptionalPhone
@CompanyName nvarchar(40),
@Phone nvarchar(24) = NULL
as
insert into Shippers
values
(@CompanyName,@Phone)
Jalankan perintah dibawah ini dengan Query Analyzer ;
exec spInsertShipperOptionalPhone 'Speedy Express Shippers, Inc'
exec spInsertShipperOptionalPhone
Hasilnya adalah :
ShipperID CompanyName Phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931
4 Speedy Shipper,Inc. (503)555-5566
5 Speedy Express Shippers, Inc Null
Menciptakan Parameter Keluaran
Kadang-Kadang, Anda ingin melewati keluar informasi non-recordset untuk apapun juga yang dipanggil dengan store procedure (sproc) anda. Satu contoh dari ini akan menciptakan suatu versi modifikasi dari dua sproc terakhir kita. Anggaplah, sebagai contoh, bahwa kita sedang melakukan suatu masukkan/menyisipkan sesuatu ke dalam tabel ( seperti kita lakukan pada contoh yang terakhir), tetapi kita sedang merencanakan untuk melakukan pekerjaan tambahan menggunakan record yang disisipkan. atau lebih secara rinci, barang-kali kita sedang menyisipkan suatu record baru ke dalam Tabel Order di dalam Northwind, tetapi kita juga harus menyisipkan record detil di dalam tabel detil order. Dalam rangka memelihara hubungannya tetap utuh, kita harus mengetahui identitas Record Order sebelum kita dapat melakukan penyisipan ke dalam Tabel Detil Order.
Ketika spInsertShipper dilakukan maka store procedure akan hampir terlihat sama, kalau tidak akan mempunyai parameter yang bertemu dengan kolom yang berbeda di dalam tabel dan yang paling penting dari semua itu akan mempunyai suatu parameter keluaran untuk nilai identitas yang dihasilkan oleh suatu penyisipan.
--listing spInsertOrder
use Northwind
go
create proc spInsertOrder
@CustomerID nvarchar(5),
@EmployeeID int,
@OrderDate datetime = NULL,
@RequiredDate datetime = NULL,
@ShippedDate datetime = NULL,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40) = NuLL,
@ShipAddress nvarchar(60) = Null,
@ShipCity nvarchar(15) = Null,
@ShipRegion nvarchar(15) = Null,
@ShipPostalCode nvarchar(10) = Null,
@ShipCountry nvarchar(15) = Null,
@OrderID Int OUTPUT
AS
--create the new record
insert into Orders
Values
(
@CustomerID,
@EmployeeID,
@OrderDate,
@RequiredDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode ,
@ShipCountry
)
--move the identity value from the newly inserted record into our output variable
Select @OrderID=@@identity
Sekarang, mari kita mencoba yang dibawah ini, hanya waktu ini, mari kita menetapkan nilai-nilai parameter dengan acuan bukannya oleh posisi. Di dalam perintah untuk melihat bagaimana parameter keluaran sedang bekerja, mereka juga akan membutuhkan untuk menulis kode test yang sedikit di dalam catatan/script yang store procedures laksanakan.
--listing untuk menjalankan penyisipan spInsertOrder
use Northwind
go
declare @MyIdent int
exec spInsertOrder
@CustomerID='ALFKI',
@EmployeeID=5,
@OrderDate='5/1/2004',
@ShipVia=3,
@Freight=5.00,
@OrderID=@MyIdent Output
Select @MyIdent as IdentityValue
Select OrderID, CustomerID, EmployeeID, OrderDate, ShipName
From Orders
Where OrderID=@MyIdent
Menghasilkan
IdentityValue
1 11079
OrderID CustomerID EmployeeID OrderDate ShipName
1 11079 ALFKI 5 2004-05-01 00:00:00.000 NULL
Kendali Statemen Aliran (Control of Flow Statements)
Kendali Statemen Aliran adalah harus dijamin kebenarannya untuk bahasa program manapun sampai dengan hari ini. Kita tidak bisa membayangkan mempunyai suatu kode yang ditulis dimana kita tidak dapat bisa merubah perintah apa yang dijalankan tergantung pada suatu kondisi. T-SQL menawarkan banyak aneka pilihan yang klasik untuk kendali situasi aliran, mencakup:
□ IF .. ELSE
□ GOTO
□ WHILE
□ WAITFOR
Kita Juga mempunyai statemen CASE ( SELECT CASE, DO CASE, dan SWITCH/BREAK di dalam bahasa yang lain ), tetapi ini tidak mempunyai tingkatan pengendalian dari kemampuan aliran yang anda sudah datang ke ahli dari bahasa lainnya
Pernyataan IF …ELSE
Sintaks dasar :
IF
7
select @OrderDate = NULL
-- create the new record
insert into Orders
values
(
@CustomerID,
@EmployeeID,
@OrderDate,
@RequiredDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)
/* MOVE THE IDENTITY VALUE FROM THE NEWLY INSERTED RECORD
INTO OUR OUTPUT VARIABLE */
select @OrderID = @@identity
-- listing menjalankan spInsertDateValidateOrder
Use Northwind
go
declare @MyIdent Int
exec spInsertDateValidateOrder
@CustomerID = 'ALFKI',
@EmployeeID = 5,
@OrderDate = '5/1/1999',
@ShipVia = 3,
@Freight = 5.00,
@OrderID = @MyIdent OUTPUT
Select @MyIdent as IdentityValue
Select OrderID, CustomerID, EmployeeID, OrderDate, ShipName
from Orders
where OrderID=@MyIdent
Hasil Menjalankan spInserDateValidateOrder
IdentityValue
1 11080
OrderID CustomerID EmployeeID OrderDate ShipName
1 11080 ALFKI 5 NULL NULL
Sungguhpun kita menyediakan tanggal/date yang sama sebagai waktu yang lalu ( 5/1/1999), tidak nilai yang telah dimasukkan/disisipi- Pernyataan IF kita menembak mati nilai yang tidak sah dan mengubahnya sebelum memasukkan/menyisipkan.
Prosedur Tersimpan Mengunakan Argumen
Prosedur Tersimpan tidak akan dapat berbuat apa-apa jika tidak mampu menerima argumen. Jika Prosedur Tersimpan diimplementasikan sebagai fungsi dapat menerima satu atau lebih argumen dan mengembalikan satu atau lebih nilai kepada pemanggilnya.
Sintaksnya adalah :
CREATE PROCEDURE procedure_name
@argument1 type1, @argument2 type2, ….
AS
--Listing untuk menunjukan prosedur tersimpan yang menerima dua argumen datetime dan mengembalikan pesanan yang dilakukan pada interval waktu tertentu.
use Northwind
go
Create procedure OrdersByDate
@StartDate datetime, @EndDate datetime
AS
Select * from Orders
Where OrderDate BETWEEN @StartDate AND @EndDate
Jalankan prosedur tersimpan diatas dan kemudian tuliskan kembali listing dibawah ini untuk menguji prosedur tersimpan diatas dan jalankanlah baris-baris dibawah ini dan lihat hasilnya
use Northwind
go
Declare @date1 datetime
Declare @date2 datetime
SET @date1='1/1/1997'
Set @date2='3/31/1997'
execute OrdersByDate @date1, @date2
Pesanan yang dilakukan pada kuartal pertama tahun 1997 akan muncul pada panel results.
Sekarang kita tambahkan parameter output kedalam prosedur tersimpan dan kita akan meminta jumlah pesanan yang dilakukan pada interval waktu yang sama. Berikut ini adalah prosedur tersimpan CountOrdersByDate :
use Northwind
go
create procedure CountOrdersByDate
@startDate datetime, @EndDate datetime,
@CountOrders int Output
AS
Select @CountOrders=Count(OrderID) from Orders
where OrderDate Between @StartDate and @EndDate
Ujilah prosedur diatas dengan menuliskan ;
use Northwind
go
declare @date1 datetime
declare @date2 datetime
set @date1='1/1/1997'
set @date2='3/31/1997'
declare @orderCount int
execute CountOrdersByDate @date1, @date2, @orderCount output
print ' There were ' + convert(varchar(5), @orderCount)+ ' Orders Placed in the Chosen Interval'
hasilnya adalah :
There were 92 Orders Placed in the Chosen Interval
□ TRIGGER
Merupakan prosedur tersimpan khusus yang digunakan untuk tugas-tugas administratif dan dijalankan oleh SQL Server secara otomatis bila terjadi aksi tertentu seperti menyisipkan, menghapus dan mengupdate baris. Trigger bisa dipanggil langsung oleh aplikasi MS VB dan perannya juga sangat penting dalam memelihara database SQL Server. Trigger biasanya digunakan untuk mencatat perubahan di dalam database seperti ingin mengetahui siapa yang melakukan apa pada data penting perusahaan, kita bisa menambahkan beberapa field kedalam tabel Orders dan mencatat informasi user serta waktu setiap kali ada record ditambah, dihapus dan diupdate.
Sintaksnya adalah sebagai berikut :
CREATE TRIGGER trigger_name
On Table
[WITH ENCRYPTION]
FOR [DELETE] [,] [INSERT] [,] [UPDATE]
[NOT FOR REPLICATION]
AS
Block of T-SQL Statement
Kata kunci [WITH ENCRYPTION] memberi tahu SQL Server untuk Menyimpan Trigger dalam Format yang Terekkripsi, sehingga user tidak bisa membacanya. Kata kunci [NOT REPLICATION] menandakan bahwa trigger tidak dapat dijalankan apabila ada proses replikasi memodifikasi tabel yang trelibat di dalam trigger. Setelah kata kunci AS adalah blok pernyataan yang mendefinisikan aksi trigger.
• Menerapkan Trigger
Kita akan mencoba untuk membuat sebuah trigger dengan aksi penyisipan, update dan penghapusan pada tabel Orders di dalam database Northwind. Trigger EditOrder dan NewOrder akan mengupdate field-field yang EditedBy/EditedOn dan AddedBy/AddedOn. Field EditedBy dan EditedOn menyimpan nama user dan yang menyunting pesanan serta tanggal dan jamnya. Field AddedBy dan AddedOn menyimpan informasi yang sama tetapi diterapkan untuk penambahan baris-baris baru. Apabila ada sebuah baris dihapus, kita kan menyimpan data yang sama (nama user serta tanggal aksi tersebut dilakukan) serta ID pesanan, ID pelanggan dn tanggal Pesanan pada sebuah tabel baru. Informasi ini akan disimpan di dalam tabel Deletions yang menyimpan informasi mengenai pesanan yang telah dihapus.
Tambahkanlah field baru kedalam tabel Orders pada database NWnamanda (misal Nwbambangm) sebagai berikut :
EditedBy varchar(20)
EditedOn datetime
AddedBy varchar(20)
AddedOn datetime
Tambahkanlah sebuah tabel baru yang bernama Deletions di dalam database Nwnamanda,
DeletedBy varchar(20)
DeletedOn datetime
DelOrderID int
DelCustomerID char(5)
DelOrderDate datetime
Tuliskanlah Listing EditOrderTrigger dibawah ini pada Query Analyzer dan jalankan serta perhatikan hasilnya
--listing EditOrderTrigger
create trigger EditOrderTrigger On [Orders]
For Update
As
Declare @OrderID char(5)
Select @OrderID = OrderID From inserted
Update Orders set EditedOn=GetDate(), EditedBy=USER
where Orders.OrderID=@OrderID
--listing NewOrderTrigger
create trigger NewOrderTrigger On [Orders]
For Insert
As
Declare @NewOrderID char(5)
Select @NewOrderID = OrderID From inserted
Update Orders set AddedOn=GetDate(), AddedBy=USER
where Orders.OrderID=@NewOrderID
--Listing DeleteOrderTrigger
Create Trigger DeleteOrderTrigger on [Orders]
For Delete
as
Declare @DelOrderId int
Declare @DelCustID char(5)
Declare @DelOrderDate datetime
Select @DelOrderID=OrderID from Deletions
Select @DelCustID=CustomerID from Deletions
Select @DelOrderDate=OrderDate from Deletions
insert Deletions (DeleteOn, DeletedBy,
DelOrderID, DelCustomerID,DelOrderDate)
values (GetDate(), USER, @DelOrderID,
@DelCustID, @DelOrderDate)
--Listing DeleteOrderTrigger
Create Trigger DeleteOrderTrigger on [Orders]
For Delete
as
Declare @DelOrderId int
Declare @DelCustID char(5)
Declare @DelOrderDate datetime
Select @DelOrderID=OrderID from Deletions
Select @DelCustID=CustomerID from Deletions
Select @DelOrderDate=OrderDate from Deletions
insert Deletions (DeleteOn, DeletedBy,
DelOrderID, DelCustomerID,DelOrderDate)
values (GetDate(), USER, @DelOrderID,
@DelCustID, @DelOrderDate)
Untuk menguji trigger ini, lakukanlah penambahan, penghapusan dan penyuntingan baris-baris pada tabel Orders. Apabila anda perhatikan anda tidak bisa menghapus baris dari Tabel Orders kecuali baris-baris yang berhubungan pada Tabel Order Details juga sudah dihapus. Anda bisa menambahkan sebuah baris palsu ke dalam tabel Orders lalu menyuntingnya dan terakhir menghapusnya. Lalu Bukalah Tabel Orders dan Deletions untuk melihat baris-baris yang ditambahkan oleh trigger.
• Menggunakan Trigger Untuk Menerapkan Penyuntingan dan Penghapusan Bertingkat
SQL Server tidak bisa melakukan secara otomatis untuk menghapus suatu tabel apabila terdapat hubungan antar tabel secara langsung dan kita dapat membuat sebuah trigger untuk melakukannya. Untuk itu kita akan membuat suatu pemicu (trigger) yang akan menghapus suatu tabel Orders dan sekaligus menghapus juga tabel Order Details.
--Listing CascadeOrderDeletes
Create trigger CascadeOrderDeletes on Orders
For Delete
As
Declare @OrderID int
Select @OrderID=OrderID from Deletions
Delete [Order Details]
From [Order Details]
Where OrderID=@OrderID
Untuk menguji trigger ini, kita harus memastikan bahwa hubungan antara tabel Orders dan Order Details tidak diterapkan. Untuk menghapus hubungan antara kedua tabel, bukalah tabel Orders pada tampilan dengan SQL Enterprise Manager, kemudian klik Tombol Tabel and Index Properties pada toolbal untuk membuka jendela properties.
Pada Tab Relationship pilih hubungan FK-Order-Order Details-Orders dan menghapus pilihan Enable Relationship For Insert and Update
Contoh Kasus lain
Kita akan menghapus baris dari Tabel Customers yang akan mempengaruhi integritas database, jika pelanggan tertentu dihubungkan ke satu atau lebih pesanan.
--Listing yang akan menghapus semua pesanan yang dihubungkan dengan pelanggan yang ingin dihapus
create trigger CascadeCustomerDeletes on Customers
For Delete
AS
Declare @CustID nchar(5)
Select @CustID=CustomerID from Deleted
Delete from Orders Where CustomerID=@CustID
Jika anda menghapus seorang pelanggan, triggerCascadeCustomerDeletes akan dijalankan. Saat dihapus baris-baris yang terhubung pada Tabel Orders, akan memacu trigger CascadeCustomerDeletes untuk melakukan penghapusan baris-baris perincian pada pesanan yang dihapus. Jadi dengan menghapus satu baris pada tabel Customers, kita telah menghapus beberapa baris dari tabel Orders dan lebih banyak lagi pada Tabel Order Details
• Transaksi
Merupakan serangkaian operasi database yang harus berhasil atau gagal secara keseluruhan. Jika semua operasi berhasil dengan sukses, maka seluruh transaksi dianggap sukses dan perubahan disimpan dalam database. Jika ada operasi yang gagal, maka seluruh transaksi dianggap gagal dan perubahan tidak akan disimpan. SQL menerapkan transaksi dengan 3 (tiga) pertanyaan yaitu : BEGIN TRANSACTION, COMMIT TRANSACTION DAN ROLLBACK TRANSACTION
• BEGIN TRANSACTION
Menandai awal dari sebuah transaksi. Jika gagal, tabel akan dikembalikan ke status awal pada saat begin transaction dijalankan
• COMMIT TRANSACTION
Menandai keberhasilan dari sebuah transaksi dan semua perubahan yang dibuat ke dalam database oleh aplikasi sejak dijalankannya Begin Transaction akan disimpan ke dalam database, kita bisa membatalkan perubahan nanti, tetapi bukan sebagai bagian dari transaksi
• ROLLBACK TRANSACTION
Menandai akhir dari transaksi yang gagal. Apabila pernyataan ini dijalankan, database akan dikembalikan ke status semula pada saat pernyataan Begin transaction dijalankan.
Bagian kode berikut menunjukan contoh bagaimana pernyataan yang berhubungan dengan transaksi digunakan di dalam batch :
Begin Transaction
{ T-SQL statement}
if @@ERROR 0
begin
Rollback Transaction
Return -100
End
{T-SQL statement}
IF @@ERROR 0
Begin
Rollback Transaction
Return -101
End
Commit transaction
{more t-sql statement}
□ Menambahkan Pelanggan
Kita akan membuat prosedur tersimpan untuk menambah pelanggan. Prosedur tersimpan ini menerima kolom-kolom dari Tabel Customers sebagai argumen dan menyisipkannnya ke dalam baris baru. Penambahan baris baru dilakukan dengan INSERT, jika sukses, kode error nol akan dihasilkan dan jika penyisipan gagal, prosedur akan mengembalikan kode error yang dihasilkan oleh pernyataan INSERT.
Diawal program dideklarasikan semua argumen input kemudian menggunakan argumen-argumen ini sebagai nilai pada pernyataan INSERT yang menambahkan baris baru ke dalam tabel Customers. Jika INSERT error, prosedur tidak akan crash. Prosedur akan memeriksa nilai dari variable global @@ERROR. Jika Variabel Gloabal @@ERROR bukan nol, berarti terjadi error. Jika sukses variabel @@ERROR akan bernilai Nol. Perhatikan bahwa @@ERROR disimpan dalam variabel lokal yang digunakan nanti pada kode. Variabel @@ERROR diubah setiap kali eksekusi baris. Jika kita mengembalikan nilai @@ERROR dengan RETURN, aplikasi yang memanggil akan menerima kode error yang selalu bernilai nol. Dan itu sebabnya kita harus menyimpan nilai @@ERROR setelah operasi jika ingin menggunakannya nanti pada kode program
--listing AddCustomer
USE NORTHWIND
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'AddCustomer')
DROP PROCEDURE AddCustomer
GO
CREATE PROCEDURE AddCustomer
@custID nchar(5), @custName nvarchar(40),
@custContact nvarchar(30), @custTitle nvarchar(30),
@custAddress nvarchar(60), @custcity nvarchar(15),
@custRegion nvarchar(15), @custPostalCode nvarchar(10),
@custCountry nvarchar(15),
@custPhone nvarchar(24), @custFax nvarchar(24)
AS
DECLARE @ErrorCode int
INSERT Customers (CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region,
PostalCode, Country, Phone, Fax)
VALUES (@custID, @custName, @custContact,
@custTitle, @custAddress,
@custCity, @custRegion, @custPostalCode, @custCountry,
@custPhone, @custFax)
SET @ErrorCode=@@ERROR
IF (@ErrorCode = 0)
RETURN (0)
ELSE
RETURN (@ErrorCode)
Batch AddACustomer akan menambahkan pelanggan baru hanya pada saat pertama kali dijalankan. Jika anda menjalankannya lagi tanpa mengubah ID pelanggan akan muncul kode error 2627
--listing AddACustomer
DECLARE @retCode int
DECLARE @custID nchar(5), @custName nvarchar(40)
DECLARE @custContact nvarchar(30)
DECLARE @custTitle nvarchar(30), @custAddress nvarchar(60)
DECLARE @custCity nvarchar(15), @custCountry nvarchar(15)
DECLARE @custPostalCode nvarchar(10), @custRegion nvarchar(15)
DECLARE @custPhone nvarchar(24), @custFax nvarchar(24)
-- Set customer data
SET @custID="SYBEX"
SET @custName="Sybex Inc."
SET @custContact="Tobias Smythe"
SET @custTitle="Customer Representative"
SET @custAddress="1000 Marina Village"
SET @custCity="Alameda"
SET @custRegion="CA"
SET @custPostalCode="90900"
SET @custCountry="USA"
SET @custPhone="(714) 3258233"
SET @custFax="(714) 3258233"
-- Call stored procedure to add new customer
EXECUTE @retCode = AddCustomer @custID, @custName, @custContact,
@custTitle, @custAddress, @custCity,
@custRegion, @custPostalCode, @custCountry,
@custPhone, @custFax
PRINT @retCode
□ Menambahkan Pesanan
Kali ini kita akan menulis sebuah prosedur tersimpan untuk menambahkan pesanan baru dan harus melakukan banyak pengujian dan mungkin dapat keluar dari seluruh operasi pada berbagai tahap tertentu. Prosedur tersimpan NewOrder harus menerima ID pelanggan, IDPegawai, Idpengirim, dan alamat pengirim serta perincian pesanan. Jika pelanggan, pegawai dan pengirim tidak ada, maka prosedur akan membatalkan eksekusinya dan menghasilkan kode error kepada pemanggil. Jika salah satu dari pengujian gagal maka prosedur tersimpan akan mengembailkan kode error yang tepat (misal -100 jika pelanggan tidak ada, -101 jika pegawai tidak ada dan -102 jika pengirim tidak ada).
--listing Prosedur tersimpan NewOrder
USE NORTHWIND
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'NewOrder')
DROP PROCEDURE NewOrder
GO
CREATE PROCEDURE NewOrder
@custID nchar(5), @empID int, @orderDate datetime,
@shipperID int, @Details varchar(1000)
AS
DECLARE @ErrorCode int
DECLARE @OrderID int
-- Add new row to the Orders table
DECLARE @shipcompany nvarchar(40)
DECLARE @shipAddress nvarchar(60), @shipCity nvarchar(15)
DECLARE @shipRegion nvarchar(15), @shipPCode nvarchar(10)
DECLARE @shipCountry nvarchar(15)
SELECT @shipCompany=CompanyName,
@shipAddress=Address,
@shipCity=City,
@shipRegion=Region,
@shipPCode=PostalCode,
@shipCountry=Country
FROM Customers
WHERE CustomerID = @custID
IF @@ROWCOUNT = 0
RETURN(-100) -- Invalid Customer!
SELECT * FROM Employees WHERE EmployeeID = @empID
IF @@ROWCOUNT = 0
RETURN(-101) -- Invalid Employee!
SELECT * FROM Shippers
WHERE ShipperID = @shipperID
IF @@ROWCOUNT = 0
RETURN(-102) -- Invalid Shipper!
BEGIN TRANSACTION
INSERT Orders (CustomerID, EmployeeID, OrderDate, ShipVia,
ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry)
VALUES (@custID, @empID, @orderDate, @ShipperID,
@shipCompany, @shipAddress, @ShipCity, @ShipRegion,
@shipPCode, @shipCountry)
SET @ErrorCode=@@ERROR
IF (@ErrorCode 0)
BEGIN
ROLLBACK TRANSACTION
RETURN (-@ErrorCode)
END
SET @OrderID = @@IDENTITY
-- Now add rows to the Order Details table
-- All new rows will have the same OrderID
DECLARE @TotLines int
DECLARE @currLine int
SET @currLine = 0
-- Use the CEILING function because the length of the
-- @Details variable may be less than 18 characters long !!!
SET @TotLines = Ceiling(Len(@Details)/18)
DECLARE @Qty smallint, @Dscnt real, @Price money
DECLARE @ProdID int
WHILE @currLine Pilih nwnamanda (misal nwbambang)>Pilih StoreProcedures > kemudian klik kanan> Pilih New Store Procedures
d. Tampil Dialog Menu Store Procedures Properties > Pada Text tuliskan store procedure (sudah ada diatas tidak perlu ditulis kembali) sbb :
use nwbambang
if exists (select name from sysobjects
where name='AddCustomer')
drop procedure AddCustomer
go
create procedure AddCustomer
@custID nchar(5), @custName nvarchar(40),
@custContact nvarchar(30), @custTitle nvarchar(30),
@custAddress nvarchar(60), @custCity nvarchar(15),
@custRegion nvarchar(15), @custPostalCode nvarchar(10),
@custCountry nvarchar(15),
@custPhone nvarchar (24), @custFax nvarchar(24)
as
declare @errorCode int
Insert Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address,
City, Region, PostalCode, Country, Phone, Fax)
Values (@custID, @custName, @custContact,
@custTitle, @custAddress,
@custCity, @custRegion, @custPostalCode, @custCountry,
@custPhone, @custFax)
set @errorCode=@@error
if (@errorcode=0)
return (0)
else
return (@errorCode)
e. Lakukan Ujicoba Store Procedure Dengan Mengetikan Informasi Sbb (sudah ada diatas tidak perlu ditulis kembali:
use nwbambang
declare @retCode int
declare @custID nchar(5), @custName nvarchar(40)
declare @custContact nvarchar(30)
declare @custTitle nvarchar(30), @custAddress nvarchar(60)
declare @custCity nvarchar(15), @custCountry nvarchar(15)
declare @custPostalCode nvarchar(10), @custRegion nvarchar(15)
declare @custPhone nvarchar (24), @custFax nvarchar(24)
-- Set customer data
set @custID='SYBEX'
set @custName='Sybex Inc'
set @custContact='Tobias Smythe'
set @custTitle='Customer Representative'
set @custAddress='1000 Marina Village'
set @custCity='Alameda'
set @custRegion='CA'
set @custPostalCode='90900'
set @custCountry='USA'
set @custPhone='(714) 5558234'
set @custFax='(714) 5558234'
-- Call stored procedure to add new customer
execute @retCode= AddCustomer @custID, @custName, @custContact,
@custTitle, @custAddress, @custCity,
@custRegion, @custPostalCode,@custCountry,
@custPhone, @custFax
Print @retCode
f. Simpan storeprocedur ini dengan nama AddACustomer
g. Jalankan Store Procedure dan lihat hasilnya
II. Membuat Aplikasi AddCustomer
1. Jalankan Progam Visual Basic, Pilih Standar.Exe, pilih Open
2. Berilah properti Form sebagai berikut : Name = AddCustomerForm dan Caption=AddCustomer Stored Procedure Demo serta MaxButton=True
|Control |Name | |
| | |Properties |
|Label |lblFieldLabel |Caption=CustomerID |
|Textbox |txtCustomerID |DataField=CustomerID,DataMember=Command1,DataSource=DataEnvironment1 |
|Label |lblFieldLabel |Caption=Company |
|Textbox |txtCompanyName |DataField=CompanyName,DataMember=Command1,DataSource=DataEnvironment1 |
|Label |lblFieldLabel |Caption=ContactName |
|TextBox |txtContactName |DataField =ContactName, DataMember=Command1, DataSource=DataEnvironment1 |
|Label |lblFieldLabel |Caption=ContactTitle |
|Textbox |txtContactTitle |DataField=ContactTitle,DataMember=Command1,DataSource=DataEnvironment1 |
|Label |lblFieldLabel |Caption=Address |
|Textbox |txtAddress |DataField=Address,DataMember=Command1,DataSource=DataEnvironment1 |
|Label |lblFieldLabel |Caption=City |
|TextBox |txtCity |DataField =City, DataMember=Command1, DataSource=DataEnvironment1 |
|Label |lblFieldLabel |Caption=Region |
|Textbox |txtRegion |DataField=Region,DataMember=Command1,DataSource=DataEnvironment1 |
|Label |lblFieldLabel |Caption=PostalCode |
|Textbox |txtPostalCode |DataField=PostalCode,DataMember=Command1,DataSource=DataEnvironment1 |
|Label |lblFieldLabel |Caption=Country |
|TextBox |txtCountry |DataField =Country, DataMember=Command1, DataSource=DataEnvironment1 |
|Label |lblFieldLabel |Caption=Phone |
|Textbox |txtPhone |DataField=Phone,DataMember=Command1,DataSource=DataEnvironment1 |
|Label |lblFieldLabel |Caption=Fax |
|Textbox |txtFax |DataField=Fax,DataMember=Command1,DataSource=DataEnvironment1 |
|CommandButton |bttnAddCustomer |Caption=Add New Customer |
| | | |
Listing commandbutton addcustomer :
Private Sub bttnAddCustomer_Click()
Dim ADOConn As New ADODB.Connection
Dim oParam As ADODB.Parameter
Dim ADOCmd As mand
Dim ADOError As ADODB.Error
ADOConn.Open "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=NorthWind;Data Source=WINSERVER2000"
Set ADOCmd = New mand
ADOCmd.ActiveConnection = ADOConn
' The first parameter will hold the
' stored procedure's return value
Set oParam = ADOCmd.CreateParameter
oParam.Name = "RETURN_VALUE"
oParam.Type = adInteger
oParam.Direction = adParamReturnValue
ADOCmd.Parameters.Append oParam
' CustomerID
Set oParam = ADOCmd.CreateParameter
oParam.Name = "CustID"
oParam.Type = adChar
oParam.Size = 5
oParam.Direction = adParamInput
oParam.Value = txtCustomerID.Text
ADOCmd.Parameters.Append oParam
' CompanyName
Set oParam = ADOCmd.CreateParameter
oParam.Name = "CustName"
oParam.Type = adVarChar
oParam.Size = 40
oParam.Direction = adParamInput
oParam.Value = txtCompanyName.Text
ADOCmd.Parameters.Append oParam
' ContactName
Set oParam = ADOCmd.CreateParameter
oParam.Name = "custContact"
oParam.Type = adVarChar
oParam.Size = 30
oParam.Direction = adParamInput
oParam.Value = txtContactName.Text
ADOCmd.Parameters.Append oParam
' ContactTitle
Set oParam = ADOCmd.CreateParameter
oParam.Name = "CustTitle"
oParam.Type = adVarChar
oParam.Size = 30
oParam.Direction = adParamInput
oParam.Value = txtContactTitle.Text
ADOCmd.Parameters.Append oParam
' Address
Set oParam = ADOCmd.CreateParameter
oParam.Name = "CustAddress"
oParam.Type = adVarChar
oParam.Size = 60
oParam.Direction = adParamInput
oParam.Value = txtAddress.Text
ADOCmd.Parameters.Append oParam
' City
Set oParam = ADOCmd.CreateParameter
oParam.Name = "CustCity"
oParam.Type = adVarChar
oParam.Size = 15
oParam.Direction = adParamInput
oParam.Value = txtCity.Text
ADOCmd.Parameters.Append oParam
' Region
Set oParam = ADOCmd.CreateParameter
oParam.Name = "CustRegion"
oParam.Type = adVarChar
oParam.Size = 15
oParam.Direction = adParamInput
oParam.Value = txtRegion.Text
ADOCmd.Parameters.Append oParam
' PostalCode
Set oParam = ADOCmd.CreateParameter
oParam.Name = "CustPostalCode"
oParam.Type = adVarChar
oParam.Size = 10
oParam.Direction = adParamInput
oParam.Value = txtPostalCode.Text
ADOCmd.Parameters.Append oParam
' Country
Set oParam = ADOCmd.CreateParameter
oParam.Name = "CustCountry"
oParam.Type = adVarChar
oParam.Size = 15
oParam.Direction = adParamInput
oParam.Value = txtCountry.Text
ADOCmd.Parameters.Append oParam
' Phone
Set oParam = ADOCmd.CreateParameter
oParam.Name = "CustPhone"
oParam.Type = adVarChar
oParam.Size = 24
oParam.Direction = adParamInput
oParam.Value = txtPhone.Text
ADOCmd.Parameters.Append oParam
' FAX
Set oParam = ADOCmd.CreateParameter
oParam.Name = "CustFax"
oParam.Type = adVarChar
oParam.Size = 24
oParam.Direction = adParamInput
oParam.Value = txtFax.Text
ADOCmd.Parameters.Append oParam
mandText = "AddCustomer"
mandType = adCmdStoredProc
On Error GoTo ExecError
ADOCmd.Execute
MsgBox "Customer added successfully!"
Set ADOCmd = Nothing
Set ADOConn = Nothing
Exit Sub
ExecError:
msg = "Could not add new customer." & vbCrLf & _
"Error(s) returned by the provider:" & vbCrLf
msg = msg & ADOConn.Errors(0).Description & vbCrLf
MsgBox msg
Set ADOCmd = Nothing
Set ADOConn = Nothing
End Sub
• Memanggil Prosedur Tersimpan NewOrder dari Aplikasi VB
[pic]
[pic]
--jangan lupa tambahkan/membuat store procedur NewOrder
--listing AddOrder
Private Sub bttnNewOrder_Click()
Dim ADOConn As New ADODB.Connection
Dim oParam As ADODB.Parameter
Dim ADOCmd As mand
Dim ADOError As ADODB.Error
Dim DetLine As String
Dim item As String * 6
ADOConn.Open "Provider=SQLOLEDB.1;User ID=sa;password=bambang;Initial Catalog=nwbambang;Data Source=winserver2000"
Set ADOCmd = New mand
ADOCmd.ActiveConnection = ADOConn
' The first parameter will hold the
' stored procedure's return value
Set oParam = ADOCmd.CreateParameter
oParam.Name = "RETURN_VALUE"
oParam.Type = adInteger
oParam.Direction = adParamReturnValue
ADOCmd.Parameters.Append oParam
' CustomerID
Set oParam = ADOCmd.CreateParameter
oParam.Name = "@CustID"
oParam.Type = adChar
oParam.Size = 5
oParam.Direction = adParamInput
oParam.Value = Left(Customers.Text, 5)
ADOCmd.Parameters.Append oParam
' EmployeeID
Set oParam = ADOCmd.CreateParameter
oParam.Name = "@EmpID"
oParam.Type = adInteger
oParam.Direction = adParamInput
oParam.Value = 4
ADOCmd.Parameters.Append oParam
' Order Date
Set oParam = ADOCmd.CreateParameter
oParam.Name = "@OrderDate"
oParam.Type = adDate
oParam.Direction = adParamInput
oParam.Value = Date
ADOCmd.Parameters.Append oParam
' Shipper ID
Set oParam = ADOCmd.CreateParameter
oParam.Name = "ShipperID"
oParam.Type = adInteger
oParam.Direction = adParamInput
oParam.Value = Shippers.ItemData(Shippers.ListIndex)
ADOCmd.Parameters.Append oParam
' Now build string with order details
For i = 0 To 2
If Val(Quantities(i).Text) > 0 Then
LSet item = Products(i).ItemData(Products(i).ListIndex)
DetLine = DetLine & item
LSet item = Quantities(i).Text
DetLine = DetLine & item
LSet item = Discounts(i).Text
DetLine = DetLine & item
End If
Next
If Len(DetLine) = 0 Then
MsgBox "You must specify at least one item!"
Set ADOCmd = Nothing
Set ADOConn = Nothing
Exit Sub
End If
Set oParam = ADOCmd.CreateParameter
oParam.Name = "Details"
oParam.Type = adVarChar
oParam.Size = Len(DetLine)
oParam.Direction = adParamInput
oParam.Value = DetLine
ADOCmd.Parameters.Append oParam
mandText = "NewOrder"
mandType = adCmdStoredProc
On Error GoTo ExecError
ADOCmd.Execute
If ADOCmd.Parameters(0).Value 0 Then
MsgBox "Operation failed" & vbCrLf & _
"The server returned the error # " & ADOCmd.Parameters(0).Value
Else
MsgBox "Order added successfully!"
End If
Set ADOCmd = Nothing
Set ADOConn = Nothing
Exit Sub
ExecError:
msg = "Could not add new customer." & vbCrLf & _
"Error(s) returned by the provider:" & vbCrLf
msg = msg & ADOConn.Errors(0).Description & vbCrLf
MsgBox msg
Set ADOCmd = Nothing
Set ADOConn = Nothing
End Sub
Private Sub Form_Load()
Dim ADOConn As New ADODB.Connection
Dim ADOCmd As New mand
Dim ADORS As New ADODB.Recordset
ADOConn.Open "Provider=SQLOLEDB.1;User ID=sa;password=bambang;Initial Catalog=nwbambang;Data Source=WINSERVER2000"
ADORS.Open "Products", ADOConn
While Not ADORS.EOF
For i = 0 To 2
Products(i).AddItem ADORS.Fields("ProductName")
Products(i).ItemData(Products(i).NewIndex) = ADORS.Fields("ProductID")
Next
ADORS.MoveNext
Wend
Products(0).AddItem "Invalid Product"
Products(0).ItemData(Products(0).NewIndex) = 1000
Products(0).AddItem "Invalid Product"
Products(0).ItemData(Products(0).NewIndex) = 1001
Products(0).AddItem "Invalid Product"
Products(0).ItemData(Products(0).NewIndex) = 1002
Products(0).ListIndex = 0
Products(1).ListIndex = 0
Products(2).ListIndex = 0
ADORS.Close
ADORS.Open "Customers", ADOConn
While Not ADORS.EOF
Customers.AddItem ADORS.Fields("CustomerID") & " " & ADORS.Fields("CompanyName")
ADORS.MoveNext
Wend
Customers.AddItem "AAAAA Invalid Customer"
Customers.AddItem "BBBBB Invalid Customer"
Customers.AddItem "CCCCC Invalid Customer"
Customers.AddItem "DDDDD Invalid Customer"
Customers.AddItem "EEEEE Invalid Customer"
Customers.ListIndex = 0
ADORS.Close
ADORS.Open "Shippers", ADOConn
While Not ADORS.EOF
Shippers.AddItem ADORS.Fields("CompanyName")
Shippers.ItemData(Shippers.NewIndex) = ADORS.Fields("ShipperID")
ADORS.MoveNext
Wend
Shippers.ListIndex = 0
Set ADORS = Nothing
End Sub
[pic]
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- sql server data classification
- sql sensitivity classification
- azure sql data classification
- sql server data classification tool
- azure sql database
- sql connection string sql user
- azure sql vs azure sql database
- azure sql vs sql databases
- azure sql managed instance vs sql db
- sql server sql syntax
- transact sql rename column
- transact sql select top