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 总页码;
