create proc prosedür kullanımı

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ı