CODEWITHSUNDEEP
sqlsql-serverstored-functions
user9278128
user9278128

Reputation:

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.

Upvotes: 0

Views: 231

Answers (1)

Tony Dong
Tony Dong

Reputation: 3313

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

Upvotes: 1

Related Questions