SqlServer存储过程

一、无参数查询
    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游标参数用来将存储过程的局部游标传递回执行调用的批处理、存储过程或触发器

 （暂时不太明白怎么玩，后续待补充）