Selecting row number from union join + paging

[emozley]

Hi,

I am trying to write a stored procedure that allows me to do a select union command and get a row number for each row and then use this recordset for paging.

The code I have so far is:

USE [Clients]
GO
/****** Object:  StoredProcedure [dbo].[PartySearch]    Script Date: 06/26/2012 10:08:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PartySearch] 
@PageNum INT, @PageSize INT, @search varchar(128)

AS

WITH searchRN AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Result) ROW_NUM
FROM
(
SELECT Users.FirstName + ' ' + Users.Surname AS Result
FROM Users
WHERE Users.FirstName LIKE '%'+@search+'%' OR Users.Surname LIKE '%'+@search+'%'

UNION

SELECT Organisation AS Result
FROM Users
WHERE Users.Organisation LIKE '%'+@search+'%' 

UNION

SELECT 'Held For: '+HeldFor AS Result
FROM Schedules
WHERE Schedules.HeldFor LIKE '%'+@search+'%'

UNION

SELECT 'Relating to: '+RelatingTo AS Result
FROM Schedules
WHERE Schedules.RelatingTo LIKE '%'+@search+'%'
)
)    <------ error is here apparently

SELECT * 
FROM searchRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize

However it says there is an error at the final bracket. Any ideas why it is failing?

Thanks very much

Ed

 

[drlex]

Ed,
I think you need a reference on your sub-query; try adding in one at the end.

...
)[red][b]T1[/b][/red]
)    

SELECT * 
FROM searchRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize

soi là, soi carré