Experience in using dynamic SQL statements

In our projects, we often need to use the surface sharding function. The methods I used previously seem so clumsy. At that time, we did this, every time we need to paging the data, we need to paging the table. Don't laugh. It's really stupid. Haha, even though there was an idea at the time that we wanted to pass in a table for operations, the compilation would fail because table variables are operated after FROM, it cannot be our custom variables, so we didn't investigate them deeply at the time. Now we are going to BS for the sake of not studying them in depth.

Dynamic SQL requires the following content:

1. @ SQL concatenated SQL statements can be any SQL statements you need, such: SET @ SQL = 'select * FROM table where id = @ id' note that @ SQL here must be of the NTEXT, NVARCHAR, and NCHAR types only, if it is another type, it is clear that there is no problem elsewhere, but an error will be reported "the process type must be 'ntext/nchar/nvarchar 'parameter. At the same time, if you need to input the table name here, you should write: SET @ SQL = 'select * from' + @ table + 'where ID = @ id ', an error is reported because the input value is of the text type.

2. @ parameters: SET @ parameters = '@ id int'. The parameter type must be NTEXT, NVARCHAR, or NCHAR.
3. Call: sp_executesql param1 (, param2) Where param1 is generally used as @ SQL, and the following parameters are the parameters in @ SQL, however, it is important to note that the parameter must correspond to the parameter:

Copy codeThe Code is as follows:
DECLARE @ InputId INT;
SET @ InputId = 1;
Param2 is: @ id = @ InputId;


The following is a simple generic page, which can be modified as needed:

Copy codeThe Code is as follows:
Alter procedure sp_pager
(
@ TableName nvarchar (50), -- table name
@ ReturnFields nvarchar (200) = '*', -- the column to be returned
@ PageSize int = 50, -- number of records per page
@ PageIndex int = 1 -- current page number
)
AS
DECLARE @ SQL NVARCHAR (1000)
DECLARE @ paramters NVARCHAR (200)
BEGIN
SET NOCOUNT ON
SET @ SQL = 'select' + @ ReturnFields + 'from' + @ TableName + 'where ID> (SELECT TOP 1 ID FROM (SELECT TOP '+ CAST (@ PageSize * @ PageIndex as varchar) + 'id from' + @ TableName + 'order by id) as a order by id desc )'
PRINT @ SQL
EXECUTE sp_executesql @ SQL, @ paramters, @ columns = @ ReturnFields
END