Reputation: 1

dynamic Select statement on declared table variable - SYBASE

I have one declared table variable in stored procedure,(sybase database). Data is populated in that table as needed. But now I want to select particular columns based on different conditions. I am trying dynamic SQL to do the same but not working. Can it go like I am assuming?

ALTER PROCEDURE "dbo"."sp_userMenus" 
@fundName VARCHAR(20) , @userName VARCHAR(20)
AS
BEGIN
declare @tableData as table (
        id int IDENTITY(1,1),
        [menuDisplayName] nvarchar(100),
        [menuOrder] int,
        [menuType] nvarchar(100),
        [parentVerticalMenu] nvarchar(100),
        [parentHorizontalMenu] nvarchar(100),
        [groupID] int,
        [inDashboardAll] int,
        [inDashboardOverview] int,
        [inDetail] int,
        [inSummary] int,
        [isDetail] int,
        [zOrder] int
    )

    --insert into @tableData

    if @userName = 'ADMIN'
        SET @SQLQuery = 'select *
            from @tableData order by parentVerticalMenu, parentHorizontalMenu'
    else
        SET @SQLQuery = 'select menuDisplayName,menuOrder,menuType,parentVerticalMenu,parentHorizontalMenu 
            from @tableData order by parentVerticalMenu, parentHorizontalMenu'

    EXEC sp_executesql @SQLQuery

END

getting error "Must declare the scalar variable "@tableData" OR Must declare the table variable "@tableData".

Upvotes: 0