一个动态SQL问题
declare @pageSize int
declare @fields varchar(100)
declare @tbName varchar(30)
declare @where varchar(100)
declare @pK varchar(30)
declare @orderBy varchar(1000)
declare @cPage int
declare @sql nvarchar(3000)
set @pageSize = 2
set @fields = '[UserID],[UserName] '
set @tbName = '[vsUser] '
set @where = '1=1 '
set @pK = '[UserID] '
set @orderBy = '[UserID] DESC '
set @cPage = 2

set @sql = 'SELECT TOP ' + cast(@pageSize as varchar(10))+ ' ' + @fields + ' FROM ' +
@tbName + ' WHERE ' + @where + ' AND ' + @pK + ' NOT IN(SELECT TOP ' +
cast((@cPage-1)*@pageSize as varchar(10))+ ' ' + @pK + ' FROM ' + @tbName + ' WHERE ' + @where +
' ORDER BY ' + @orderBy + ') ORDER BY ' + @orderBy -----> ①

EXEC(@sql)----> ②

这样能正确执行

但如果换成将①②合并,写成Exec( 'SELECT TOP ' + cast(@pageSize as varchar(10))+ ' ' + @fields + ' FROM ' +
@tbName + ' WHERE ' + @where + ' AND ' + @pK + ' NOT IN(SELECT TOP ' +
cast((@cPage-1)*@pageSize as varchar(10))+ ' ' + @pK + ' FROM ' + @tbName + ' WHERE ' + @where +
' ORDER BY ' + @orderBy + ') ORDER BY ' + @orderBy)

就提示: 'cast ' 附近有语法错误。

请教大虾,这是为什么?

[解决办法]
不能在exec 里写~~cast~~
declare @sql varchar(5000)
set @sql= 'SELECT TOP ' + cast(@pageSize as varchar(10))+ ' ' + @fields + ' FROM ' +
@tbName + ' WHERE ' + @where + ' AND ' + @pK + ' NOT IN(SELECT TOP ' +
cast((@cPage-1)*@pageSize as varchar(10))+ ' ' + @pK + ' FROM ' + @tbName + ' WHERE ' + @where +
' ORDER BY ' + @orderBy + ') ORDER BY ' + @orderBy
Exec(@sql)