存储过程------数据库（SQL）

格式：
存储过程：
create proc 过程名
as
语句
运用：
exec过程名

一：

create proc selectproduct
@price money
as
select productname,unitprice
from Products
where unitprice>@price

exec selectproduct 10

二：

create procedure selectproduct
as
 select productname,unitprice
 from products
 where unitprice>50
 
exec selectproduct

三：

create procedure selectproduct
@inputprice money
as
 select productname,unitprice
 from products
 where unitprice>@inputprice
 
exec selectproduct 10
 
select employeeid
from employees
where lastname='fuller'

四：

create proc selectempid
(@ln varchar(10),@empid int output)
as
 select @empid=employeeid
from employees
where lastname=@ln
 
declare @empid int
exec selectempid 'fuller',@empid output
select @empid

五：

create proc selecttname
(@tid nchar(10),@tname nchar(10) output)
as
 select @tname=tname
 from teacher
 where tid=@tid
 
declare @tname nchar(10)
exec selecttname '004',@tname output
select @tname
 
select productname,unitprice
from products
where productname='tofu'

六：

create proc selectprice
(@pname nvarchar(40),@uprice money output)
as
select @uprice=unitprice
from products
where productname=@pname
 
declare @uprice money
exec selectprice 'Queso Manchego La Pastora',@uprice output
print @uprice

七：

create proc selectnamesex
as
 select sname,ssex
 from student
execute selectnamesex
 
declare @maxprice money,@minprice money
set @maxprice=50
set @minprice=20
select productname,unitprice
from products
where unitprice >=@minprice and unitprice<=@maxprice

八：

create proc selectnameprice
(@minprice money,@maxprice money)
as
 select productname,unitprice
from products
where unitprice >=@minprice and unitprice<=@maxprice
 
exec selectnameprice 10,50

九：

create proc selectname
(@begindate datetime,@enddate datetime)
as
 select lastname,firstname,hiredate
from employees
where hiredate>=@begindate and hiredate<=@enddate
 
exec selectname '1-1-1993','12-31-1994'

十：

create proc [dbo].[selecttname]
(@tid nchar(10),@tname nchar(10) output)
as
 select @tname=tname
 from teacher
 where tid=@tid
declare @tname1 nvarchar(20)
exec [selecttname] '004',@tname1 output
set @tname1=@tname1+'大坏蛋'
select @tname1

十一：

create proc selectcount
(@cateid int,@pcount int output)
as
select @pcount=count(*)
from products
where categoryid=@cateid
 
declare @count int,@cateid int
set @cateid=8
exec selectcount @cateid,@count output
print '第'+convert(varchar(5),@cateid)+
    '类有'+convert(varchar(5),@count)+'种商品'

十二：

create proc selectcount
(@sex nchar(10),@person int output)
as
select @person=count(*)
from student
where ssex=@sex
 
declare @sex nchar(1),@rs int
set @sex='女'
exec selectcount @sex,@rs output
print '学校有'+@sex+'生'+convert(varchar(5),@rs)+'人'

十三：

alter proc selectcount
(@nameid char(11),@ncount int output)
as
select @ncount=count(*)
from LendBook
where Reader_ID=@nameid
 
declare @ncount int
exec selectcount '20081504114',@ncount output
select @ncount
 
select *
from sc
where sid='001'

十四：

alter procedure selectscore
 @st_id nchar(10),@c_id nchar(10) ,@score int output
as
select @score=score
from sc
where sid=@st_id and cid=@c_id
 
declare @s int,@st nchar(10),@course nchar(10)
set @st='004'
set @course='004'
exec selectscore @st,@course,@s output
print '第'+@st+'号同学'+'第'+@course+
     '号课程成绩'+convert(varchar(10),@S)

十五：

create proc selectbirthday
@ln nvarchar(20),@fn nvarchar(10),@birth datetime output
as
 select @birth=birthdate
 from employees
 where lastname=@ln and firstname=@fn
 
declare @birth datetime
exec selectbirthday 'Leverling','Janet',@birth output
select @birth

十六：

create proc selectstname
@tname nchar(10)
as
select s.sname
from student s inner join sc on s.sid=sc.sid
               inner join course c on sc.cid=c.cid
               inner join teacher t on c.tid=t.tid
where t.tname=@tname
 
exec selectstname '张江'

十七：

create proc deletescore
@st_id nchar(10),@c_id nchar(10)
as
delete from sc
where sid=@st_id and cid=@c_id
 
exec deletescore '004','005'

十八：

create proc insertscore
@st_id nchar(10),@c_id nchar(10),@score int
as
 insert into sc(sid,cid,score)
   values(@st_id,@c_id,@score)
 
exec insertscore '004','004',100
 
update sc
 set score=99
where sid='004' and cid='004'

十九：

create proc updatescore
@st_id nchar(10),@c_id nchar(10),@newscore int
as
 update sc
 set score=@newscore
 where sid=@st_id and cid=@c_id
 print '记录已更新！'
 
exec updatescore '001','001',100