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

代码语言
代码分类
【MSSQL】可重复使用的存储过程（增，删，改，查，分页）
作者:dezai / 发布于2014/4/11/ 993
可重复使用的存储过程（增，删，改，查，分页）
/*
* 增加
*/
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
试试其它关键字
　可重复使用　
同语言下
可能有用的
dezai贡献的其它代码(1065)