存储过程------数据库（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
