龙空技术网

Sql Server 简单存储过程

小磊搬砖 86

前言:

现时看官们对“sql存储过程的使用”都比较重视,你们都想要剖析一些“sql存储过程的使用”的相关知识。那么小编也在网络上网罗了一些对于“sql存储过程的使用””的相关文章,希望各位老铁们能喜欢,大家快快来学习一下吧!

--分页显示存储过程

if(exists(select * from sys.objects where name='pr_ShowPage'))

drop proc pr_ShowPage

go

create proc pr_ShowPage

(

@where nvarchar(500),

@pageIndex int,

@pageSize int,

@totalCount int output,

@pageCount int output

)

as

begin

declare @strcount nvarchar(500)=''

set @strcount='select @totalCount=count(*) from

(select ROW_NUMBER() over(order by UId) RowNumber,* from UserInfo

where 1=1 '+@where+') t'

exec sp_executesql @strcount,N'@totalCount int output',@totalCount output

set @pageCount=CEILING(@totalCount*1.0/@pageSize)

declare @strsql nvarchar(500)=''

declare @startRow int,@endRow int

set @startRow=(@pageIndex-1)*@pageSize+1

set @endRow=@pageIndex*@pageSize

set @strsql='select * from

(select ROW_NUMBER() over(order by UId) RowNumber,* from UserInfo where 1=1 '+@where+') t

where t.RowNumber between '+CAST(@startRow as nvarchar(5))+' and '+CAST(@endRow as nvarchar(5))+''

exec(@strsql)

end

declare @count int,@page int

exec pr_ShowPage '',2,5,@count output,@page output

select @count,@page

--添加存储过程

if (exists(select *from sys.objects where name ='Proc_Add'))

drop proc Proc_Add

go

create proc Proc_Add

(

@ID int,

@name varchar(50),

@UPhosne varchar(20),

@Bis bit,

@Upnum float

)

as

begin

begin tran

begin try

-- 添加语句

insert into UserInfo values (@ID,@name,@UPhosne,@Bis,@Upnum)

commit tran

end try

begin catch

print error_message()

rollback tran

end catch

end

标签: #sql存储过程的使用 #sqlserver存储过程写法