none
求教高手关于表变量问题 RRS feed

  • 问题

  • 以下这段代码是正常的

    ALTER PROCEDURE [dbo].[wwforpoorderitemnumbersum_copy]   
     ( @filterdateb AS date,
      @filterdatee AS date ,
      @filterstring AS nvarchar 
    )
    AS
    BEGIN
    DECLARE @tb156 TABLE
                (
                  authorized_order_number VARCHAR(50) ,
                 is_whole int,
    processing_order_item_number int,
    processing_order_number varchar(50),
    item_width numeric(18,6),
    item_height numeric(18,6),
    [item_quantity rack] int,
    unit_price decimal(18,6),
    totalprice decimal(18,6),
    rack_id varchar(200)
                )
    INSERT  @tb156
                    ( authorized_order_number ,
                 is_whole ,
    processing_order_item_number ,
    processing_order_number ,
    item_width ,
    item_height ,
    [item_quantity rack] ,
    unit_price ,
    totalprice ,
    rack_id 
                    )
                    select c.* from (
    select dbo.wwforpoorderitemnumbersum3a.* from
    dbo.wwforpoorderitemnumbersum3a left join processing_orders
    on dbo.wwforpoorderitemnumbersum3a.processing_order_number=processing_orders.processing_order_number
    where dbo.processing_orders.input_date>=@filterdateb and dbo.processing_orders.input_date<=@filterdatee
    ) as c



     if (DATALENGTH(ltrim(rtrim(@filterstring)))=0)
    BEGIN
    select * from @tb156
    end

    end



    • 已编辑 hzpemu 2012年3月29日 4:14
    2012年3月29日 4:09

答案

  • 使用table變數的欄位時不須指定table變數的名稱,嘗試把@tb156拿掉呢?直接使用欄位名稱看看。

    SELECT     
    @tb156.processing_order_number,     /*本行错误error137 ,必须声明标量变量 @tb156*/
    @tb156.processing_order_item_number,  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(@tb156.[item_quantity rack]) AS [本条目委外数量],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(@tb156.totalprice) AS [本条目委外金额],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from @tb156 as t where processing_order_item_number = @tb156.processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,''),  /*本行错误error137 ,必须声明标量变量 @tb156*/
    rack_id= STUFF((select distinct ';'+ rack_id from @tb156 as t where processing_order_item_number = @tb156.processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,'') /*本行错误error137 ,必须声明标量变量 @tb156*/
    FROM
    @tb156
    GROUP BY
    @tb156.processing_order_item_number, /*本行错误error137 ,必须声明标量变量 @tb156*/
    @tb156.processing_order_number  /*本行错误error137 ,必须声明标量变量 @tb156*/

    改成

    SELECT     
    processing_order_number,     /*本行错误error137 ,必须声明标量变量 @tb156*/
    processing_order_item_number,  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum([item_quantity rack]) AS [本条目委外数量],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(totalprice) AS [本条目委外金额],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from @tb156 as t where processing_order_item_number = processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,''),  /*本行错误error137 ,必须声明标量变量 @tb156*/
    rack_id= STUFF((select distinct ';'+ rack_id from @tb156 as t where processing_order_item_number = processing_order_item_number and processing_order_number = processing_order_number for XML path('')),1,1,'') /*本行错误error137 ,必须声明标量变量 @tb156*/
    FROM
    @tb156
    GROUP BY
    processing_order_item_number, /*本行错误error137 ,必须声明标量变量 @tb156*/
    processing_order_number  /*本行错误error137 ,必须声明标量变量 @tb156*/


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/

    • 已标记为答案 hzpemu 2012年3月30日 0:52
    2012年3月29日 4:36
  • 给表变量一个别名,类似这样

    SELECT    
    tb156.processing_order_number,     /*本行错误error137 ,必须声明标量变量 @tb156*/
    ……
    FROM
    @tb156 AS tb156

    联机丛书里面有说明:

    http://msdn.microsoft.com/en-us/library/ms175010.aspx

    Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

    SELECT EmployeeID, DepartmentID 
    FROM @MyTableVar m
    JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
       m.DepartmentID = Employee.DepartmentID);
    

    想不想时已是想,不如不想都不想。

    • 已标记为答案 hzpemu 2012年3月30日 0:51
    2012年3月29日 5:33
    版主
  • select distinct ';'+ authorized_order_number from @tb156 as t 这里不要把@tb156改掉。

    你没看懂联机丛书里面那段英文?意思是”table 变量“


    想不想时已是想,不如不想都不想。

    • 已标记为答案 hzpemu 2012年3月30日 0:51
    2012年3月29日 15:25
    版主

全部回复

  • 我把上面那一段标黑的改以如下就报错,请教高手有办法解决吗?谢谢!

    ALTER PROCEDURE [dbo].[wwforpoorderitemnumbersum_copy]   

     ( @filterdateb AS date,
      @filterdatee AS date ,
      @filterstring AS nvarchar 
    )
    AS
    BEGIN
    DECLARE @tb156 TABLE
                (
                  authorized_order_number VARCHAR(50) ,
                 is_whole int,
    processing_order_item_number int,
    processing_order_number varchar(50),
    item_width numeric(18,6),
    item_height numeric(18,6),
    [item_quantity rack] int,
    unit_price decimal(18,6),
    totalprice decimal(18,6),
    rack_id varchar(200)
                )
    INSERT  @tb156
                    ( authorized_order_number ,
                 is_whole ,
    processing_order_item_number ,
    processing_order_number ,
    item_width ,
    item_height ,
    [item_quantity rack] ,
    unit_price ,
    totalprice ,
    rack_id 
                    )
                    select c.* from (
    select dbo.wwforpoorderitemnumbersum3a.* from
    dbo.wwforpoorderitemnumbersum3a left join processing_orders
    on dbo.wwforpoorderitemnumbersum3a.processing_order_number=processing_orders.processing_order_number
    where dbo.processing_orders.input_date>=@filterdateb and dbo.processing_orders.input_date<=@filterdatee
    ) as c

     if (DATALENGTH(ltrim(rtrim(@filterstring)))=0)
    BEGIN

    select * from (
    SELECT
    a.processing_order_number AS [销售计划单],
    a.processing_order_item_number AS [销售计划单条目],
    a.[本条目委外数量],
    dbo.processing_product.item_quantity AS [本条目计划单数量],
    a.[本条目委外金额],
    dbo.processing_product.total_price AS [本条目计划单金额],
    a.authorized_order_number AS [委外计划单],
    dbo.processing_orders.project_name AS [工程项目名],
    dbo.processing_orders.sales_date as [销售计划单销售下单日],
    dbo.processing_orders.input_date AS [销售计划单录入日期],
    a.rack_id as [架号]
    from
    (

    SELECT     
    @tb156.processing_order_number,     /*本行错误error137 ,必须声明标量变量 @tb156*/
    @tb156.processing_order_item_number,  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(@tb156.[item_quantity rack]) AS [本条目委外数量],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(@tb156.totalprice) AS [本条目委外金额],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from @tb156 as t where processing_order_item_number = @tb156.processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,''),  /*本行错误error137 ,必须声明标量变量 @tb156*/
    rack_id= STUFF((select distinct ';'+ rack_id from @tb156 as t where processing_order_item_number = @tb156.processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,'') /*本行错误error137 ,必须声明标量变量 @tb156*/


    FROM
    @tb156
    GROUP BY
    @tb156.processing_order_item_number, /*本行错误error137 ,必须声明标量变量 @tb156*/
    @tb156.processing_order_number  /*本行错误error137 ,必须声明标量变量 @tb156*/


    ) as a
    LEFT JOIN dbo.processing_product
    on a.processing_order_item_number=dbo.processing_product.processing_order_item_number and 
    a.processing_order_number=dbo.processing_product.processing_order_number left join processing_orders
    on processing_product.processing_order_number=processing_orders.processing_order_number
    ) as ta 


    end

    end



    • 已编辑 hzpemu 2012年3月29日 4:24
    2012年3月29日 4:14
  • 使用table變數的欄位時不須指定table變數的名稱,嘗試把@tb156拿掉呢?直接使用欄位名稱看看。

    SELECT     
    @tb156.processing_order_number,     /*本行错误error137 ,必须声明标量变量 @tb156*/
    @tb156.processing_order_item_number,  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(@tb156.[item_quantity rack]) AS [本条目委外数量],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(@tb156.totalprice) AS [本条目委外金额],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from @tb156 as t where processing_order_item_number = @tb156.processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,''),  /*本行错误error137 ,必须声明标量变量 @tb156*/
    rack_id= STUFF((select distinct ';'+ rack_id from @tb156 as t where processing_order_item_number = @tb156.processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,'') /*本行错误error137 ,必须声明标量变量 @tb156*/
    FROM
    @tb156
    GROUP BY
    @tb156.processing_order_item_number, /*本行错误error137 ,必须声明标量变量 @tb156*/
    @tb156.processing_order_number  /*本行错误error137 ,必须声明标量变量 @tb156*/

    改成

    SELECT     
    processing_order_number,     /*本行错误error137 ,必须声明标量变量 @tb156*/
    processing_order_item_number,  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum([item_quantity rack]) AS [本条目委外数量],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(totalprice) AS [本条目委外金额],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from @tb156 as t where processing_order_item_number = processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,''),  /*本行错误error137 ,必须声明标量变量 @tb156*/
    rack_id= STUFF((select distinct ';'+ rack_id from @tb156 as t where processing_order_item_number = processing_order_item_number and processing_order_number = processing_order_number for XML path('')),1,1,'') /*本行错误error137 ,必须声明标量变量 @tb156*/
    FROM
    @tb156
    GROUP BY
    processing_order_item_number, /*本行错误error137 ,必须声明标量变量 @tb156*/
    processing_order_number  /*本行错误error137 ,必须声明标量变量 @tb156*/


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/

    • 已标记为答案 hzpemu 2012年3月30日 0:52
    2012年3月29日 4:36
  • 给表变量一个别名,类似这样

    SELECT    
    tb156.processing_order_number,     /*本行错误error137 ,必须声明标量变量 @tb156*/
    ……
    FROM
    @tb156 AS tb156

    联机丛书里面有说明:

    http://msdn.microsoft.com/en-us/library/ms175010.aspx

    Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

    SELECT EmployeeID, DepartmentID 
    FROM @MyTableVar m
    JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
       m.DepartmentID = Employee.DepartmentID);
    

    想不想时已是想,不如不想都不想。

    • 已标记为答案 hzpemu 2012年3月30日 0:51
    2012年3月29日 5:33
    版主
  • 使用table變數的欄位時不須指定table變數的名稱,嘗試把@tb156拿掉呢?直接使用欄位名稱看看。

    SELECT     
    @tb156.processing_order_number,     /*本行错误error137 ,必须声明标量变量 @tb156*/
    @tb156.processing_order_item_number,  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(@tb156.[item_quantity rack]) AS [本条目委外数量],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(@tb156.totalprice) AS [本条目委外金额],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from @tb156 as t where processing_order_item_number = @tb156.processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,''),  /*本行错误error137 ,必须声明标量变量 @tb156*/
    rack_id= STUFF((select distinct ';'+ rack_id from @tb156 as t where processing_order_item_number = @tb156.processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,'') /*本行错误error137 ,必须声明标量变量 @tb156*/
    FROM
    @tb156
    GROUP BY
    @tb156.processing_order_item_number, /*本行错误error137 ,必须声明标量变量 @tb156*/
    @tb156.processing_order_number  /*本行错误error137 ,必须声明标量变量 @tb156*/

    改成

    SELECT     
    processing_order_number,     /*本行错误error137 ,必须声明标量变量 @tb156*/
    processing_order_item_number,  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum([item_quantity rack]) AS [本条目委外数量],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    Sum(totalprice) AS [本条目委外金额],  /*本行错误error137 ,必须声明标量变量 @tb156*/
    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from @tb156 as t where processing_order_item_number = processing_order_item_number and processing_order_number = @tb156.processing_order_number for XML path('')),1,1,''),  /*本行错误error137 ,必须声明标量变量 @tb156*/
    rack_id= STUFF((select distinct ';'+ rack_id from @tb156 as t where processing_order_item_number = processing_order_item_number and processing_order_number = processing_order_number for XML path('')),1,1,'') /*本行错误error137 ,必须声明标量变量 @tb156*/
    FROM
    @tb156
    GROUP BY
    processing_order_item_number, /*本行错误error137 ,必须声明标量变量 @tb156*/
    processing_order_number  /*本行错误error137 ,必须声明标量变量 @tb156*/


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/

    用你的方法确实可以运行,没报错,可是其中有两行

    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from @tb156 as t where processing_order_item_number =  @tb156.processing_order_item_number and processing_order_number =  @tb156.processing_order_number for XML path('')),1,1,''),
    rack_id= STUFF((select distinct ';'+ rack_id from @tb156 as t where processing_order_item_number = @tb156.processing_order_item_number and processing_order_number =  @tb156.processing_order_number for XML path('')),1,1,'')

    改成

    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from @tb156 as t where processing_order_item_number =  processing_order_item_number and processing_order_number =  processing_order_number for XML path('')),1,1,''),
    rack_id= STUFF((select distinct ';'+ rack_id from @tb156 as t where processing_order_item_number = processing_order_item_number and processing_order_number =  processing_order_number for XML path('')),1,1,'')

    这样运行的话,会出现的结果跟原来不是一个样的,能运行,但结果不正确,继续求教高手指点。

    2012年3月29日 5:34
  • 给表变量一个别名,类似这样

    SELECT    
    tb156.processing_order_number,     /*本行错误error137 ,必须声明标量变量 @tb156*/
    ……
    FROM
    @tb156 AS tb156

    联机丛书里面有说明:

    http://msdn.microsoft.com/en-us/library/ms175010.aspx

    Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

    SELECT EmployeeID, DepartmentID 
    FROM @MyTableVar m
    JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
       m.DepartmentID = Employee.DepartmentID);
    

    想不想时已是想,不如不想都不想。

    如果用怡红公子的方法,把黑色部份代码改成

    if (DATALENGTH(ltrim(rtrim(@filterstring)))=0)  
    BEGIN
    select * from (
    SELECT
    a.processing_order_number AS [销售计划单],
    a.processing_order_item_number AS [销售计划单条目],
    a.[本条目委外数量],
    dbo.processing_product.item_quantity AS [本条目计划单数量],
    a.[本条目委外金额],
    dbo.processing_product.total_price AS [本条目计划单金额],
    a.authorized_order_number AS [委外计划单],
    dbo.processing_orders.project_name AS [工程项目名],
    dbo.processing_orders.sales_date as [销售计划单销售下单日],
    dbo.processing_orders.input_date AS [销售计划单录入日期],
    a.rack_id as [架号]
    from
    (
    SELECT
    tb156.processing_order_number,
    tb156.processing_order_item_number,
    Sum(tb156.[item_quantity rack]) AS [本条目委外数量],
    Sum(tb156.totalprice) AS [本条目委外金额],
    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from tb156 as t where processing_order_item_number =  tb156.processing_order_item_number and processing_order_number = tb156.processing_order_number for XML path('')),1,1,''),
    rack_id= STUFF((select distinct ';'+ rack_id from tb156 as t where processing_order_item_number = tb156.processing_order_item_number and processing_order_number =  tb156.processing_order_number for XML path('')),1,1,'')
    FROM
    @tb156 as tb156
    GROUP BY
    tb156.processing_order_item_number,
    tb156.processing_order_number
    ) as a
    LEFT JOIN dbo.processing_product
    on a.processing_order_item_number=dbo.processing_product.processing_order_item_number and a.processing_order_number=dbo.processing_product.processing_order_number
    left join processing_orders
    on processing_product.processing_order_number=processing_orders.processing_order_number
    ) as ta 
    end

    保存的时侯没报错,但运行时报错

    Procedure execution failed
    42S02 - [SQL Server]对象名 'tb156' 无效。    看来对于我这个新手来说比较有难度,-__-!!!


    • 已编辑 hzpemu 2012年3月29日 6:59
    2012年3月29日 6:43
  • 怡红公子的方法 报错的原因是因为有这两行

    authorized_order_number = STUFF((select distinct ';'+ authorized_order_number from tb156 as t where processing_order_item_number =  tb156.processing_order_item_number and processing_order_number = tb156.processing_order_number for XML path('')),1,1,''),
    rack_id= STUFF((select distinct ';'+ rack_id from tb156 as t where processing_order_item_number = tb156.processing_order_item_number and processing_order_number =  tb156.processing_order_number for XML path('')),1,1,'')

    这两行成

    authorized_order_number ='',rack_id=''就没错,但是我需要解决这个问题,怎样才能不提示42S02 - [SQL Server]对象名 'tb156' 无效。 正常运行。

    谢谢!
    • 已编辑 hzpemu 2012年3月29日 6:59
    2012年3月29日 6:58
  • select distinct ';'+ authorized_order_number from @tb156 as t 这里不要把@tb156改掉。

    你没看懂联机丛书里面那段英文?意思是”table 变量“


    想不想时已是想,不如不想都不想。

    • 已标记为答案 hzpemu 2012年3月30日 0:51
    2012年3月29日 15:25
    版主
  • select distinct ';'+ authorized_order_number from @tb156 as t 这里不要把@tb156改掉。

    你没看懂联机丛书里面那段英文?意思是”table 变量“


    想不想时已是想,不如不想都不想。

    很好,我的问题彻底解决了,非常感谢谢两位!
    2012年3月30日 0:50