Stored Procedure Oluşturmak – Bölüm 48

Geliştirici ve yöneticiler tarafından yazılarak kullanıma sunulan sp’lerin çalışma dinamiklerini daha iyi anlamak adına bu bölümde select sorguları kullanılan sp’lerin nasıl oluşturulduğundan bahsedeceğiz.

Değer Döndüren Stored Procedure Oluşturmak

Stored procedure’ler SQL Server’da veri işleme, sistem yapılandırması ve sistem bakımı da dahil olmak üzere birçok görevde kullanılmaktadır. Verilere erişimi standart hale getirmek amacıyla sp’ler oluşturulabilir. Önceki bölümlerde bahsettiğimiz veri manipülasyonu yöntemlerinden herhangi birini kullanarak sp’ler içerisinde SELECT sorgusunda kullanılabilir.

Aşağıdaki örnekte, sipariş verilerini çeken bir stored procedure oluşturduk:

CREATE PROCEDURE Sales.OrderSummaries
AS
SELECT O.orderid, O.custid, O.empid, O.shipperid, CAST(O.orderdate AS date)AS orderdate,
 SUM(OD.qty) AS quantity,
 CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount))
 AS NUMERIC(12, 2)) AS ordervalue
FROM Sales.Orders AS O
 JOIN Sales.OrderDetails AS OD
 ON O.orderid = OD.orderid
GROUP BY O.orderid, O.custid, O.empid, O.shipperid, O.orderdate;
GO 

Oluşturulan sp’nin çalıştırılması için procedure isminden önce EXEC komutu kullanılır:

EXEC [Sales].[OrderSummaries];

Dönen sonuç:

orderid custid empid shipperid orderdate quantity ordervalue
------- ----- ------ --------- ---------- -------- ----------
10248 85 5 3 2006-07-04 27 440.00
10249 79 6 1 2006-07-05 49 1863.40
10250 34 4 2 2006-07-08 60 1552.60

Herhangi bir yolla sp yapısı değiştirilmek istendiğinde (sütunların sırası veya ORDER BY eklenilmesi gibi), ALTER PROCEDURE (ALTER PROC) ifadesi kullanılarak değişiklikler için baştan yeni bir kod girilir. Aşağıda, önceki örnekte oluşturulan sp’nin yapısı değiştirilmiş ve güncellenmiştir:

ALTER PROCEDURE Sales.OrderSummaries
AS
SELECT O.orderid, O.custid, O.empid, O.shipperid, CAST(O.orderdate AS date)AS orderdate,
 SUM(OD.qty) AS quantity,
 CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount))
 AS NUMERIC(12, 2)) AS ordervalue
FROM Sales.Orders AS O
 JOIN Sales.OrderDetails AS OD
 ON O.orderid = OD.orderid
GROUP BY O.orderid, O.custid, O.empid, O.shipperid, O.orderdate
ORDER BY orderid, orderdate; 

Sp’de değişiklik yapmak istendiğinde DROP PROCEDURE kullanarak silip en baştan CREATE PROCEDURE ile oluşturmak yerine, ALTER PROCEDURE ile kolayca güncelleştirilebilir. Bu sayede yerinde değişiklik yapılmış olur ve izinlerin yeniden sağlanması gibi ek bir uğraşı gerektirmez.

Parametreli Stored Procedure Oluşturmak

Sp’lere input parametresi vererek sp içerisinde bu değişkenlerin kullanması sağlanabilir, bu sayede sorguya çalışması sırasında ilave esneklik kazandıracaktır. Input parametreleri sp oluşturulurken CREATE PROCEDURE ifadesinde belirtilir. Parametre tanımında @ öneki kullanılır ve veri türü de belirtilir. Parametrelere NULL dahil varsayılan değerler de atanabilir.

Aşağıda sp oluşturulması sırasında parametre tanımlarken genel olarak kullanılan syntax gösterilmiştir:

CREATE PROCEDURE <schema>.<procedure_name>
(@<parameter_name> AS <data_type>)
AS ... 

Örneğin burada yukarıdaki syntax kullanılarak bir sp oluşturulmuştur. Integer türündeki empid input parametresi tanımlanmıştır ve sp çalıştırıldığında WHERE ifadesinde bu parametre kullanılacaktır:

CREATE PROCEDURE Sales.OrderSummariesByEmployee
(@empid AS int)
AS
SELECT O.orderid, O.custid, O.empid, O.shipperid, CAST(O.orderdate AS date)AS orderdate,
 SUM(OD.qty) AS quantity,
 CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount))
 AS NUMERIC(12, 2)) AS ordervalue
FROM Sales.Orders AS O
 JOIN Sales.OrderDetails AS OD
 ON O.orderid = OD.orderid
WHERE empid = @empid
GROUP BY O.orderid, O.custid, O.empid, O.shipperid, O.orderdate
ORDER BY orderid, orderdate;
GO

Aşağıda input parametresi için 5 değeri atanarak exec komutuyla stored procedure çalıştırılmıştır.

EXEC Sales.OrderSummariesByEmployee @empid = 5; 

Kaynak:
Modify a Stored Procedure