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

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

一：

create proc selectproduct@price moneyasselect productname,unitpricefrom Productswhere unitprice>@priceexec selectproduct 10

二：

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

三：

create procedure selectproduct@inputprice moneyas select productname,unitprice from products where unitprice>@inputprice exec selectproduct 10 select employeeidfrom employeeswhere lastname='fuller'

四：

create proc selectempid(@ln varchar(10),@empid int output)as select @empid=employeeidfrom employeeswhere lastname=@ln declare @empid intexec selectempid 'fuller',@empid outputselect @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 outputselect @tname select productname,unitpricefrom productswhere productname='tofu'

六：

create proc selectprice(@pname nvarchar(40),@uprice money output)asselect @uprice=unitpricefrom productswhere productname=@pname declare @uprice moneyexec selectprice 'Queso Manchego La Pastora',@uprice outputprint @uprice

七：

create proc selectnamesexas select sname,ssex from studentexecute selectnamesex declare @maxprice money,@minprice moneyset @maxprice=50set @minprice=20select productname,unitpricefrom productswhere unitprice >=@minprice and unitprice<=@maxprice

八：

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

九：

create proc selectname(@begindate datetime,@enddate datetime)as select lastname,firstname,hiredatefrom employeeswhere 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=@tiddeclare @tname1 nvarchar(20)exec [selecttname] '004',@tname1 outputset @tname1=@tname1+'大坏蛋'select @tname1

十一：

create proc selectcount(@cateid int,@pcount int output)asselect @pcount=count(*)from productswhere categoryid=@cateid declare @count int,@cateid intset @cateid=8exec selectcount @cateid,@count outputprint '第'+convert(varchar(5),@cateid)+    '类有'+convert(varchar(5),@count)+'种商品'

十二：

create proc selectcount(@sex nchar(10),@person int output)asselect @person=count(*)from studentwhere ssex=@sex declare @sex nchar(1),@rs intset @sex='女'exec selectcount @sex,@rs outputprint '学校有'+@sex+'生'+convert(varchar(5),@rs)+'人'

十三：

alter proc selectcount(@nameid char(11),@ncount int output)asselect @ncount=count(*)from LendBookwhere Reader_ID=@nameid declare @ncount intexec selectcount '20081504114',@ncount outputselect @ncount select *from scwhere sid='001'

十四：

alter procedure selectscore @st_id nchar(10),@c_id nchar(10) ,@score int outputasselect @score=scorefrom scwhere 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 outputprint '第'+@st+'号同学'+'第'+@course+     '号课程成绩'+convert(varchar(10),@S)

十五：

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

十六：

create proc selectstname@tname nchar(10)asselect s.snamefrom 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.tidwhere t.tname=@tname exec selectstname '张江'

十七：

create proc deletescore@st_id nchar(10),@c_id nchar(10)asdelete from scwhere sid=@st_id and cid=@c_id exec deletescore '004','005'

十八：

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

十九：

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