ALTER PROC PRO_GET_COM_LIST

  @EveryPage      INT,             --每页记录数
  @Columns        VARCHAR(500),    --列名字
  @CurrentPage    INT,             --当前页
  @WhereStr       NVARCHAR(2000),  --筛选条件
  @LowestPriceStr VARCHAR(50),     --最低竞价线
  @Tables         VARCHAR(100),    --表名
  @BetweenTable   VARCHAR(100),    --多表链接条件
  @TotalCount     INT OUTPUT,      --记返回总记录
  @TotalPageCount INT OUTPUT       --返回总页数  
AS

  SET NOCOUNT ON

  DECLARE 
    @Tmp_Top_Str    VARCHAR(100),
    @Tmp_Re_Order   VARCHAR(500),
    @Tmp_OrderStr   VARCHAR(500),
    @Tmp_Sql        NVARCHAR(4000),
    @LowestPrice    NUMERIC(9,1)
    
  SET @LowestPrice = CAST(@LowestPriceStr AS NUMERIC(9,1))

  SET @Tmp_Sql = 'SELECT @TotalCount = COUNT(0) FROM y_user ' + @WhereStr
  EXEC SP_EXECUTESQL @Tmp_Sql,N'@TotalCount INT OUTPUT', @TotalCount OUTPUT

  SET @Tmp_Top_Str = ' TOP ' + LTrim(STR(@EveryPage)) + ' '
  SET @TotalPageCount = CEILING(@TotalCount / @EveryPage) + 1
  SET @Tmp_OrderStr = 'displaychujia DESC, y_vip DESC, Is_License DESC, hits DESC, y_user.[id] DESC'

  SET @Tmp_Re_Order = UPPER(@Tmp_OrderStr)
  SET @Tmp_Re_Order = REPLACE(REPLACE(@Tmp_Re_Order,'ASC','{ASC}'),'DESC','{DESC}')
  SET @Tmp_Re_Order = REPLACE(REPLACE(@Tmp_Re_Order,'{ASC}','DESC'),'{DESC}','ASC')


  IF @CurrentPage = 1 OR @CurrentPage = @TotalPageCount
    BEGIN
      IF @CurrentPage = 1
        SET @Tmp_Sql = N'SELECT '+ @Tmp_Top_Str + @Columns + ' FROM ' + @Tables +' ON '+ @BetweenTable + @WhereStr +' ORDER BY  '+ @Tmp_OrderStr
      IF @CurrentPage = @TotalPageCount AND @TotalPageCount <> 1
        BEGIN
          SET @Tmp_Top_Str = ' TOP ' + LTrim(STR(@TotalCount - ((@TotalPageCount - 1) * @EveryPage))) + ' '
          SET @Tmp_Sql = N'SELECT TOP ' + LTrim(STR(@EveryPage)) + ' * FROM ('
                        + 'SELECT '+ @Tmp_Top_Str + @Columns + ' FROM '+ @Tables + ' ON ' + @BetweenTable + @WhereStr + ' ORDER BY ' + @Tmp_Re_Order
                        + ') AS y_user ORDER BY ' + @Tmp_OrderStr
        END
    END
  ELSE
    BEGIN
      SET @Tmp_Sql = N'SELECT TOP ' + LTrim(STR(@EveryPage)) + ' * FROM ('
                     + 'SELECT TOP ' + LTrim(STR(@EveryPage)) + ' * FROM ('
                     + 'SELECT TOP '+ LTrim(STR(@EveryPage*@CurrentPage)) + ' ' + @Columns + ' FROM '+ @Tables + ' ON ' + @BetweenTable + @WhereStr + ' ORDER BY ' + @Tmp_OrderStr
                     + ') AS y_user ORDER BY ' + @Tmp_Re_Order
                     + ') AS y_user ORDER BY ' + @Tmp_OrderStr
    END
  SET QUOTED_IDENTIFIER OFF
PRINT @Tmp_Sql
  EXEC SP_EXECUTESQL @Tmp_Sql


GO



EXECUTE PRO_GET_COM_LIST 10,
' y_user.[id],username, y_vip,hits,displaychujia, qymc, dz, tp, pro, city, t_domain, Is_License, xjdh, dhfj, S_Areas_Names, S_Types_Names, S_Special_Names, S_Price_Names, Con_Quality_Name, Des_Quality_Name, Com_Age_Name, Reg_Capital_Name, Is_Con_Pass, Is_Des_Pass, Is_Age_Pass, Is_Capital_Pass '
,2,N' WHERE ", " + [zs_user_attr].[S_Types] + "," like "%, 2,%" ',30.0,' zs_user_attr RIGHT JOIN y_user  ',' y_user.[id] = zs_user_attr.UserID','',''

--在查询分析器中执行，也会返回正确的结果集，不过有这么一个错误
服务器: 消息 107，级别 16，状态 3，行 1
列前缀 'zs_user_attr' 与查询中所用的表名或别名不匹配。

--我非常确定以及肯定，zs_user_attr表里面有 S_Types这个字段


--下面是PRINT出来的SQL
SELECT TOP 10 * FROM (
SELECT TOP 10 * FROM (
SELECT TOP 20  y_user.[id],username, y_vip,hits,displaychujia, qymc, dz, tp, pro, city, t_domain, Is_License, xjdh, dhfj, S_Areas_Names, S_Types_Names, S_Special_Names, S_Price_Names, Con_Quality_Name, Des_Quality_Name, Com_Age_Name, Reg_Capital_Name, Is_Con_Pass, Is_Des_Pass, Is_Age_Pass, Is_Capital_Pass  
FROM  zs_user_attr RIGHT JOIN y_user   
ON  y_user.[id] = zs_user_attr.UserID WHERE ", " + [zs_user_attr].[S_Types] + "," like "%, 2,%"  
ORDER BY displaychujia DESC, y_vip DESC, Is_License DESC, hits DESC, y_user.[id] DESC