通用存储过程

分页

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