– We should create procedure in database(Table Function that result as the table)
Example in SQL Server

USE [ICEPOS]
GO
/****** Object: UserDefinedFunction [dbo].[TBL_Web_Search] Script Date: 5/19/2017 3:20:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[TBL_Web_Search]()
RETURNS @temptable TABLE (ID int NOT NULL,
BrandID int NOT NULL,
DepartmentID int NOT NULL,
CategoryID int NOT NULL,
Name nvarchar(max) NOT NULL,
PictureName nvarchar(max) NOT NULL,
WebsiteShow int NOT NULL,
WebsitePrice money NOT NULL,
Price money NOT NULL,
PriceA money NOT NULL,
PriceB money NOT NULL,
PriceC money NOT NULL,
PriceID money NOT NULL,
Quantity money NOT NULL,
QuantityDiscount int NOT NULL,
QuantityDiscountExpired date NOT NULL,
Active int NOT NULL,
LastUpdated date NOT NULL,
[Key] text NOT NULL )
AS
BEGIN

INSERT INTO @temptable(ID, BrandID, DepartmentID, CategoryID,Name, PictureName, WebsiteShow, WebsitePrice , Price, PriceA, PriceB, PriceC, PriceID, Quantity, QuantityDiscount, QuantityDiscountExpired, Active, LastUpdated, [Key])
SELECT i.ID, i.BrandID, c.DepartmentID, i.CategoryID, i.Name, i.PictureName, i.WebsiteShow, i.WebsitePrice, i.Price, i.PriceA, i.PriceB, i.PriceC, i.PriceID, i.Quantity, i.QuantityDiscount, i.QuantityDiscountExpired, i.Active, i.LastUpdated,

b.BrandName + ‘ ‘ + d.Name + ‘ ‘ + c.Name + ‘ ‘ + i.Name + ‘ ‘ + i.Notes

FROM Item as i
JOIN Category as c on i.CategoryID = c.ID
JOIN Department as d on c.DepartmentID = d.ID
JOIN Brand as b on i.BrandID = b.ID
WHERE i.Active = 1
AND c.ActiveInWeb = 1 AND d.ActiveInWeb = 1

RETURN;

END

Advertisements