SQL Common efficient paging code (stored procedure is with SQL injection) (1/2)

SQL Common efficient paging code (stored procedure with SQL injection)

You can see that the above stored procedure is the final concatenation of a number of steps into a SQL string, and then execute this string through exec to get pagination results.

We assume that we want to do a query, through the user name username fuzzy query users, in order to describe the convenience, easy to understand we only take the first page of the case, take out the first page of the stored procedure in the spelling serial as follows:

For the sake of illustration, we can assume that @pagesize is @strgetfields ' * ', @tblname is UserAccount, @strorder is ' ORDER BY id desc ' So the above line can be written as follows:

Set @strsql = ' Select top ~ ' [UserAccount] where ' + @strwhere + ' ORDER BY id DESC '

We can assume that the user entered the fuzzy user name is: Jim ' s dog
We use SqlParameter to pass parameters to the paging stored procedure the @strwhere value is: ' username like '%jim ' dog% ' (note that the single quotation marks in the strings behind like have all been converted into two single quotes), We are substituting this value in the @strsql assignment statement above, as follows:

Set @strsql = ' Select top ~ ' [UserAccount] where username like '%jim ' ' dog% ' ORDER by id DESC '

Let's write the partial execution of the declaration variable to test in Query Analyzer, the code is as follows:

DECLARE @strsql varchar (8000)
DECLARE @strwhere varchar (1000)
Set @strwhere = ' username like '%jim ' dog% '
Set @strsql = ' Select top ~ ' [UserAccount] where ' + @strwhere + ' ORDER BY id DESC '
Print @strsql
EXEC (@strsql)

Home 1 2 last page