Hi there,
I'm using SQL2005 standard an am accessing the stored procedure via an ASP page.
We have a table of products in excess of 10,000 so we developed a stored procedure to
return the only a snapshot of the data based on a value sent via the asp page - Works Great.
What we are trying to do is to filter the recordset and then send the stored procedure a whereclause
for it to use.
IF @MyWhereClause is not null
WHERE @MyWhereClause
else
WHERE ProductID is not null
end if
The above code obvioulsy does not work but it is the sort of thing we are trying to achieve.
Below is the store procedure in its full glory.
Thanks
Paul
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Products_Page2]
(
@Page int,
@RecsPerPage int,
@MyWhereClause nchar(250)
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #Temp_Products
(
ID int IDENTITY,
ProductID uniqueidentifier,
PartNumber nchar(20),
DataSheet nchar(10),
Type nchar(20),
Description nchar(100),
M nchar(10),
GripRange nchar(20),
HoleSize float,
OverallLength float,
HeadDiameter float,
HeadThickness float,
BodySetLength float,
Material nchar(50),
PricePer000UK money
)
-- Insert the rows from mkt_contacts into the temp. table
INSERT INTO #Temp_Products
(
ProductID,
PartNumber,
DataSheet,
Type,
Description,
M,
GripRange,
HoleSize,
OverallLength,
HeadDiameter,
HeadThickness,
BodySetLength,
Material,
PricePer000UK
)
SELECT
ProductID,
PartNumber,
DataSheet,
Type,
Description,
M,
GripRange,
HoleSize,
OverallLength,
HeadDiameter,
HeadThickness,
BodySetLength,
Material,
PricePer000UK
FROM Products
IF @MyWhereClause is not null
WHERE @MyWhereClause
else
WHERE ProductID is not null
end if
ORDER BY Datasheet Desc
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #Temp_Products TI
WHERE TI.ID >= @LastRec
)
FROM #Temp_Products
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
Start Free Trial
