分页
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Proc_Util_Page]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Proc_Util_Page
GO
/*
分页
调用方法:EXEC Proc_Util_Page 'country,city', 'table', ' where 1=1', ' order by mid asc', 'mid', 1, 1, 0, ''
输入:
1.字段(不可为空)
2.表名(不可为空)
3.条件(可以为空,需要where)
4.排序(可以为空,需要order by,需要asc和desc字符)
5.主键(可以为空)
6.当前页数
7.每页记录数
8.输出总记录条数(若<1则执行count)
9.输出sql语句
返回:记录集
*/
CREATE PROCEDURE dbo.Proc_Util_Page
(
@sField nvarchar(1000),
@sTable nvarchar(1000),
@sWhere nvarchar(1000),
@sOrderby nvarchar(1000),
@sPkey nvarchar(50),
@iPageIndex int,
@iPageSize int,
@iRecordCount int OUTPUT,
@sOutsql nvarchar(4000) OUTPUT
)
--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @iRC int, @sSQL nvarchar(4000), @sW nvarchar(1000), @sOB nvarchar(1000), @sT nvarchar(100)
SELECT @iRC = @iRecordCount, @sSQL = '', @sW = ' WHERE 1=1 ', @sOB = ''
--判断条件
IF RTRIM(@sWhere) != '' AND @sWhere IS NOT NULL
BEGIN
SET @sW=' ' + @sWhere + ' '
END
--判断总记录数
IF @iRC<1
BEGIN
SET @sSQL='SELECT @iRC=Count(*) FROM ' + @sTable + @sW
EXEC sp_executesql @sSQL,N'@iRC int OUT',@iRC OUT
END
--判断页数是否超出范围
SELECT @iPageIndex=(CASE WHEN @iRC<(@iPageIndex-1)*@iPageSize THEN CEILING(@iRC/@iPageSize) WHEN @iPageIndex<1 THEN 1 ELSE @iPageIndex END)
--判断排序
IF RTRIM(@sOrderby) != '' AND @sOrderby IS NOT NULL
BEGIN
SELECT @sOB=' ' + @sOrderby + ' '
END
--如果是第一页
IF @iPageIndex=1
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+@sOB
GOTO step4
END
--看有否主键
IF RTRIM(@sPkey) = '' OR @sPkey IS NULL
GOTO step1
ELSE
--看是否按主键排序
BEGIN
DECLARE @sOB1 nvarchar(1000), @sPkey1 nvarchar(50)
SELECT @sOB1 = UPPER(@sOrderby), @sPkey1 = UPPER(@sPkey)
IF CHARINDEX(@sPkey1 + ' ASC', @sOB1)>0
BEGIN
SET @sT='>(SELECT MAX('
GOTO step2
END
IF CHARINDEX(@sPkey1 + ' DESC', @sOB1)>0
BEGIN
SET @sT='<(SELECT MIN('
GOTO step2
END
GOTO step3
END
--如果无主键
step1:
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW + ' AND EXISTS (SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+@sOB+')'+@sOB+')'
GOTO step4
END
--纯按主键排序
step2:
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+' AND '+@sPkey+@sT+@sPkey+') FROM (SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW+@sOB+') AS tbTemp)'+@sOB
GOTO step4
END
--不纯按主键排序
step3:
BEGIN
SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sW + ' AND ' + @sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW + ' AND ' + @sPkey+' NOT IN(SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW+@sOB+')'+@sOB+')'+@sOB
GOTO step4
END
--输出最终执行的分页sql语句并执行
step4:
SELECT @sOutsql = @sSQL, @iRecordCount = @iRC
--print(@sSQL)
EXEC(@sSQL)
SET NOCOUNT OFF
END
GO
*************************************************************************
取总数
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Proc_Record_Count]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Proc_Record_Count
GO
/*
调用方法:EXEC Proc_Record_Count '表名', '1=1', ''
输入:
1.表名
2.条件(不包含WHERE)
3.输出
*/
CREATE PROCEDURE dbo.Proc_Record_Count
(
@insTable varchar(50),
@insCondition varchar(1000),
@count int output
)
--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
IF(@insCondition <> '')
SET @insCondition = ' WHERE ' + @insCondition
DECLARE @SqlStr As nvarchar(2000)
SET @SqlStr = 'SELECT @count=COUNT(*) FROM ' + @insTable + @insCondition
EXEC sp_executesql @SqlStr, N'@count int output', @count output
SET NOCOUNT OFF
END
GO
*********************************************************************
添加
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Proc_Record_Add]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Proc_Record_Add
GO
/*
调用方法:EXEC Proc_Record_Add '表名', 'id,name', '3,2', ''
输入:
1.表名
2.表字段
3.添加字段
4.输出
*/
CREATE PROCEDURE dbo.Proc_Record_Add
(
@insTable varchar(50),
@insField varchar(1000),
@insStr varchar(2000),
@returnflag int output
)
--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
BEGIN TRAN
DECLARE @SQL As nvarchar(4000)
SET @SQL = 'INSERT INTO ' + @insTable + ' (' + @insField + ') VALUES(' + @insStr + ');SELECT @returnflag = SCOPE_IDENTITY()'
EXEC sp_executesql @SQL, N'@returnflag int output', @returnflag output
IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
SET NOCOUNT OFF
END
GO
********************************************************************************
取记录集
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Proc_Record_View]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Proc_Record_View
GO
/*
调用方法:EXEC Proc_Record_View 'id,name', '表名', '1=1'
输入:
1.表字段
2.表名
3.条件(不包含WHERE)
*/
CREATE PROCEDURE dbo.Proc_Record_View
(
@insField varchar(1000),
@insTable varchar(50),
@insCondition varchar(1000)
)
--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
IF(@insCondition <> '')
SET @insCondition = ' WHERE ' + @insCondition
DECLARE @SQL As varchar(4000)
SET @SQL = 'SELECT ' + @insField + ' FROM ' + @insTable + @insCondition
EXEC(@SQL)
SET NOCOUNT OFF
END
GO
********************************************************************
删除
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Proc_Record_Del]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Proc_Record_Del
GO
/*
调用方法:EXEC Proc_Record_Del '表名', '1=1'
输入:
1.表名
2.条件(不包含WHERE)
*/
CREATE PROCEDURE dbo.Proc_Record_Del
(
@insTable varchar(50),
@insCondition varchar(1000)
)
--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
BEGIN TRAN
IF(@insCondition <> '')
SET @insCondition = ' WHERE ' + @insCondition
DECLARE @SQL As varchar(2000)
SET @SQL = 'DELETE FROM ' + @insTable + @insCondition
EXEC(@SQL)
IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
SET NOCOUNT OFF
END
GO
*******************************************************************************
修改
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Proc_Record_Edit]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Proc_Record_Edit
GO
/*
调用方法:EXEC Proc_Record_Edit '表名', 'id=2,name=2', '1=1'
输入:
1.表名
2.修改字段
3.条件(不包含WHERE)
*/
CREATE PROCEDURE dbo.Proc_Record_Edit
(
@insTable varchar(100),
@insStr varchar(2000),
@insCondition varchar(1000)
)
--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
BEGIN TRAN
IF(@insCondition <> '')
SET @insCondition = ' WHERE ' + @insCondition
DECLARE @SQL As varchar(4000)
SET @SQL = 'UPDATE ' + @insTable + ' SET ' + @insStr + @insCondition
EXEC(@SQL)
IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
SET NOCOUNT OFF
END
GO