Removing individual results from a paged set of results.

Hi,

I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().

I would like to give my users to option of removing individual people from this list but cannot find a way to do this.

I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.

Is there any way to do this? I am still new to stored procedures so any advice would be helpful.

Thanks

The only way I know to do it is to use dynamic sql.

|||

Can you post your SQL stored procedure? Its a lot easier to give suggestions if we can see what we're working with. There are several ways to do this, but I'd like to see what I'm working with before I make a suggestion.

Stu

|||

This is the SPROC I am currently working with without any code to remove individual rows.

CREATE PROCEDURE [dbo].[zk_update_request_england]
(
@.property_type tinyint,
@.market_status tinyint,
@.price int,
@.bedrooms tinyint,
@.search_location varchar(30),
@.PageSize int,
@.PageIndex int,
@.TRV int,
@.topx int,
@.TotalRequests int OUTPUT
)

AS

SET NOCOUNT ON

BEGIN

-- Set Paging limits
Declare @.firstRow Int;
Declare @.lastRow Int;
Set @.firstRow = (@.PageIndex * @.PageSize) + 1;
Set @.lastRow = @.firstRow + @.PageSize - 1;

-- Load count into @.TotalRequests
Set @.TotalRequests = (SELECT Count(*)
FROM [dbo].[zk_request_england]
WHERE property_type = @.property_type
AND market_status = @.market_status
AND bedrooms <= @.bedrooms
AND search_location = @.search_location
AND min_price <= @.price
AND max_price >= @.price)

-- If @.TotalRequests is less than @.topx change @.topx to @.TotalRequests
IF @.TotalRequests <= @.topx
SET @.topx = @.TotalRequests
ELSE
SET @.TotalRequests = @.topx
;

WITH SearchResults AS
(
SELECT TOP(@.topx) ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber,
id,
user_name,
bedrooms,
min_price,
max_price,
property_description,
searched
FROM [dbo].[zk_request_england]
WHERE property_type = @.property_type
AND market_status = @.market_status
AND bedrooms <= @.bedrooms
AND search_location = @.search_location
AND min_price <= @.price
AND max_price >= @.price
)

SELECT id,
user_name,
bedrooms,
min_price,
max_price,
property_description
FROM SearchResults
WHERE RowNumber BETWEEN @.firstRow and @.lastRow

END

SET NOCOUNT OFF