SQL 2005常用操作存储过程（原创）

/******************************************************************
 * SQL 2005常用操作存储过程

 * 作者：周超亿（转载请注明）

******************************************************************/

 

------------------------------分页获得列表----------------------------------
if exists(select * from sysobjects where name = 'Proc_News_GetRecordByPage')
drop proc Proc_News_GetRecordByPage
go
create proc Proc_News_GetRecordByPage
@pageSize int,   -------显示的个数-------
@pageIndex int,  -------显示的当前页数-------
@orderType int,  -------排序方式，0-升序，1-降序（显示最新的数据）-------
@orderName nvarchar(200),  -------排序的字段名称-------
@strWhere nvarchar(2000),  -------查询条件-------
@totalCount int output,       -------查询数据总条数，暂时没有使用-------
@viewName nvarchar(200)    -------表名或者视图名称-------
as
begin
    declare @strSql nvarchar(2000),@sql nvarchar(2000),@strOrderType nvarchar(4),
    @strBet nvarchar(2000),@tc int
    begin
        ------------------------------获得数据总条数-----------------------------
        set @sql = 'Select @totalCount = Count(*) From ' + @viewName + ' Where 1 = 1 '+@strWhere
        exec sp_executesql @sql,N'@totalCount int output', @totalCount output
    end
    if @orderType < 1
        begin
            set @strOrderType = 'asc'
            set @strBet = convert(nvarchar(2000),(@pageIndex-1)*@pageSize+1)+
            ' and '+convert(nvarchar(2000),@pageIndex*@pageSize)
        end
    if @orderType >= 1
        begin
            set @strOrderType = 'desc'            
            set @strBet = convert(nvarchar(2000),@totalCount-@pageIndex*@pageSize+1)+
            ' and '+convert(nvarchar(2000),@totalCount-@pageIndex*@pageSize+@pageSize)    
        end
    begin
        ------------------------------分页获得数据-----------------------------
        set @strSql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@orderName+') as OrderRank,* FROM '+@viewName+
        ' Where 1 = 1 '+@strWhere+')'+' as rank WHERE OrderRank BETWEEN '+@strBet+' ORDER BY '+@orderName+' '+@strOrderType
        exec sp_executesql @strSql
    end
end
go

exec Proc_News_GetRecordByPage 2,2,1,'newsID','',0,'NEWS'

 

 

 

------------------------------获得最大值----------------------------------
if exists(select * from sysobjects where name = 'Proc_GetMax')
drop proc Proc_GetMax
go
create proc Proc_GetMax
@tableName nvarchar(50),  ----表名----
@fieldName nvarchar(50)  ----字段名----
as
declare @strSql nvarchar(2000)
begin
    set @strSql = 'select max(' + @fieldName + ') from ' + @tableName
    exec sp_executesql @strSql
end
go

exec Proc_GetMax 'news','newsID'

 

 

 

------------------------------验证数据是否已存在----------------------------------
if exists(select * from sysobjects where name = 'Proc_Exists')
drop proc Proc_Exists
go
create proc Proc_Exists
@tableName nvarchar(50),  ----表名----
@fieldName nvarchar(50),  ----字段名----
@fieldValue nvarchar(200)
as
declare @strSql nvarchar(2000)
begin
    set @strSql = 'select count(1) from ' + 
    @tableName + ' where 1=1 and ' + @fieldName + ' = ''' + @fieldValue+''''
    exec sp_executesql @strSql
end
go

exec Proc_Exists 'news','newsID','15'

 

 

 

/******************************************************************
 * 用途：删除一条记录 
******************************************************************/
if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[Proc_NEWS_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Proc_NEWS_Delete]
GO
CREATE PROCEDURE Proc_NEWS_Delete
@tableName nvarchar(200), ---表名---
@fieldName nvarchar(200), ---字段名---
@items nvarchar(2000)     ---字段值集合以,分开---
--如果字段名列是字符串类型，那么需要使用这种形式''内容''
--例如 exec Proc_Delete 'news','title','''周'',''超'',''亿'''
AS
    declare @strSql nvarchar(2000)
    set @strSql = 'delete from ['+@tableName+'] where '+@fieldName+' in ('+@items+')'
    exec sp_executesql @strSql
GO

exec Proc_NEWS_Delete 'news','title','''周'',''超'''