set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SearchVague]
(
@Description VARCHAR(1000),
@DescriptionLength INT,
@PageNumber TINYINT,
@ProductsPerPage TINYINT,
@HowManyResults SMALLINT OUTPUT
)
AS
/*Create the table variable that will contain the search results*/
DECLARE @Products TABLE
(RowNumber SMALLINT IDENTITY(1,1) NOT NULL,
ProductId INT,
Name VARCHAR(50),
Description VARCHAR(1000),
Price Money,
Image1FileName VARCHAR(50),
Image2FileName VARCHAR(50)
)
INSERT INTO @Products
SELECT ProductId,Name,SUBSTRING(Description,1,@DescriptionLength)+'...' AS Description,
Price,Image1FileName,Image2FileName
FROM Product
SELECT ProductId,Name,SUBSTRING(Description,1,@DescriptionLength)+'...' AS Description,
Price,Image1FileName,Image2FileName
FROM @Products
WHERE Name LIKE '%'+@Description+'%'
AND RowNumber BETWEEN (@PageNumber-1)*@ProductsPerPage+1
AND @PageNumber*@ProductsPerPage