将SQL语句定义成变量,构造出完整的SQL语句
然后
Exec (@SQL)
然后
Exec (@SQL)
具体实现
ALTER PROCEDURE sp_CommonPage
(@sql nvarchar(4000), @BeginIndex int, @EndIndex int)
AS
BEGIN
DECLARE @ResultSql nvarchar(4000);
DECLARE @OrderStr nvarchar(400);
DECLARE @SelectStr nvarchar(3000);
DECLARE @OtherStr nvarchar(1000);
DECLARE @ColumnsStr nvarchar(2000);
DECLARE @OrderIndex int;
DECLARE @FromIndex int;
SET @OrderIndex = Charindex('Order',@sql,0);
SET @FromIndex= Charindex('From',@sql,0);
SET @SelectStr = Left(@sql,@FromIndex-1);
SET @ColumnsStr = Substring(@Sql,7,@FromIndex-7);
SET @OtherStr = Substring(@sql, @FromIndex,@OrderIndex-@FromIndex);
SET @OrderStr = Right(@sql,len(@sql)-@OrderIndex+1);
SET @ResultSql = @SelectStr + ',row_number() Over('+@OrderStr+') RowNumber '
+@OtherStr;
Set @ResultSql = 'Select '+@ColumnsStr+' from('+@ResultSql+')Temp where RowNumber between '
+Str(@BeginIndex) + ' AND ' + Str(@EndIndex)
EXEC(@ResultSql)
END
(@sql nvarchar(4000), @BeginIndex int, @EndIndex int)
AS
BEGIN
DECLARE @ResultSql nvarchar(4000);
DECLARE @OrderStr nvarchar(400);
DECLARE @SelectStr nvarchar(3000);
DECLARE @OtherStr nvarchar(1000);
DECLARE @ColumnsStr nvarchar(2000);
DECLARE @OrderIndex int;
DECLARE @FromIndex int;
SET @OrderIndex = Charindex('Order',@sql,0);
SET @FromIndex= Charindex('From',@sql,0);
SET @SelectStr = Left(@sql,@FromIndex-1);
SET @ColumnsStr = Substring(@Sql,7,@FromIndex-7);
SET @OtherStr = Substring(@sql, @FromIndex,@OrderIndex-@FromIndex);
SET @OrderStr = Right(@sql,len(@sql)-@OrderIndex+1);
SET @ResultSql = @SelectStr + ',row_number() Over('+@OrderStr+') RowNumber '
+@OtherStr;
Set @ResultSql = 'Select '+@ColumnsStr+' from('+@ResultSql+')Temp where RowNumber between '
+Str(@BeginIndex) + ' AND ' + Str(@EndIndex)
EXEC(@ResultSql)
END