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
USE Northwind
GO
CREATE PROC pahali_kitaplar
AS
SELECT *
FROM products
WHERE unitprice > 30
GO
— çalıştırmak için
select * from products
EXEC pahali_kitaplar
—– bunun içinde declare ile değişken tanımlayıp çalışyıtman gerekiyor….
— stored procedure ye örnekler
select * from products
select * from [order details]
alter proc urunler
@productId int — declare siz hali yazılır
as
select e.firstname + ‘ ‘ + e.Lastname as ‘full name’,p.productname,p.unitprice * quantity
from employees e inner join orders o
on e.employeeID = o.employeeID inner join
[order details] od on
o.orderId = od.orderId inner join products p
on od.productId = p.productId
go
exec urunler
— bu örneği çoğaltalım pls…
create proc urunlerproductId
@productId int — declare siz hali yazılır
as
select e.firstname + ‘ ‘ + e.Lastname as ‘full name’,p.productname,p.unitprice * quantity,od.productId
from employees e inner join orders o
on e.employeeID = o.employeeID inner join
[order details] od on
o.orderId = od.orderId inner join products p
on od.productId = p.productId
where od.productId = @productID
go
exec urunlerproductId 1
create proc urunlerproductId
@productId int — declare siz hali yazılır
as
select e.firstname + ‘ ‘ + e.Lastname as ‘full name’,p.productname,p.unitprice * quantity,od.productId
from employees e inner join orders o
on e.employeeID = o.employeeID inner join
[order details] od on
o.orderId = od.orderId inner join products p
on od.productId = p.productId
where od.productId = @productID
go
exec urunlerproductId 1
create proc urunlerproductId2
@productId int, — declare siz hali yazılır
@orderID int
as
select e.firstname + ‘ ‘ + e.Lastname as ‘full name’,p.productname,p.unitprice * quantity,od.productId
from employees e inner join orders o
on e.employeeID = o.employeeID inner join
[order details] od on
o.orderId = od.orderId inner join products p
on od.productId = p.productId
where od.productId = @productID and
od.orderID = @orderID
go
exec urunlerproductId2 11,10248
— deefault ayar vermek
create proc urunlerproductId4
@productId int=11, — declare siz hali yazılır
@orderID int=10248
as
select e.firstname + ‘ ‘ + e.Lastname as ‘full name’,p.productname,p.unitprice * quantity,od.productId
from employees e inner join orders o
on e.employeeID = o.employeeID inner join
[order details] od on
o.orderId = od.orderId inner join products p
on od.productId = p.productId
where od.productId = @productID and
od.orderID = @orderID
go
exec urunlerproductId4 @orderId=10248
—
create proc urunlerproductId5
@productId int=null, — declare siz hali yazılır
@orderID int=null
as
select e.firstname + ‘ ‘ + e.Lastname as ‘full name’,p.productname,p.unitprice * quantity,od.productId
from employees e inner join orders o
on e.employeeID = o.employeeID inner join
[order details] od on
o.orderId = od.orderId inner join products p
on od.productId = p.productId
where (@productId is null or od.productId = @productID) and
(@orderID is null or od.orderID = @orderID)
go
exec urunlerproductId5 @orderID = 10248
–veya
exec urunlerproductId5 @productID = 11 — gibi
select * from customers
CREATE PROC musteriarama
@musterikodu int,
@company varchar(100),
@contactname varchar(50),
@city varchar(50),
@region varchar(50),
@phone int
AS
SELECT *
FROM customers
where (@company is null or Companyname = @company)and
(@contactname is null or contactname = @contactname)and
(@city is null or city = @city)and
(@region is null or region = @region)and
(@phone is null or phone = @phone)
GO
exec musteriarama @company = a
— çalıştırmak için
— bunu evde çalışş
select * from products
EXEC pahali_kitaplar
—
— ASCII KARATERLER İKİSİDE AYNI ANLAMA GELİYO AŞAĞIYI İNCELE
–SUBSTRING (DEGERI OKUR, NERDEN BASLICAK) ÇALIŞMA SİSTEMİ BÖLE
— SELECT SUBSTRING (AHMET,1,2,3) gibi
select ascii(‘H’)
SELECT CHAR(72)
create function encrypt (@value varchar(20))
returns varchar(200)
as
begin
declare @counter int
declare @encr_value nvarchar(200)
declare @position int
set @counter = len(@value)
set @position = 1
set @encr_value = ”
while @counter > 0
begin
set @encr_value = @encr_value + cast (ascii(substring(@value,@position,1)) as varchar)+char(8)
set @counter = @counter -1
set @position = @position + 1
end
return (@encr_value)
end
— bu bir tek çalışırılacak
select dbo.encrypt(customerID)
from customers
— buda ayrı
select dbo.encrypt(‘hasan’)
bunun — decrype tini yapıcaz
create proc prosedür kullanımı
