Chapter 10 - Procedures

Stored Procedures

Stored procedures jane nje pjese e rendesishme e SQL Server. Ne to inkapsulohet nje pjese e rendeishme e llogjikes se biznesit me te dhenat.

Nje stored procedure eshte nje grup komandash SQL i parakompiluar , dhe qe ruhet ne database (nen nyjen "Stored Procedures" ). Programuesit apo administratoret e database mund ti therrasin ne ekzekutim procedurat nga SQL Server Management Studio or from within an application as required.

Te mirat e Stored Procedurave

Si te krijojme nje Stored Procedure

Komanda SQL per krijimin e nje stoered procedure eshte

CREATE PROCEDURE StoredProcedureName AS ...

Shembulli me poshte krijon nje procedure: "MyStoredProcedure":

CREATE PROCEDURE MyStoredProcedure AS SET ROWCOUNT 10 SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice FROM Products ORDER BY Products.UnitPrice DESC

Pasi kemi krujar proceduren ajo gjendet nen nujen Programability - Stored Procedures ne Object Explorer.

Si te modifikojme nje Stored Procedure

Mund ta fshijme dhe rikrijojme ose mund te perdorim komanden ALTER.

ALTER PROCEDURE MyStoredProcedure AS ...

Si te ekzekutojme nje Stored Procedure

Nje stored procedure mund te therritet ne executim nepermjet komandes EXECUTE ose EXEC. Psh:

EXEC MyStoredProcedure

Nese emri i procedures ka hapesira boshe midis atehee emri i saj duhet vendosur brenda thonejzave dyshe:

EXEC "My Stored Procedure"

Nese procedura merr argumenta ata vendosen pas emrit te procedures:

EXEC MyStoredProcedure @ParameterName="MyParameter"

Per shembull:

EXEC SalesByCategory @CategoryName ="Beverages"

Te punojme me procedurat nepermjet menuve grafike

Per te punuar me nje procedure ndiqni hapat e meposhtem:

    1. Shkoni tek stored procedure ne bazen Tuaj te te dhenave nen Object Explorer ne SSMS

    2. Klikoni me te djathetn mbi proceduren e duhur dhe zgjidhni "Execute Stored Procedure...":

3. Do te hapet nje dritare dialogu. Futni parametrat qe pret procedura:

4. Klikoni "OK

5. SQL Server do te gjeneroje kodin SQL dhe do te ekzekutoje stored procedure:

Parametrat

Nje parameter eshte nje vlere qe perdor procedura per te kryer funksionet e saj. Kur shkruani nje procedure, mund te percaktoni cilet parametra duhen vendosur nga perdoruesi. Per shembull, nese shkruani nje procedure per te perzgjedhur adresen e nje personi , procedura duhet te dije adresen e cilit individ duhet te ktheje. Ne kete rast , perdoruesu mund ti jape procedures si parameter nje UserId per ti treguar procedures adresen e cilit person deshiron.

Shembuj

shembull 1

-- krijojme njE procedure qe kthen emrin e nje punonjesi

CREATE PROCEDURE EMRI

(

@kodi INT,

@emri varchar(100) OUTPUT

)

AS

BEGIN

DECLARE @e as varchar(100)

SELECT @e=Firstname+' '+Lastname from Employees

where EmployeeID=@kodi

-- si e kthejme emrin e punonjesit

SELECT @emri=@e

END

shembull 2

DECLARE @doc xml

SET @doc = '<?xml version="1.0" ?>

<Order EmployeeId="2" CustomerId="ALFKI" >

<OrderDetails>

<Product ProdId="1" Quantity="6" Price="100"/>

<Product ProdId="2" Quantity="4" Price="245"/>

<Product ProdId="3" Quantity="4" Price="100"/>

</OrderDetails>

</Order>'

exec dbo.shtoporosi @doc

CREATE PROCEDURE shtoporosi

(@docxml as xml)

AS

BEGIN

BEGIN TRANSACTION neworder

Declare @oid as int

Declare @a as int

exec sp_xml_preparedocument @a OUTPUT,@docxml

INSERT INTO Orders(EmployeeId,CustomerId)

select EmployeeId,CustomerID from

OPENXML(@a,'Order',1)

WITH

(

EmployeeId INT '@EmployeeId',

CustomerId VARCHAR(10) '@CustomerId'

)

select @oid=@@IDENTITY

insert into [Order Details](OrderId,ProductId,UnitPrice,Quantity )

select @oid,ProductId,Price,Quantity from

OPENXML(@a,'Order/OrderDetails/Product',1)

WITH

(

ProductId INT '@ProdId',

Quantity float '@Quantity',

Price money '@Price'

)

If @@ERROR<>0 ROLLBACK TRANSACTION neworder

ELSE COMMIT TRANSACTION neworder

END

Funksion qe kthe nje Shopping Cart

create function neworder

(

@kodi as int

)

returns XML

as

BEGIN

Declare @res as XML

SET @res=

(

SELECT OrderID,EmployeeID,CustomerID,OrderDate,

(

select ProductID,UnitPrice,Quantity

from [Order Details]

where [Order Details].OrderID=Orders.OrderID

for XML RAW('Detail'),ROOT('Details'),TYPE

)

FROM Orders

where OrderID=@kodi

for xml raw('OrderInfo'),ROOT('NewOrder')

)

return @res

END

Procedura qe ben import nje shopping Cart ne XML

neper tabelat Order dhe Order Details.

create procedure ImportOrder

(

@neworder as xml

)

as

begin

begin try

begin transaction

Declare @i as int

Declare @dochandle as INT

exec sp_xml_preparedocument @dochandle OUTPUT,@neworder

insert into Orders(EmployeeID,CustomerID,OrderDate)

SELECT *

from Openxml(@dochandle,'NewOrder/OrderInfo',1)

with

(

punonjesi INT '@EmployeeID',

klienti char(5) '@CustomerID',

data datetime '@OrderDate'

)

set @i=@@IDENTITY

insert into [Order Details](OrderID,ProductID,UnitPrice,Quantity)

select @i,produkti,cmimi,sasia

from

Openxml(@dochandle,'NewOrder/OrderInfo/Details/Detail',1)

with

(

produkti INT '@ProductID',

cmimi money '@UnitPrice',

sasia float '@Quantity'

)

Commit transaction

return 0

end try

begin catch

rollback transaction

return -1

end catch

end

Si ta perdorim proceduren

Declare @tmp as xml

set @tmp=dbo.NewOrder(10252)

exec ImportOrder @tmp