可重复使用的存储过程（增，删，改，查，分页）代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】可重复使用的存储过程（增，删，改，查，分页）
作者:欣玉 / 发布于2016/9/13/ 958
/*
 * 增加 
 */
IF OBJECT_ID('usp_InsertObj','P') IS NOT NULL
 DROP PROC usp_InsertObj
GO
CREATE PROC usp_InsertObj
 @InsertList VARCHAR(2000),         --欲更新字段列表
    @TableSource VARCHAR(100),         --表名或视图表 
 @valueList VARCHAR(2000)           --赋值            
AS
BEGIN 
 BEGIN TRANSACTION 
 DECLARE @errorSum INT    
 SET @errorSum=0
 IF @InsertList IS NOT NULL OR LTRIM(RTRIM(@InsertList))<>''
  BEGIN
   SET @InsertList=' ( '+@InsertList+' )'
  END 
 PRINT @InsertList
  
 SET @valueList=ISNULL(@valueList,'')
 SET @valueList=LTRIM(RTRIM(@valueList))
 IF @valueList<>''
  BEGIN 
   SET @valueList=' VALUES ( '+@valueList+' )'
  END
 PRINT @valueList
 
 DECLARE @SQL VARCHAR(2000)
  SET @SQL='INSERT INTO '+@TableSource+@InsertList+@valueList
 PRINT @SQL
 
 SET NOCOUNT ON
 EXEC(@SQL)
 SET @errorSum=@errorSum+@@ERROR
 SET NOCOUNT OFF
 
    IF @errorSum<>0
  BEGIN 
   PRINT '添加失败'
   ROLLBACK TRANSACTION
  END
 ELSE 
  BEGIN 
   PRINT '添加成功'
   COMMIT TRANSACTION
  END  
 RETURN @@RowCount
END
GO

 

/*
 * 数据删除
 */
IF OBJECT_ID('usp_DeleteObj','P') IS NOT NULL
 DROP PROC usp_DeleteObj
GO
CREATE PROC usp_DeleteObj
    @TableSource VARCHAR(100),         --表名或视图表 
 @DeleteCondition VARCHAR(2000)     --查询条件
AS
BEGIN
 BEGIN TRANSACTION 
 DECLARE @errorSum INT  
 SET @DeleteCondition=ISNULL(@DeleteCondition,'')
 SET @DeleteCondition=LTRIM(RTRIM(@DeleteCondition))
 IF @DeleteCondition<>''
  BEGIN
   SET @DeleteCondition=' WHERE '+@DeleteCondition
  END
 PRINT @DeleteCondition
 
 DECLARE @SQL VARCHAR(2000)
 SET @SQL='DELETE FROM '+@TableSource +@DeleteCondition
 PRINT @SQL
 
 SET NOCOUNT ON
 EXEC(@SQL)
 SET @errorSum=@errorSum+@@ERROR
 SET NOCOUNT OFF
 
 IF @errorSum<>0
  BEGIN
   PRINT '更新失败'
   ROLLBACK TRANSACTION
  END
 ELSE 
  BEGIN
   PRINT '更新成功'
   COMMIT TRANSACTION
  END
 RETURN @@RowCount
END
GO

 

/*
 * 数据更新
 */
IF OBJECT_ID('usp_UpdateObj','P') IS NOT NULL
 DROP PROC usp_UpdateObj
GO
CREATE PROC usp_UpdateObj
 @UpdateList VARCHAR(2000),         --欲更新字段列表
    @TableSource VARCHAR(100),         --表名或视图表 
 @UpdateCondition VARCHAR(2000)     --查询条件
AS
BEGIN 
 BEGIN TRANSACTION 
 DECLARE @errorSum INT  
 SET @UpdateCondition=ISNULL(@UpdateCondition,'')
 SET @UpdateCondition=LTRIM(RTRIM(@UpdateCondition))
 IF @UpdateCondition<>''
  BEGIN
   SET @UpdateCondition=' WHERE '+@UpdateCondition
  END
 PRINT @UpdateCondition
 
 DECLARE @SQL VARCHAR(2000)
 SET @SQL='UPDATE '+@TableSource+' '+@UpdateList+' '+@UpdateCondition
 PRINT @SQL
 
 SET NOCOUNT ON
    EXEC(@SQL)
 SET @errorSum=@errorSum+@@ERROR
 SET NOCOUNT OFF
 
 IF @errorSum<>0
  BEGIN
   PRINT '更新失败'
   ROLLBACK TRANSACTION
  END
 ELSE 
  BEGIN
   PRINT '更新成功'
   COMMIT TRANSACTION
  END
 RETURN @@RowCount
END
GO

 

/*
 * 查询
 * 支持分页
 */
IF OBJECT_ID('usp_GetRecordFromPage','P') IS NOT NULL
 DROP PROC usp_GetRecordFromPage
GO
CREATE PROC usp_GetRecordFromPage
 @SelectList VARCHAR(2000),         --欲选择字段列表
    @TableSource VARCHAR(100),         --表名或视图表 
 @SearchCondition VARCHAR(2000),    --查询条件
 @OrderExpression VARCHAR(1000),    --排序表达式
 @PageIndex INT=1,       --页号,从0开始
    @PageSize INT=10       --页尺寸
AS
BEGIN 
 IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList))=''
  BEGIN 
   SET @SelectList='*'
  END
 PRINT @SelectList
 
 SET @SearchCondition=ISNULL(@SearchCondition,'')
 SET @SearchCondition=LTRIM(RTRIM(@SearchCondition)) 
 IF @SearchCondition <>''
  BEGIN 
   SET @SearchCondition='WHERE '+@SearchCondition
  END 
 PRINT @SearchCondition
 
 SET @OrderExpression=ISNULL(@OrderExpression,'')
 SET @OrderExpression=LTRIM(RTRIM(@OrderExpression))
 IF @OrderExpression <>''
  BEGIN 
   IF UPPER(SUBSTRING(@OrderExpression,1,5))<>'WHERE'
    BEGIN
     SET @OrderExpression='ORDER BY '+@OrderExpression
    END
  END
 PRINT @OrderExpression

 IF @PageIndex IS NULL OR @PageIndex<1
  BEGIN 
   SET @PageIndex=1
  END
 PRINT @PageIndex

 IF @PageSize IS NULL OR @PageSize<1
  BEGIN
   SET @PageSize=10
  END 
 PRINT @PageSize

    DECLARE @SQL VARCHAR(4000)

    SET @SQL='SELECT '+@SelectList+',RowNumber
    FROM 
        (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber
          FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource
    WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR) 
    + ' AND ' + 
    CAST((@PageIndex * @PageSize) AS VARCHAR) 
--    ORDER BY ' + @OrderExpression
    PRINT @SQL

    SET NOCOUNT ON
    EXECUTE(@SQL)
    SET NOCOUNT OFF
 
    RETURN @@RowCount
END
GO

 

/*
 * 查询
 * 普通查询
 */
IF OBJECT_ID('usp_GetObj','P') IS NOT NULL
 DROP PROC usp_GetObj
GO
CREATE PROC usp_GetObj
 @SelectList VARCHAR(2000),         --欲选择字段列表
    @TableSource VARCHAR(100),         --表名或视图表 
 @SearchCondition VARCHAR(2000),    --查询条件
 @OrderExpression VARCHAR(1000)     --排序表达式
AS
BEGIN 
 IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList))=''
  BEGIN 
   SET @SelectList='*'
  END
 PRINT @SelectList
 
 SET @SearchCondition=ISNULL(@SearchCondition,'')
 SET @SearchCondition=LTRIM(RTRIM(@SearchCondition)) 
 IF @SearchCondition <>''
  BEGIN 
   SET @SearchCondition=' WHERE '+@SearchCondition
  END 
 PRINT @SearchCondition
 
 SET @OrderExpression=ISNULL(@OrderExpression,'')
 SET @OrderExpression=LTRIM(RTRIM(@OrderExpression))
 IF @OrderExpression <>''
  BEGIN 
   IF UPPER(SUBSTRING(@OrderExpression,1,5))<>'WHERE'
    BEGIN
     SET @OrderExpression=' ORDER BY '+@OrderExpression
    END
  END
 PRINT @OrderExpression

 DECLARE @SQL VARCHAR(4000)
 SET @SQL='SELECT '+@SelectList+' FROM '+@TableSource+@SearchCondition+@OrderExpression
 PRINT @SQL

 SET NOCOUNT ON
    EXECUTE(@SQL)
    SET NOCOUNT OFF

 RETURN @@RowCount
END
GO


/*
 * 数量查询
 */
IF OBJECT_ID('usp_GetCount','P') IS NOT NULL
 DROP PROC usp_GetCount
GO
CREATE PROC usp_GetCount
 @Count INT OUTPUT,         --数量输出
 @TableName VARCHAR(100),   --表名或者视图名称
 @SearchWhere VARCHAR(1000) --查询调价
AS
 IF @SearchWhere IS NOT NULL OR LTRIM(RTRIM(@SearchWhere))=''
  BEGIN
   SET @SearchWhere = ' WHERE '+@SearchWhere
  END
 
 DECLARE @SQL NVARCHAR(1200)
 SET @SQL=N'SELECT @Count = COUNT(*) FROM '+ @TableName +@SearchWhere
 
 EXEC sp_executesql @SQL,N'@Count INT OUTPUT',@Count OUTPUT
GO
试试其它关键字
同语言下
可能有用的
欣玉贡献的其它代码(17)