Improved SQL Service General-purpose stored procedure paging

The generic stored procedure was last written. There are still a lot of bugs. Is that the condition can not be parametric painting. This bug can be said to be fatal. But I always wanted to be able to solve this thing. In fact I just want to write a small amount of code to do more things. I was wondering if I could pass the collection to the stored procedure, but it seems like this is not working.

On the code.

　　

IF (SELECT COUNT (*) from sysobjects s WHERE s.[type]= ' P ' and s.name= ' sp_page_practical ') >0drop PROC sp_page_practical; GO CREATE PROC sp_page_practical (@tbName varchar, @Condition NVARCHAR (+), @ConditionColumn varchar (100), @ ConditionColumn2 varchar, @ConditionColumn3 varchar (+), @ConditionColumn4 varchar (+), @ConditionColumn5 varchar (+), @SortColumn varchar, @isDesc int, @thisPage int, @PageRowNumber int, @sumPage INT out) as begin TRAN Saction DECLARE @exeSql NVARCHAR (1000); DECLARE @sumRowNumber INT, @descStr NVARCHAR (10); SET @exeSql = ' Select @sumRowNumber =count (*) from ' [email protected]+ ' [email protected]; --Execute dynamic SQL Get value exec sp_executesql @exeSql, N ' @sumRowNumber int out, @ConditionColumn VARCHAR, @ConditionColumn2 varchar (+), @ConditionColumn3 varchar, @ConditionColumn4 varchar, @ConditionColumn5 varchar (100) ', @ Sumrownumber out, @ConditionColumn, @ConditionColumn2, @ConditionColumn3, @ConditionColumn4, @ConditionColumn5;-- Calculates the total number of pages set @sumPage = (@sumRowNumber-1)/@PageRowNumber +1;--calculates the paging line declare @BeginRow int, @EndRow int; SET @BeginRow = (@thisPage-1) * @PageRowNumber +1; SET @[email protected]* @PageRowNumber;--Calculate sort if @isDesc =1beginset @descStr = ' desc '; endelsebeginset@descstr= ' ASC '; Endset @exeSql = ' SELECT * FROM (select A.*,row_number () over (order by ' [email protected]+ ' [email protected]+ ') as RowIndex from ' [email protected]+ ' a ' [email protected]+ ') a where a.rowindex between ' +convert (VARCHAR (1 0), @BeginRow) + ' and ' +convert (VARCHAR), @EndRow) + '; EXEC sp_executesql @exeSql, N ' @ConditionColumn varchar, @ConditionColumn2 varchar, @ConditionColumn3 varchar (+), @ConditionColumn4 varchar (+), @ConditionColumn5 varchar ', @ConditionColumn, @ConditionColumn2, @ ConditionColumn3, @ConditionColumn4, @ConditionColumn5;--the thing commits or rolls back if @ @ERROR >0beginrollback TRANSACTION; Endelsebegincommit TRANSACTION; END; END

I think the maximum of 5 conditional queries on my project is OK. According to your project to determine how many conditions it. As for how the parameters are delivered is simple. We need to play with the idea of wireless.

DECLARE @sumPage INT;
EXEC sp_page_practical ' tb_user_info ', ' where [email protected] and (1=1 or [email protected] or [email protected] or [Emai L protected] or [email protected]) ', 1,1,1,1,1, ' user_info_id ', 1,1,10, @sumPage out

Above is the test program I wrote. If there are 1 conditions, I will declare one of the other I 1=1 all. Does not conflict with other conditions at all.

If the great God has better ways and better ideas can solve the problem. We can explore and study together.

　　

　　

Improved SQL Service General-purpose stored procedure paging