select * from customers
–customerID sini esas alacağız
–o da bize hangi bölümde çalıştığını yani şirket adını göstercek
–output olanlara biz değer atamayız
create proc SirketAdiGetir
@customerID char(10),
@companyName varchar(100) output
as
if exists (select top 1 customerID from customers
where customerID=@customerID)
begin
set @companyName =(select top 1 companyname from customers
where companyname=@companyName)
end
else
set @companyName = ‘Hatalı müşteri kodu…’
declare @sirketAdi varchar(100)
exec SirketAdiGetir ‘ALFKI’,@sirketAdi output
create proc SirketAdiGetir2
@customerID char(10)
as
declare @companyName varchar(100)
if exists (select top 1 customerID from customers
where customerID=@customerID)
begin
set @companyName =(select top 1 companyname from customers
where companyname=@companyName)
end
else
set @companyName = ‘Hatalı müşteri kodu…’
exec SirketAdiGetir2 ‘ALFKI’
–sp yi sp içerisinden çağırcağız
create procedure procTest
@customerID char(10)
as
declare @sirketAdi varchar(100)
exec SirketAdiGetir @customerID,@sirketAdi output
select * from customers where CompanyName like ‘%’+@sirketAdi+’%’
exec procTest ‘ALFKI’
–SirketAdiGetir2 yi tanımaz
create procedure procTest2
@customerID char(10)
as
declare @sirketAdi varchar(100)
set @sirketAdi =(SirketAdiGetir2 @customerID)
select * from customers
where CompanyName like ‘%’+@sirketAdi+’%’
exec procTest ‘ALFKI’
create proc CokluDegerDondurme
@customerID char(10)
as
if exists (select top 1 customerID from customers
where customerID=@customerID)
begin
select top 1 CompanyName,ContactName,Address
from customers
where customerID=@customerID
end
else
select ‘kayıt bulunamadı.’
exec CokluDegerDondurme ‘ALFKI’
–output ile yapılışı
create proc CokluDegerDondurme_output
@customerID char(10),
@companyName varchar(100) output,
@contactName varchar(100) output,
@address varchar (200)output
as
if exists (select top 1 customerID from customers
where customerID=@customerID)
begin
select top 1
@companyName = CompanyName,
@contactName = ContactName,
@address = Address
from customers
where customerID=@customerID
end
else
select ‘kayıt bulunamadı.’
declare @sirketAdi varchar(100),
@kisiAdi varchar(100),@adres varchar(200)
exec CokluDegerDondurme_output ‘ALFKI’,
@sirketAdi output,@kisiAdi output,@adres output
Output kullanımı ve örnekleri
Hiç yorum yok:
Yorum Gönder