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=