Create PROCEDURE prctestbypage (@tablename varchar (), @selectfilter varchar (100), @orderbyfilter varchar, @selectpage int, @pageSize int As begin--The stored procedure begins declare @pkname varchar (100)--Gets the primary key name of the table select @pkname =column_name from information_s Chema. Key_column_usage WHERE [email protected]declare @num intset @num = (@selectpage-1) * @pageSizedeclare @strsqltwo VARCHAR (4000) Set @strsqltwo = ' Select Top ' +str (@num) + ' [email protected]+ ' from ' [Email protected]declare @ strSQL varchar (4000)--sql EXECUTE statement Set @strSql = ' Select Top ' +str (@pageSize) + ' * from ' [Email protected]if (Len (@s Electfilter) >0)--Determine if there is a where condition BEGIN set @[email protected]+ ' where ' [email protected] Set @strSql = @ strsql+ ' where ' [email protected]+ ' and ' [email protected]+ ' Not In (' [email protected]+ ') ' END else SET @strSql = @strSql + ' WHERE ' [EMAIl protected]+ ' Not in (' [email protected]+ ') ' if (Len (@orderbyfilter) >0)--Determine if there is a sort condition of begin set @[email protected]+ ' ORDER BY ' [email protected] SET @strSql = @strSql + ' ORDER BY ' [email protected] END EXE C (@strSql) End--The stored procedure ends GO exec prctestbypage ' print_order ', ', ', 2,6
SQL Universal Paging Custom table condition stored procedure