Stored function not returning a table in SQL Server

Question

I'm trying to create a stored function called func_GetMenuItemsForMenu.

For this function, I need to pass the ID of the menu to it. The function will find all MenuItems that are on the menu. It will return a table.

Here is what I have tried:

CREATE FUNCTION [dbo].[func_GetMenuItemsForMenu] 
    (@MenuItemMenuItemID NVARCHAR(200))
RETURNS TABLE
AS
   RETURN (SELECT Menu.MenuID 
           FROM Menu, MenuItem
           WHERE MenuItem.MenuID LIKE @MenuItemMenuItemID + '%'
             AND MenuItem.MenuID = Menu.MenuID)
GO

Here is my table structure:

Table Structure

I'm only getting the MenuID and it's not returning the menu item as well that's on the specific menu.

Because only have Menu.MenuID in the SELECT list.... you should also use explicit joins. Your old-style join has really, really old. — S3S
– S3S, Commented Feb 7, 2019 at 22:02
No problem, but this would be the same for any select statement. Only the columns listed between select and from would be returned. Change SELECT Menu.MenuID FROM Menu... to SELECT Menu.MenuID, MenuItem.MenuItemTitle FROM Menu... or what ever column you want to return. — S3S
– S3S, Commented Feb 7, 2019 at 22:04

S3S · Accepted Answer · 2019-02-07 22:07:27Z

1

You did not select the columns you need and that is why you only get MenuID. I add MenuItemTitle here.

CREATE FUNCTION [dbo].[func_GetMenuItemsForMenu] 
(
    @MenuItemMenuItemID NVARCHAR(200)
)
RETURNS TABLE
AS
RETURN (
    SELECT 
        Menu.MenuID, 
        MenuItem.MenuItemTitle 
    FROM Menu
    INNER JOIN 
        MenuItem ON 
        MenuItem.MenuID = Menu.MenuID
    WHERE
        MenuItem.MenuID LIKE @MenuItemMenuItemID + '%')
GO

edited Feb 7, 2019 at 22:07

S3S

25.2k5 gold badges29 silver badges48 bronze badges

answered Feb 7, 2019 at 22:05

Tony Dong

3,3431 gold badge31 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Stored function not returning a table in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related