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


Te mirat    Shpjegim
Programim ModularE shkruajme njehere nje  stored procedure dhe e perdorim sa here na duhet ne nje aplikacion
PerformanceStored procedures ofrojne ekzekutim me te shpejte te kodit dhe reduktojne trafikun e rrjetit
SiguriaPerdoruesit  aksesojne objektet e DB nepermjet nje menyre me te kontrolluar

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