prosedur dan function

 

nama/nim : mochammad irfan/10410100283

dosen : Tan Amelia

tugas : prosedur dan function

 A.   Parameter  “IN”

  

 
CREATE PROCEDURE sales

@Category varchar (15)

AS SELECT CategoryId, CategoryName, ProductName, ProductSales

FROM [Sales by Category]

WHERE CategoryName like @Category

EXEC sales 'beverages'

B. 

B. Parameter “OUT”

ALTER PROCEDURE Coun

    (@total nvarchar (25) OUTPUT )

       AS

       SELECT CategoryName, ProductName, ProductSales ,count(CategoryID)       

    as SumCon

       FROM [Sales by Category]

       WHERE ProductName like @total

       group by CategoryName, ProductName, ProductSales

 EXEC coun 'Chai'

C.  C. parameter “2 (Dua) OUT”

alter PROC Sales

@total_sales INT OUTPUT, @avg_sales INT OUTPUT

AS

SELECT CategoryName, count(ProductID) as countProducts, AVG(ProductID) as AvgProducts

FROM [Sales by Category] x, Products y

WHERE x.ProductName = y.ProductName

group by CategoryName

DECLARE @total_sales_co int, @avg_sales_co INT

EXEC Sales @total_sales=@total_sales_co OUTPUT,

@avg_sales = @avg_sales_co OUTPUT

D. D.  parameter  “IN dan OUT”

ALTER PROC Coba_product

@sales VARCHAR(30), @pro INT OUTPUT

AS

SELECT [Sales by Category].CategoryName, [Sales by Category].ProductSales, Products.UnitPrice,

count (ProductID) AS SumOfProducts

FROM [Sales by Category], Products

WHERE CategoryName like @sales and UnitsInStock like @pro

GROUP BY [Sales by Category].CategoryName, ProductSales, UnitPrice

EXEC Coba_product 'beverages', '25'

E.     E.   Dengan parameter “INOUT”

create PROC INOUT

@maksimum varchar(15) OUT

as select CategoryName,ProductName ,AVG (CategoryID) as Average

from [Sales by Category]

where ProductName like @maksimum

group by CategoryName, ProductName

EXEC INOUT 'chai'

2. function

a.

ALTER function kd_city

(@kd_city char (20))

returns char

begin

return @kd_city

end

select FirstName, LastName,

dbo.kd_city (city) from Employees

 

3. trigger

CREATE TRIGGER For_Delete2 ON dbo.[Sales by Category]

FOR DELETE

AS

DECLARE

@dlt int

set @dlt = (select CategoryID FROM [Sales by Category])

BEGIN

      if @dlt = 1

            BEGIN

            DELETE FROM [Sales by Category] where CategoryID = @dlt

            END

      ELSE

            BEGIN

            PRINT 'DATA TIDAK ADA'

            END

END