通用存储过程

[size=large]

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();
}


[/size]