必须在sqlserver中声明标量变量吗

I try to collect my data from number function and put it inside variable table call with @GetAllRep and then add a condition such as

我尝试从number函数中收集我的数据，并将其放入变量table调用中@GetAllRep，然后添加一个条件，比如。

WHERE  @GetAllRep.UserId IN ( SELECT @tableSubset.userId FROM @tableSubset)

for filter my data by UserIds field but Get me this error :

通过UserIds字段过滤我的数据，但是得到这个错误:

Must declare the scalar variable "@GetAllRep".

必须声明标量变量“@GetAllRep”。

Must declare the scalar variable "@tableSubset".

必须声明标量变量“@汤匙ubset”。

    ALTER PROCEDURE [dbo].[ProceCompleteReportB2B]
(
    @startdate     DATETIME,
    @enddate       DATETIME,
    @top           INT,
    @state         INT,
    @type          INT,
    @subset        NVARCHAR(15),
    @oneSubset     NVARCHAR(128),
    @userId        NVARCHAR(128)
)
AS
BEGIN
    DECLARE @GetAllRep TABLE
            (
                [Id] [int],
                [factorno][int],
                [PayType][bit],
                [ReserveNumber][int],
                [ReserveState][int],
                [state][nvarchar](50),
                [Price][nvarchar](50),
                [ReserveType][nvarchar](50),
                [ObjectIdDepartue][int],
                [IssueDate][nvarchar](50),
                [BankId][int],
                [Confirmed][bit],
                [TrackingCode][nvarchar](50),
                [Transactionsuccess][nvarchar](50),
                [Name][nvarchar](128),
                [TiketUrl][nvarchar](128),
                [ObjectIdReturn] [int] NULL,
                [TelNumber][nvarchar](50) NULL,
                [UserId][nvarchar](128)
            )

    IF (@type = 0)
        INSERT INTO @GetAllRep
        SELECT *
        FROM   dbo.[FuncFlightReportB2B](@startdate, @enddate, @top, @state)
        UNION
        SELECT *
        FROM   dbo.[FuncTrainReportB2B](@startdate, @enddate, @top, @state)
        UNION
        SELECT *
        FROM   dbo.[FuncCharterReportB2B](@startdate, @enddate, @top, @state)
        UNION
        SELECT *
        FROM   dbo.[FuncBusReportB2B](@startdate, @enddate, @top, @state)
        UNION
        SELECT *
        FROM   dbo.[FuncInsuranceReportB2B](@startdate, @enddate, @top, @state)
        UNION
        SELECT *
        FROM   dbo.[FuncCarReportB2B](@startdate, @enddate, @top, @state)
        UNION
        SELECT *
        FROM   dbo.[FuncHotelReportB2B](@startdate, @enddate, @top, @state)
        UNION
        SELECT *
        FROM   dbo.[FuncIFlightReportB2B](@startdate, @enddate, @top, @state)
        UNION
        SELECT *
        FROM   dbo.[FuncMassageReportB2B](@startdate, @enddate, @top, @state)
    -------------------------------------------------------------------
    IF (@type = 1)
        INSERT INTO @GetAllRep
        SELECT *
        FROM   dbo.[FuncFlightReportB2B](@startdate, @enddate, @top, @state)
               -----------------------------------------------------------------------
    ELSE 
    IF (@type = 2)
        INSERT INTO @GetAllRep
        SELECT *
        FROM   dbo.[FuncTrainReportB2B](@startdate, @enddate, @top, @state)
    -----------------------------------------------------------------------
    IF (@type = 3)
        INSERT INTO @GetAllRep
        SELECT *
        FROM   dbo.[FuncCharterReportB2B](@startdate, @enddate, @top, @state)
               ---------------------------------------------------------------------
    ELSE 
    IF (@type = 4)
        INSERT INTO @GetAllRep
        SELECT *
        FROM   dbo.[FuncBusReportB2B](@startdate, @enddate, @top, @state)
    ---------------------------------------------------------------------
    IF (@type = 5)
        INSERT INTO @GetAllRep
        SELECT *
        FROM   dbo.[FuncInsuranceReportB2B](@startdate, @enddate, @top, @state)
               ---------------------------------------------------------------------
    ELSE 
    IF (@type = 6)
        INSERT INTO @GetAllRep
        SELECT *
        FROM   dbo.[FuncCarReportB2B](@startdate, @enddate, @top, @state)
    ---------------------------------------------------------------------
    IF (@type = 7)
        INSERT INTO @GetAllRep
        SELECT *
        FROM   dbo.[FuncHotelReportB2B](@startdate, @enddate, @top, @state)
               ---------------------------------------------------------------------
    ELSE 
    IF (@type = 8)
        INSERT INTO @GetAllRep
        SELECT *
        FROM   dbo.[FuncIFlightReportB2B](@startdate, @enddate, @top, @state)
    ELSE 
    IF (@type = 9)
        INSERT INTO @GetAllRep
        SELECT *
        FROM   dbo.[FuncMassageReportB2B](@startdate, @enddate, @top, @state)
    ---------------------------------------------------------------------
    DECLARE @tableSubset TABLE ([userId] [nvarchar](128))
    INSERT INTO @tableSubset
    SELECT *
    FROM   dbo.FuncGetSubsetUserIds(@subset, @oneSubset, @userId) AS SubsetUserIds

    SELECT *
    FROM   @GetAllRep
    WHERE  @GetAllRep.UserId IN ( SELECT @tableSubset.userId FROM @tableSubset)
    ORDER BY
           IssueDate DESC
END

How to fix this ?

如何解决这个问题?

1 个解决方案

#1

Just create alias to Table variable and use it in WHERE clause

只需为表变量创建别名，并在WHERE子句中使用它

SELECT *
FROM   @GetAllRep GetAllRep
                 ---^^^ 
WHERE  GetAllRep.UserId IN ( SELECT userId FROM @tableSubset t)
ORDER BY
       IssueDate DESC