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)

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