存储过程的模板(2000),包括分页和事务

CREATE   PROCEDURE RT_SearchWorkShow

(

       @Name varchar(50),

       @TypeID int,

       @UserID int,

       @Status int,

       @InsertDateBegin varchar(50),

       @InsertDateEnd varchar(50),

       @PageSize int,

       @PageNo int

)

AS

 

DECLARE @PageLowerBound int

DECLARE @PageUpperBound int

declare @strSQL varchar(1000)

declare @StrWhere varchar(1000)

 

SET @PageLowerBound = @PageSize * @PageNo

SET @PageUpperBound = @PageLowerBound + @PageSize + 1

--begin transaction

--SET @strSQL = 'select *  from cngda_workshow where 1=1 '

SET @strSQL = 'insert into #WorkShow(WorkID) select ID from cngda_WorkShow where 1=1 '

PRINT @strSQL

create table #WorkShow

(

       IndexID int IDENTITY (1, 1) NOT NULL,

       WorkID int

)

 

SET @StrWhere = ' '

--Name

if @Name <> ''

Begin

set @StrWhere = @StrWhere +  ' and Name like ''%' + @Name +'%'''

set @StrWhere = @StrWhere + ' and Detail like ''%' + @Name +'%'''

End

 

--TypeID大于0，表示按照TypeID搜索

if @TypeID > 1

Begin

set @StrWhere = @StrWhere + ' and TypeID=' + rtrim(convert(char,@TypeID,10))

End

 

--UserID大于0，表示按照UserID搜索

if @UserID > 0

Begin

set @StrWhere = @StrWhere + ' and UserID=' + rtrim(convert(char,@UserID,10))

End

 

if @Status <> -1

BEGIN

SET @StrWhere = @StrWhere + ' and IsCheck=' + rtrim(convert(char,@Status,10))

END

 

--InsertDateBegin

if @InsertDateBegin is not null  and  @InsertDateBegin <> ''

Begin

set @StrWhere = @StrWhere + ' and InsertDate>=''' + @InsertDateBegin + ''''

End

 

--InsertDateEnd

if @InsertDateEnd is not null  and  @InsertDateEnd <> ''

Begin

set @StrWhere = @StrWhere + ' and InsertDate<''' + @InsertDateEnd + ''''

End

 

 

set @StrWhere = @StrWhere + ' order by SortNo desc ,ID desc'

 

set @strSQL   = @strSQL + @StrWhere

execute(@strSQL)

Print @strSQL

select cngda_workshow.*, forums_Users.UserName as UserName ,cngda_workshowtype.Name as TypeName, TotalCount=(select count(*) from #WorkShow) from #WorkShow ,cngda_WorkShow left outer join cngda_workshowtype on cngda_workshow.TypeID=cngda_workshowtype.ID left outer join forums_Users on cngda_workshow.UserID=forums_Users.UserID where cngda_WorkShow.ID = #WorkShow.WorkID and IndexID > @PageLowerBound and IndexID < @PageUpperBound order by IndexID

--commit transaction

RETURN

GO