Anasayfa / SQL SERVER / Sql Fonksiyonlar / create sp nedir stored procedure nasıl kullanılır

create sp nedir stored procedure nasıl kullanılır

–stored procedure:
–stored = saklı demektir
–executeplanı saklıyor
–yapısal bir değişiklik yapmadığımız sürece explan aynı kalacaktır
–dışardan parametre alabilir
–istediğimiz kadar programlama tarzı yapabiliriz
–2 tane storedprocedure vardır
–sistem procedureleri(system procedure) ve bizim tanımladıklarımız(user defined)
–sp = normal
–xp = extended

select * from yazarlar
select * from kitaplar
select * from kategoriler

select K.KitapNo,K.Adi as ‘Kitap Adi’,Y.Adi+ ‘ ‘+Y.Soyadi as ‘Ad Soyad’,
Kt.Adi as ‘Kategori’,K.Fiyati,K.Miktar,
case K.Durum when 1 then ‘Var’ else ‘Yok’
end as ‘Durum’
from kitaplar K inner join yazarlar Y
on K.YazarID=Y.YazarID
inner join Kategoriler Kt
on Kt.KategoriID=K.KategoriID
–stored procedureli hali
create procedure KitapListesi
as
select K.KitapNo,K.Adi as ‘Kitap Adi’,Y.Adi+ ‘ ‘+Y.Soyadi as ‘Ad Soyad’,
Kt.Adi as ‘Kategori’,K.Fiyati,K.Miktar,
case K.Durum when 1 then ‘Var’ else ‘Yok’
end as ‘Durum’
from kitaplar K inner join yazarlar Y
on K.YazarID=Y.YazarID
inner join Kategoriler Kt
on Kt.KategoriID=K.KategoriID

–ve tek cümleyle çağırabiliriz

exec kitaplistesi
declare @kitapadi varchar(100)
set @kitapadi = ‘c’
select K.KitapNo,K.Adi as ‘Kitap Adi’,Y.Adi+ ‘ ‘+Y.Soyadi as ‘Ad Soyad’,
Kt.Adi as ‘Kategori’,K.Fiyati,K.Miktar,
case K.Durum when 1 then ‘Var’ else ‘Yok’
end as ‘Durum’
from kitaplar K inner join yazarlar Y
on K.YazarID=Y.YazarID
inner join Kategoriler Kt
on Kt.KategoriID=K.KategoriID
where K.Adi like ‘%’+@kitapadi+’%’

–stored proc hali
create proc KitapArama
@kitapadi varchar(100)
as
select K.KitapNo,K.Adi as ‘Kitap Adi’,Y.Adi+ ‘ ‘+Y.Soyadi as ‘Ad Soyad’,
Kt.Adi as ‘Kategori’,K.Fiyati,K.Miktar,
case K.Durum when 1 then ‘Var’ else ‘Yok’
end as ‘Durum’
from kitaplar K inner join yazarlar Y
on K.YazarID=Y.YazarID
inner join Kategoriler Kt
on Kt.KategoriID=K.KategoriID
where K.Adi like ‘%’+@kitapadi+’%’

–as olanları getircez

exec KitapArama ‘as’

select * from products
–fiyatı 20 ile 50 olanları getir

declare @ilkfiyat money,
@sonfiyat money
set @ilkfiyat =20
set @sonfiyat = 50
select * from Products
where unitprice between @ilkfiyat and @sonfiyat

–proc hali
create proc UrunAra
@ilkfiyat money,
@sonfiyat money
as
select * from Products
where unitprice between @ilkfiyat and @sonfiyat

select * from employees

declare @city varchar(20)
set @city= ‘on’
select city from employees
where city like ‘%on%’

create proc SehirAra
@city varchar(20)
as
select EmployeeID,FirstName,LastName,City from Employees
where city like ‘%’+@city+’%’
exec SehirAra ‘%on%’

–kitap aramada değişiklikler yaptık
alter proc KitapArama
@kitapadi varchar(100),
@ilkfiyat money,
@sonfiyat money
as
select K.KitapNo,K.Adi as ‘Kitap Adi’,Y.Adi+ ‘ ‘+Y.Soyadi as ‘Ad Soyad’,
Kt.Adi as ‘Kategori’,K.Fiyati,K.Miktar,
case K.Durum when 1 then ‘Var’ else ‘Yok’
end as ‘Durum’
from kitaplar K inner join yazarlar Y
on K.YazarID=Y.YazarID
inner join Kategoriler Kt
on Kt.KategoriID=K.KategoriID
where K.Adi like ‘%’+@kitapadi+’%’
or (K.Fiyati between @ilkfiyat and @sonfiyat)
exec kitaparama ‘c’,10,50

select * from [order details]

–productIDlere göre toplam gelirlerini getircez

declare @productID int
set @productID = 51
select sum (Unitprice*Quantity) as ‘Toplam Kazanç’
from [order details] where ProductID = @productID
group by productID

declare @sum money,@productname varchar(50)
select @productname =P.Productname,
@sum = sum(OD.unitprice*OD.Quantity)
from [order details] OD inner join Products P
on OD.ProductID=P.ProductID
group by P.Productname

create proc UrunArama
@productID int = null,
@customerID char (10) = null,
@ilkfiyat money = 0,
@sonfiyat money = null
as
if @sonfiyat is null
set @sonfiyat = (select max(unitprice) from [order details])
declare @sum money,@productname varchar(50)
select O.customerID,P.Productname,
sum(OD.Unitprice*OD.Quantity)
from [order details] OD inner join Products P
on OD.ProductID=P.ProductID
inner join orders O
on OD.OrderID=O.OrderID
where(@productID is null or OD.ProductID = @productID)
and (@customerID is null or O.CustomerID = @customerID)
and (OD.Unitprice between @ilkfiyat and @sonfiyat)
group by P.ProductName,O.CustomerID

select top 2 * from orders
select top 2 * from customers
select top 2 * from employees

–fullname,companyname,contactname,orderdate,region listele

select E.FirstName+ ‘ ‘+E.LastName as ‘Full Name’,C.CompanyName,C.ContactName,
isnull(E.Region,’no region’),convert(varchar(10),O.OrderDate,104)
from employees E inner join Orders O
on E.EmployeeID=O.EmployeeID
inner join customers C
on C.CustomerID=O.CustomerID

create proc deneme
@firstname varchar(50)=null,
@lastname varchar(50)=null ,
@companyname varchar(50)=null ,
@startdate datetime=null ,
@enddate datetime =null,
@region char(10)=null
as

select E.FirstName+ ‘ ‘+E.LastName as ‘Full Name’,C.CompanyName,C.ContactName,
isnull(E.Region,’no region’),convert(varchar(10),O.OrderDate,104)as ‘order Date’
from employees E inner join Orders O
on E.EmployeeID=O.EmployeeID
inner join customers C
on C.CustomerID=O.CustomerID
where (@firstname is null or E.FirstName like ‘%’+@firstname+’%’)
and (@lastname is null or E.Lastname like ‘%’+@lastname+’%’)
and (@companyname is null or C.Companyname like ‘%’+@companyname+’%’)
and (@region is null or E.Region like ‘%’+@region+’%’)
and ((@startdate is null or @enddate is null)
or O.Orderdate between @startdate and @enddate)

Ayrıca Buna Bakabilirsiniz

SQL 2016 Database mail göndermiyor

SQL 2016 Database mail göndermiyor SQL 2016 Database mail göndermiyor problemi ve çözümü için makaleyi …

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir