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:
x
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:
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