SQL Server存储过程实例

1、不带参数的存储过程

create procedure pro_noparm
as
begin
          print('Hello World!!!!');
end

2、带参数的存储过程

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[pro_add](@str decimal(18,2),@num decimal(18,2))
as
begin
    print(@str + @num);
end

运行：exec pro_add 1265.25 , 69854.26
或    exec pro_add @str = 16854.23 , @num = 585.25

3、参数带默认值的存储过程

create procedure [dbo].[pro_withparmvalue] 
@str varchar(14) = 'yuangsSyx' ,
@num decimal(18,2) = 9601.01
as
begin
    declare @strline varchar(50);
    set @strline = CONCAT(CONVERT(varchar(15),@num),@str);
    print(@strline);
end

运行： exec pro_withparmvalue
或     exec pro_withparmvalue '大家好，我是渣渣辉' , 666.66
或     exec pro_withparmvalue @str = '大家好，我是渣渣辉' , @num = 666.66

4、创建带输出参数的存储过程

create procedure pro_withoutput
@shebeiSn varchar(14),
@nums int output
as
begin
  select * from T_ShebeiData_TH where shebeiId like @shebeiSn;
  set @nums = (select count(*) from T_ShebeiData_TH where shebeiId like @shebeiSn);
end

运行：
declare @count int = -1;
exec pro_withoutput @shebeiSn='21%',@nums=@count output;
select @count as 总记录数

5、存储过程实现分页

create procedure pro_pagedata
@pagesize int = 20,
@pageindex int = 1,
@totalrecords int output,
@totalpages int output
as
begin
    select t.*
    from(select *,xuhao=ROW_NUMBER() over (order by rq desc) from T_ShebeiData_TH) as t
    where t.xuhao between (@pageindex-1)*@pagesize + 1 and @pageindex*@pagesize;
    set @totalrecords=(select count(*) from T_ShebeiData_TH);
    set @totalpages = CEILING(@totalrecords * 1.0 /@pagesize);
end

运行：
declare @records int,@pages int;
exec pro_pagedata @pagesize=100,@pageindex=16, @totalrecords=@records output,@totalpages=@pages output;
select @records as 总记录数,@pages as 总页码;