1、增删改—通用存储过程
--增删改 存储过程
create proc Infos_InsertUpdateDelete
(
@Id int,
@Name varchar(50),
@DataTable_Action_ varchar(10) = '' --操作方法：Insert:增加 Update:修改 Delete:删除
)
AS
declare @ReturnValue varchar(18) -- 返回操作结果
set @ReturnValue = -1
--开启事务
begin tran
--错误捕捉机制
begin try
--新增
if(@DataTable_Action_='Insert')
begin
insert into Infos(Name) values(@Name)
select
@ReturnValue = SCOPE_IDENTITY()
end
--删除
if(@DataTable_Action_='Delete')
begin
delete Infos where Id=@Id
set @ReturnValue = @@REMSERVER
end
commit tran
end try
begin catch
rollback tran
end catch
select
@ReturnValue
exec Infos_InsertUpdateDelete @Id,@AId,@DId,@Ceng,@HomeNum,@FSize,@JSize,@HId,@JieGou,@Address,@Path,@XingZhi,@States,@DataTable_Action_
2、查询分页—通用存储过程
Create PROC [dbo].[ProcPageList] (
@pageSize INT = 20, --每页记录数
@pageNo INT =1, --当前页
@tableName VARCHAR(50), --表名
@whereString VARCHAR(800) = ' 1=1 ',--WHERE条件
@orderString VARCHAR(200), --排序条件(倒序需要带desc)
@recordTotal INT OUTPUT --输出记录总数
)
AS
BEGIN
DECLARE @tempSql NVARCHAR(4000)
--输出参数@recordTotal为总记录数
SET @tempSql = N'select @recordTotal = count(*) from ' + @tableName + ' where '+ @whereString
EXEC sp_executesql @tempSql,N'@recordTotal INT OUTPUT',@recordTotal OUTPUT
--主查询返回结果集
SET @tempSql = N'select * from (select *,Row_number() over(order by '+@orderString+') as rows from '+@tableName+' WHERE '+@whereString+') as main_temp where rows BETWEEN '+ convert(varchar(100),(@pageNo - 1) * @pageSize+ 1) + ' and ' + convert(varchar(100),@pageNo * @pageSize)
EXEC(@tempSql)
END
3、比较完整的查询分页存储过程
create PROCEDURE [dbo].[commonPagination]
@columns varchar(500), --要显示的列名，用逗号隔开
@tableName varchar(100), --要查询的表名
@orderColumnName varchar(100), --排序的列名
@order varchar(50), --排序的方式，升序为asc,降序为 desc
@where varchar(100), --where 条件，如果不带查询条件，请用 1=1
@pageIndex int, --当前页索引
@pageSize int, --页大小(每页显示的记录条数)
@pageCount int out --总页数，输出参数
as
begin
declare @sqlRecordCount nvarchar(1000) --得到总记录条数的语句
declare @sqlSelect nvarchar(1000) --查询语句
set @sqlRecordCount=N'select @recordCount=count(*) from ' +@tableName + ' where '+ @where
declare @recordCount int --保存总记录条数的变量
exec sp_executesql @sqlRecordCount,N'@recordCount int output',@recordCount output
--动态 sql 传参
if( @recordCount % @pageSize = 0) --如果总记录条数可以被页大小整除
set @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小
else --如果总记录条数不能被页大小整除
set @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1
set @sqlSelect =
N'select '+@columns+' from (
select row_number() over (order by '
+@orderColumnName+' '+@order
+') as tempid,* from '
+@tableName+' where '+ @where
+') as tempTableName where tempid between '
+str((@pageIndex - 1)*@pageSize + 1 )
+' and '+str( @pageIndex * @pageSize)
exec (@sqlSelect) --执行动态Sql
end