andysross
asked on
Dynamic WHERE clause with paging functionality in SQL 2005
Hi
I would like to pass a where clause as a parameter (e.g.: FirstName Like '%Jo%')
When I run this stored procedure in query analyzer I get:
Incorrect syntax near ')'.
Invalid object name 'ItemEntries'.
I followed an example on a previous question on expert exchange but the solution did not seem to work for me.
Seems to be the dynamic WITH that throws an error.
Is it in anyway possible to resolve this problem?
I would like to pass a where clause as a parameter (e.g.: FirstName Like '%Jo%')
When I run this stored procedure in query analyzer I get:
Incorrect syntax near ')'.
Invalid object name 'ItemEntries'.
I followed an example on a previous question on expert exchange but the solution did not seem to work for me.
Seems to be the dynamic WITH that throws an error.
Is it in anyway possible to resolve this problem?
ALTER PROCEDURE [dbo].[PagedUserList]
(
@PageIndex int,
@NumRows int,
@SearchText nvarchar(1000)
)
AS
BEGIN
DECLARE @nSQL nvarchar(1090)
SET @nSQL = 'SELECT (SELECT COUNT(*) FROM Users WHERE '+@SearchText+') AS MemberCount'
EXECUTE sp_executeSQL @nSQL
DECLARE @startRowIndex int;
SET @startRowIndex = (@PageIndex * @NumRows) + 1;
DECLARE @wSQL nvarchar(1090)
SET @wSQL = 'WITH ItemEntries AS (SELECT ROW_NUMBER() OVER (ORDER BY LastName DESC) AS Row, UserID, FirstName, LastName FROM Users WHERE '+@SearchText+')'
EXEC sp_executesql @wSQL
SELECT UserID, FirstName, LastName
FROM ItemEntries
WHERE Row between
@startRowIndex and @StartRowIndex+@NumRows-1
END
Deepika_Rastogi
check whether the ' character is passed properly in the parameter
ASKER
Hi
I made the parameter @SearchText redundent to eliminate how I may be passing this parameter and ran it again in query analyzer with the result below. I still get the same error
--------------------------
PagedUserList 1, 9
--------------------------
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 208, Level 16, State 1, Procedure PagedUserList, Line 24
Invalid object name 'ItemEntries'.
I made the parameter @SearchText redundent to eliminate how I may be passing this parameter and ran it again in query analyzer with the result below. I still get the same error
--------------------------
PagedUserList 1, 9
--------------------------
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 208, Level 16, State 1, Procedure PagedUserList, Line 24
Invalid object name 'ItemEntries'.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PagedUserList]
(
@PageIndex int,
@NumRows int
--@SearchText nvarchar(1000)
)
AS
BEGIN
DECLARE @nSQL nvarchar(1090)
SET @nSQL = 'SELECT (SELECT COUNT(*) FROM Users WHERE Status = 1) AS MemberCount'
EXECUTE sp_executeSQL @nSQL
DECLARE @startRowIndex int;
SET @startRowIndex = (@PageIndex * @NumRows) + 1;
DECLARE @wSQL nvarchar(1090)
SET @wSQL = 'WITH ItemEntries AS (SELECT ROW_NUMBER() OVER (ORDER BY LastName DESC) AS Row, UserID, FirstName, LastName FROM Users WHERE Status = 1)'
EXEC sp_executesql @wSQL
SELECT UserID, FirstName, LastName
FROM ItemEntries
WHERE Row between
@startRowIndex and @StartRowIndex+@NumRows-1
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Soory
As u r using With clause the select query of the table with which With clase is attached should be embed within the string itself
As u r using With clause the select query of the table with which With clase is attached should be embed within the string itself
ASKER
This solution was great. The only thing I had to do was cast the int's to varchar within the string