一、无参数查询
1、创建存储过程：
create procedure GetAllClient --存储过程名
as
select * from dbo.FinanceLease_Client --查询FinanceLease_Client所有数据
2、执行：
exec GetAllClient
二、带参数数据插入
1、创建存储过程
create procedure InsertClient
@ClientNumber nvarchar(50),@ClientName nvarchar(50),@Phone nvarchar(50),
@Gender nvarchar(50),@MaritalStatus nvarchar(50),@Age nvarchar(50),
@LicenseType nvarchar(50),@LicenseNumber nvarchar(50),@Birth nvarchar(50),
@Province nvarchar(50),@City nvarchar(50),@Address nvarchar(50),
@UnitProvince nvarchar(50),@UnitCity nvarchar(50),@UnitAddress nvarchar(50),@Employer nvarchar(50),
@PlatformName nvarchar(50),@PlatformType nvarchar(50),@PlatformRegion nvarchar(50)
as
begin
insert into FinanceLease_Client(
ClientNumber,
ClientName,
Phone,
Gender,
MaritalStatus,
Age,
LicenseType,
LicenseNumber,
Birth,
Province,
City,
Address,
UnitProvince,
UnitCity,
UnitAddress,
Employer,
CreateDate,
PlatformName,
PlatformType,
PlatformRegion
)
values
(
@ClientNumber,
@ClientName,
@Phone,
@Gender,
@MaritalStatus,
@Age,
@LicenseType,
@LicenseNumber,
@Birth,
@Province,
@City,
@Address,
@UnitProvince,
@UnitCity,
@UnitAddress,
@Employer,
sysdatetime(),
@PlatformName,
@PlatformType,
@PlatformRegion
)
end
2、执行存储过程
exec InsertClient
@ClientNumber='10000011',
@ClientName='储勇',
@Phone='13872849382',
@Gender='男',
@MaritalStatus='已婚',
@Age='37',
@LicenseType='身份证',
@LicenseNumber='4.203231978e+017',
@Birth='1999-11-11',
@Province='北京',
@City='北京',
@Address='朝阳',
@UnitProvince='十堰',
@UnitCity='十堰市',
@UnitAddress='龙背湾交通复建工程十四项目部',
@Employer='龙背湾交通复建工程十四项目部1',
@PlatformName='汇通信诚',
@PlatformType=1,
@PlatformRegion='成交合同明细1401-1406'
三、判断数据库是否已经存在存储过程，如果存在，则删除
if OBJECT_ID('DeleteClientByID','p') is not null
drop procedure DeleteClientByID
四、根据ID删除记录
1、创建存储过程
create procedure DeleteClientByID
@Clientid int
as
begin
delete from FinanceLease_Client where id=@Clientid
end
2、执行存储过程
exec DeleteClientByID @Clientid=15660
五、带默认值参数的存储过程
1、创建存储过程
create procedureup_insertstuwithdefault
@sid varchar(15), @sname varchar(30), @ssex char(10) = '男',
@sbirth datetime,@sbirthplace varchar(300) = '', @semail varchar(50) = ''
as
begin
insert into students
(stu_id, stu_name, stu_sex, stu_birth, stu_birthplace,stu_email)
values
(@sid, @sname, @ssex, @sbirth, @sbirthplace,@semail)
end
2、执行存储过程
exec up_insertstuwithdefault @sid ='11', @sname = '赵小乐', @sbirth = '1976-07-05'
六、 带输出参数的存储过程
（获取客户表中所有为男性的人数）
1、创建存储过程
create procedure GetAvgAgeBySex
@sex nvarchar(50),@counts int output
as
begin
select COUNT(*) from FinanceLease_Client where Gender=@sex
end
2、执行存储过程
declare @mancounts int
exec GetAvgAgeBySex '男',@mancounts output
print @mancounts
七、带返回值的存储过程
（如果已存在，返回‘要插入的数据已存在’，否则插入数据并返回‘恭喜！数据插入成功！’）
1、创建存储过程
create procedure InsertIntoClientifno
@ClientNumber nvarchar(50),@ClientName nvarchar(50),@Phone nvarchar(50),
@Gender nvarchar(50),@MaritalStatus nvarchar(50),@Age nvarchar(50),
@LicenseType nvarchar(50),@LicenseNumber nvarchar(50),@Birth nvarchar(50),
@Province nvarchar(50),@City nvarchar(50),@Address nvarchar(50),
@UnitProvince nvarchar(50),@UnitCity nvarchar(50),@UnitAddress nvarchar(50),@Employer nvarchar(50),
@PlatformName nvarchar(50),@PlatformType nvarchar(50),@PlatformRegion nvarchar(50)
as
begin
if exists(select * from FinanceLease_Client where ClientNumber=@ClientNumber)
print ('要插入的数据已存在')
else
begin
insert into dbo.FinanceLease_Client(
ClientNumber,
ClientName,
Phone,
Gender,
MaritalStatus,
Age,
LicenseType,
LicenseNumber,
Birth,
Province,
City,
Address,
UnitProvince,
UnitCity,
UnitAddress,
Employer,
CreateDate,
PlatformName,
PlatformType,
PlatformRegion
)
values
(
@ClientNumber,
@ClientName,
@Phone,
@Gender,
@MaritalStatus,
@Age,
@LicenseType,
@LicenseNumber,
@Birth,
@Province,
@City,
@Address,
@UnitProvince,
@UnitCity,
@UnitAddress,
@Employer,
sysdatetime(),
@PlatformName,
@PlatformType,
@PlatformRegion
)
print ('恭喜！数据插入成功！')
end
end
2、执行存储过程
exec InsertIntoClientifno
@ClientNumber='10000011',
@ClientName='储勇',
@Phone='13872849382',
@Gender='男',
@MaritalStatus='已婚',
@Age='37',
@LicenseType='身份证',
@LicenseNumber='4.203231978e+017',
@Birth='1999-11-11',
@Province='北京',
@City='北京',
@Address='朝阳',
@UnitProvince='十堰',
@UnitCity='十堰市',
@UnitAddress='龙背湾交通复建工程十四项目部',
@Employer='龙背湾交通复建工程十四项目部1',
@PlatformName='汇通信诚',
@PlatformType=1,
@PlatformRegion='成交合同明细1401-1406'
八、存储过程之Return
(删除数据，并返回受影响的行数)
1、创建存储过程
create procedure ReturnDeletedCounts
@ClientNumber nvarchar(50)
as
begin
delete from FinanceLease_Client where ClientNumber=@ClientNumber
return @@rowcount
end
2、执行存储过程
declare @rowc int
exec @rowc=ReturnDeletedCounts '10000011'
select @rowc as '删除的行'
九、带变量的存储过程
（查询某学生成绩是否高于平均分）
1、创建存储过程
create procedureup_getavgscorebyname2
@aname varchar(30), @resStr varchar(30) output
as
begin
declare @curAvg decimal(18,2)
declare @totalAvg decimal(18,2)
select @totalAvg = avg(score) from course_score
select curAvg = avg(score) from students"S", course_score "CS"
where "S".stu_id = "CS".stu_idand "S".stu_name= @aname
if @curAvg > @totalAvg
set @resStr = '高于平均分'
else
set @resStr = '低于平均分'
print ('总平均分为' + convert(varchar(18), @totalAvg))
print ('该生平均分' + convert(varchar(18), @curAvg))
print @resStr
2、执行存储过程
declare @resstring varchar(30)
exec up_getavgscorebyname2 '甘明', @resstring output
十、使用output游标参数
--output游标参数用来将存储过程的局部游标传递回执行调用的批处理、存储过程或触发器
（暂时不太明白怎么玩，后续待补充）