Experience with dynamic SQL statements

We often need to use the faceted function in our project, and I used to use the method now seems to be so clumsy, at that time is done, every time to do data paging on the table to do when the page, we do not laugh, before really stupid. Oh, although at that time also have an idea is to be able to pass in a table into the operation, but in that case, compiling is not going to work, because from the back of the operation is a table variable, but not our custom variable, so there was no deep investigation, now for not in-depth study and BS.

Dynamic SQL needs to prepare the following:

1, @SQL after the concatenation of the SQL statement, you can be any need of SQL statements such as: SET @SQL = ' SELECT * from table WHERE id= @id ' Note that the @sql must and can only be ntext, NVARCHAR, nchar type, If other types of words have no problem elsewhere, they will report "the process requires the type of ' Ntext/nchar/nvarchar ' parameter" this error. Also, if you need to pass in the table name here: SET @SQL = ' SELECT * from ' + @table + ' WHERE id= @id ' because the value passed in above is a literal type and therefore an error.

2, @parameters the concatenation of the parameters of the SQL statement, according to the above words here should be: SET @parameters = ' @id INT ' and the type of this parameter must also be ntext, NVARCHAR, nchar type
3, Call: sp_executesql param1 (, param2) of which param1 generally we as a @sql, the following parameters are our parameters in the @sql, but here to note that the time must be the reference to the corresponding:

Copy CodeThe code is as follows:
DECLARE @InputId INT;
SET @InputId = 1;
Param2 is: @id = @InputId;


The following is a simple general page that is written and needs to be modified by itself:

Copy CodeThe code is as follows:
ALTER PROCEDURE Sp_pager
(
@TableName nvarchar (50),--table name
@ReturnFields nvarchar (200) = ' * ',--columns 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 * @Pag Eindex as VARCHAR) + ' ID from ' + @TableName + ' ORDER by ID ' as an ORDER by ID DESC) '
PRINT @SQL
EXECUTE sp_executesql @SQL, @paramters, @columns = @ReturnFields
End