24 Aralık 2015 Perşembe

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

Hiç yorum yok:

Yorum Gönder