NIM/NAMA:10410100256/ ARIOCKY AGUSTINUS
DOSEN : TAN AMELIA
1. Stored Procedure
A. Dengan parameter “ IN”
Membuat suatu procedure dengan memasukkan parameter IN
Syntax:
CREATE PROCEDURE SupplierCountry
@Cityname varchar(15)
AS SELECT SupplierID, CompanyName, ContactName, City, Country, Phone
FROM Suppliers
WHERE Country like @Cityname
EXEC SupplierCountry 'Canada'
Hasil:
B. Dengan parameter “OUT”
Membuat procendur out tentang jumlah data yang tersedia:
Syntax:
ALTER PROCEDURE CountOrderByStatus
(@total INT OUTPUT )
AS
SELECT ShipName,CustomerID,UnitPrice,Quantity , count(OrderID)
as SumOfProduct
FROM Invoices
WHERE Quantity like @total
group by ShipName,CustomerID,UnitPrice,Quantity
EXEC CountOrderByStatus '24'
Hasil:
C. Procedure Insert
Membuat suatu procedure dengan menginsertkan data
Syntax:
CREATE PROC insert_employee
@EmployeeID varchar (20),
@FirstName varchar (20),
@LastName varchar (20),
@Title varchar (20)
AS
INSERT INTO employees (EmployeeID,FirstName,LastName,Title)
VALUES (@EmployeeID,@FirstName,@LastName,@Title)
EXEC insert_employee '14','Ariocky','Agustinus','Supervisor'
SELECT * FROM Employees
Hasil:
d.Update Data
membuat suatu procedure dengan mengupdate suatu data :
Syntax:
ALTER PROC update_employee
@EmployeeID varchar (10),
@Title varchar (10)
AS
UPDATE Employees
SET Title=@Title
where EmployeeID=@EmployeeID
EXEC update_employee '14','SALESMAN'
SELECT * FROM Employees
e.DELETE
Membuat suatu procedure dengan menghapus suatu data yang telah diinsertkan.
Syntax:
CREATE PROC delete_employee
@EmployeeID varchar (10)
AS
delete Employees
where EmployeeID=@EmployeeID
EXEC delete_employee '14'
SELECT * FROM Employees
Hasil:
2.FUNCTION
a.IN
Kita akan membuat function dengan menyingkat inisial sebuah kota:
Syntax:
CREATE function kd_city
(@kd_city char (20))
RETURNS char
BEGIN
RETURN @kd_city
End
Hasil tampilannya dengan syntax seperti :
SELECT EmployeeID,LastName,
dbo.kd_city (city) from employees
hasil:
D. Trigger untuk Update
Trigger update untuk menjalankan 2 fungsi sekaligus .salah satunya yaitu dengan update:
CREATE TRIGGER For_Update ON dbo.CUSTOMERS
FOR UPDATE
AS
DECLARE
@ID int, @jumlah smallint
set @ID = (select CustomersID FROM Customers)
set @jumlah = (SELECT UnitsInStock FROM [Customers] by ProductName])
BEGIN
UPDATE Customers SET UnitsInStock = (@jumlah) WHERE ProductID = @ID
END
SELECT * FROM Customers