MSSQL 分页：
1--select top 3 * from tbl_users where id not in (select top 6 id from tbl_users )
2--SELECT TOP 3 * FROM tbl_users
WHERE ( ID >
( SELECT MAX(id) FROM (SELECT TOP 6 id FROM tbl_users ) as T ) )
通用存储过程：
---------------------------------------------------
备份数据库：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BackupDataBase_Proc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure BackupDataBase_Proc
GO
CREATE PROC BackupDataBase_Proc
@filePath varchar(1000)
AS
DECLARE @GetFilePath varchar(1000)
BEGIN
SET @GetFilePath = @filePath + 'Mall_DB '+
convert(VARCHAR(4),year(getdate())) + '-' +
convert(VARCHAR(2),MONTH(getdate())) + '-' +
convert(VARCHAR(2),DAY(getdate())) + ' ' +
replace(Convert(char(8),Getdate(),14),':','')
BACKUP DATABASE [Mall_DB] TO DISK = @GetFilePath WITH NOFORMAT, NOINIT,
NAME = N'Mall_DB', SKIP, NOREWIND, NOUNLOAD, STATS = 10
END
exec BackupDataBase_Proc 'E:\VS2008_ProJ\svnworks\db\'
---------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,yongqiu_xie>
-- Create date: <Create Date,,2010-6-27>
-- Description: <Description,,通用删除>
-- 规定条件为空情况下 的值为 = 'NULLS'
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Common_Delete_Proc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Common_Delete_Proc]
GO
CREATE PROCEDURE Common_Delete_Proc
-- Add the parameters for the stored procedure here
@tableName varchar(100),
@DeleteCondition varchar(1000),
@returnValue int output
AS
declare @SQLStr varchar(4000)
set @SQLStr='DELETE FROM '
BEGIN
SET NOCOUNT ON;
BEGIN TRAN
if(@DeleteCondition <> 'NULLS')
begin
set @SQLStr=@SQLStr+' '+@tableName+' WHERE '+@DeleteCondition
end
else
begin
set @SQLStr=@SQLStr+' '+@tableName
end
exec(@SQLStr)
IF @@ERROR <> 0
begin
set @returnValue = 0
ROLLBACK TRAN
end
ELSE
begin
set @returnValue = 1
COMMIT TRAN
end
SET NOCOUNT OFF
end
GO
-- =============================================
-- Author: <Author,,yongqiu_xie>
-- Create date: <Create Date,,2010-6-27>
-- Description: <Description,,添加>
-- 规定条件为空情况下 的值为 = 'NULLS'
-- sp_executesql需要 ntext/nchar/nvarchar类型 的参数
-- =============================================
/*
exec sp_executesql N'select * from T_Users where userid=988'
exec sp_executesql N'select * from T_Users where userid = @uid',N'@uid int',@uid=988
exec sp_executesql N'select @counts = select count(*) from T_Users',N'@counts int output',@uid output
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Common_Add_Proc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Common_Add_Proc]
GO
ALTER procedure [dbo].[Common_Add_Proc]
@tableName varchar(100),
@fields varchar(1000),
@fieldValue varchar(2000), /*字段值*/
@returnValue varchar(2000) output /*返回参数*/
AS
declare @SQLStr nvarchar(4000)
BEGIN
SET NOCOUNT ON
BEGIN TRAN
set @SQLStr = 'INSERT INTO '
if(@fields <> 'NULLS') /*@fields = 'NULLS' 说明不用写字段INSERT INTO Users(name,email...)*/
begin
set @SQLStr = @SQLStr+ @tableName +'('+@fields+') values('+@fieldValue+');SELECT @returnValue = SCOPE_IDENTITY()'
end
else
begin
set @SQLStr = @SQLStr+ @tableName +' values('+@fieldValue+');SELECT @returnValue = SCOPE_IDENTITY()'
end
EXEC sp_executesql @SQLStr, N'@returnValue varchar(2000) output', @returnValue output
IF @@ERROR <> 0
begin
set @returnValue = 0
ROLLBACK TRAN
end
ELSE
begin
COMMIT TRAN
end
SET NOCOUNT OFF
END
-- =============================================
-- Author: <Author,,yongqiu_xie>
-- Create date: <Create Date,,2010-6-27>
-- Description: <Description,,修改>
-- 规定条件为空情况下 的值为 = 'NULLS'
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Common_Modify_Proc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Common_Modify_Proc]
GO
CREATE PROC Common_Modify_Proc
@tableName varchar(100), --表名
@fieldsValue varchar(2000), --修改字段值
@modifyCondition varchar(1000), --修改记录条件
@returnValue int output --返回值
AS
declare @SQLStr varchar(4000)
set @SQLStr = 'UPDATE '+@tableName
begin
SET NOCOUNT ON;
BEGIN TRAN
set @SQLStr = @SQLStr +'SET ' +@fieldsValue +' WHERE ' + @modifyCondition
EXEC(@SQLStr)
IF @@ERROR <> 0
BEGIN
set @returnValue = 0
ROLLBACK TRAN
END
ELSE
BEGIN
set @returnValue = 1
COMMIT TRAN
END
SET NOCOUNT OFF
end
-- =============================================
-- Author: <Author,,yongqiu_xie>
-- Create date: <Create Date,,2010-6-27>
-- Description: <Description,,分页查询>
-- 分页查询
-- 规定条件为空情况下 的值为 = 'NULLS'
-- =============================================
/*
SELECT top 10 * FROM topic WHERE id > (SELECT MAX(id) FROM
(SELECT TOP 10 id FROM topic ORDER BY ID) T ) ORDER BY ID
SELECT top 10 * FROM topic WHERE id NOT IN(SELECT TOP ((2 - 1) * 10)
id FROM topic ORDER BY ID) ORDER BY ID
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Common_PageQuery_Proc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Common_PageQuery_Proc]
GO
CREATE PROC Common_PageQuery_Proc
@tableName varchar(100), --表名
@isKey varchar(100), --id
@fieldName varchar(2000), --字段名
@queryCondition varchar(2000) , --查询条件不含where关键字
@orderByParam varchar(50) , --按照哪个字段排序
@descOrAsc varchar(20), --降序 or 升序
@pageSizes int , --页显示多少条
@currPage int , --当前页
@totalPage int output, --总页数
@totalCount int output --总记录
AS
declare @pageSQL nvarchar(2000) ,@countSQL nvarchar(2000)
BEGIN
SET NOCOUNT ON
--查询总记录
SET @countSQL = 'SELECT @totalCount = COUNT(*) from '+@tableName
if(@queryCondition <> 'NULLS')
SET @countSQL = @countSQL + ' WHERE '+@queryCondition
exec sp_executesql @countSQL,N'@totalCount int output' , @totalCount output
--总页数
if( @totalCount % @pageSizes <> 0)
SET @totalPage = (@totalCount / @pageSizes) + 1
else
SET @totalPage = @totalCount / @pageSizes
SET @pageSQL = 'SELECT TOP '+ CAST(@pageSizes AS varchar)
if(@fieldName <> 'NULLS')
SET @pageSQL = @pageSQL + @fieldName
ELSE
SET @pageSQL = @pageSQL + ' * '
SET @pageSQL = @pageSQL +' FROM ' +@tableName
SET @pageSQL = @pageSQL + ' WHERE '+@isKey +' NOT IN '
SET @pageSQL = @pageSQL + '(SELECT TOP '+CAST((@currPage - 1)*@pageSizes AS varchar) + ' ' + @isKey
SET @pageSQL = @pageSQL + ' FROM ' + @tableName + ' ORDER BY ' + @isKey
if(@descOrAsc <> 'NULLS')
SET @pageSQL = @pageSQL + @descOrAsc + ')'
else
SET @pageSQL = @pageSQL + ')'
if(@queryCondition <> 'NULLS')
SET @pageSQL = @pageSQL + @queryCondition
SET @pageSQL = @pageSQL + ' ORDER BY '
if(@orderByParam <> 'NULLS')
SET @pageSQL = @pageSQL + @orderByParam
ELSE
SET @pageSQL = @pageSQL + @isKey
if(@descOrAsc <> 'NULLS')
SET @pageSQL = @pageSQL + @descOrAsc
--print 'sql = ' + @pageSQL
EXEC(@pageSQL)
--EXEC sp_executesql @pageSQL
SET NOCOUNT OFF
END
--exec Common_PageQuery_Proc 'Table_A','id','name','NULLS','NULLS','NULLS',5,1,0,0
-- =============================================
-- Author: <Author,,yongqiu_xie>
-- Create date: <Create Date,,2010-6-27>
-- Description: <Description,,普通列表查询>
-- 列表查询 查询几条数据 top
-- 规定条件为空情况下 的值为 = 'NULLS'
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Common_List_Proc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Common_List_Proc]
GO
CREATE PROC Common_List_Proc
@tableName varchar(100),
@fields varchar(2000),
@topCount int ,
@selectCondition varchar(2000)
AS
declare @SQLStr Nvarchar(4000)
BEGIN
SET @SQLStr = 'SELECT TOP '
IF(@fields <> 'NULLS')
SET @SQLStr = @SQLStr + '' + CAST(@topCount AS nvarchar) + @fields
ELSE
SET @SQLStr = @SQLStr + '' + CAST(@topCount AS nvarchar) + ' * '
SET @SQLStr = @SQLStr + +' FROM ' + @tableName
IF(@selectCondition <> 'NULLS')
SET @SQLStr = @SQLStr + 'WHERE' + @selectCondition
EXEC sp_executesql @SQLStr
END
--EXEC Common_List_Proc 'Table_A','name', 2 ,'NULLS'
-- =============================================
-- Author: <Author,,yongqiu_xie>
-- Create date: <Create Date,,2010-6-27>
-- Description: <Description,,查询记录数>
-- 查询记录数，最大值
-- 规定条件为空情况下 的值为 = 'NULLS'
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Common_Count_Proc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Common_Count_Proc]
GO
CREATE PROC Common_Count_Proc
@tableName varchar(100),
@outCount int output,
@selectCondition varchar(2000)
AS
declare @SQLStr Nvarchar(4000)
BEGIN
SET NOCOUNT ON
SET @SQLStr = 'SELECT @outCount= COUNT(*) FROM ' + @tableName
IF(@selectCondition <> 'NULLS')
SET @SQLStr = @SQLStr +' WHERE '+ @selectCondition
EXEC sp_executesql @SQLStr, N'@outCount int output', @outCount output
SET NOCOUNT OFF
END
##############Spring+hibernate 调用 存储过程###########
Session ses=this.getHibernateTemplate().getSessionFactory().openSession();
try{
Connection conn = ses.connection();
conn.setAutoCommit(false);
String proc="{call leatherdb.dbo.p_delallusertabledata()}";
CallableStatement st = conn.prepareCall(proc);
st.executeUpdate();
conn.commit();
}catch(Exception e){
e.printStackTrace();
}
分享到：
发表评论
相关推荐
通用存储过程(通用的分页存储过程) 在实际项目开发中，存储过程是非常常见的，因此掌握存储过程的用法是开发人员必备的实战法宝
mysql 通用存储过程,,下载直接导入mysql工具中即可使用，包含所有通用查询功能,下载资源即可使用
Mysql分页通用存储过程
通用存储过程+增删改查,裏面有很詳細的說明及註釋。
不错的经典的分页、排序SQL 通用存储过程，可以大大节省Web数据库中大批量记录展现的性能问题。
mysql通用存储过程sql脚本，支持多表，子查询，高级查询 分页
ASP.NET基于通用存储过程的分页，简单实用的分页。
sql列转行以及行转列的通用存储过程！mssqlserver版本！
sqlserver通用存储过程,全面好用，你可下载看看代码哦，保证最全面。
oracle数据库的查询分页加条件和排序的通用型存储过程，通过将表名以参数的形式传入到存储过程中做到多表通用，也可以是多表关联的sql语句 将其看作一个表也能调用该存储过程，分页只需要传与页数，和每页显示的行数...
MySQL分页查询通用存储过程的代码总结
一个通用存储过程分页（特别是对大数据很好用），里面包含liq to sql 和sap.net 调用他的实例方法
通用分页存储过程 适用于分页 的通用存储过程
自己写的，不怎么好。 呵呵，贴出来大家参考参考！
一个基于C#+ASP.NET实现的通用存储过程自动生成的管理程序代码，可以自定义存储过程相关输入参数，进行过程的动态维护。
sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程 sql Server 通用分页存储过程
Oracle通用数据库存储过程代码--高效分页存储过程，只需简单的修改即可使用，方便易懂！
3、资源分为两类：真实应用场景的存储过程案例61个（真实业务案例，供学习参考）、通用存储过程案例57个（涉及序列、表及列操作、主键唯一索引约束、事务、内存、权限、导出文件、视图、迭代、备份、参数校验等，可...
原项目中使用Oracle存储过程进行分页,前端借助AspNetPager控件进行分页控制，使用简单方便，并可容易地实现绑定控件（如GridView）的自动编号列功能。现将数据库改用SqlServer2000，经测试，能很好地实现分页功能，...
通用的增删改查 存储过程 对基本的数据超作 通俗 容懂