Skip to content
Advertisement

Stored function not returning a table in SQL Server

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.

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement