Tichnique Advance Search

– 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
This entry was posted in Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s