Must declare the scalar variable in sqlserver

Question

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

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

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

Must declare the scalar variable "@GetAllRep".

Must declare the scalar variable "@tableSubset".

    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 ?

Jaydip Jadhav · Accepted Answer · 2017-03-16 07:01:23Z

3

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

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

edited Mar 16, 2017 at 7:01

answered Mar 16, 2017 at 6:53

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hmahdavi Over a year ago

ok this is right . Now get this error :Must declare the scalar variable "@tableSubset".

Collectives™ on Stack Overflow

Must declare the scalar variable in sqlserver

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related