当前位置: 代码迷 >> Sql Server >> 存储过程传列名提示异常
  详细解决方案

存储过程传列名提示异常

热度:43   发布时间:2016-04-27 12:30:38.0
存储过程传列名提示错误
SQL code
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,[email protected] 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(@[email protected])) + ' ' + @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 OFFPRINT @Tmp_Sql  EXEC SP_EXECUTESQL @Tmp_SqlGOEXECUTE 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出来的SQLSELECT 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) AS y_user ORDER BY DISPLAYCHUJIA ASC, Y_VIP ASC, IS_LICENSE ASC, HITS ASC, Y_USER.[ID] ASC) AS y_user ORDER BY displaychujia DESC, y_vip DESC, Is_License DESC, hits DESC, y_user.[id] DESC--这个SQL运行是没有问题的
  相关解决方案