Kodo
"The Shoe"
- Local time
- Today, 09:41
- Joined
- Jan 20, 2004
- Messages
- 707
I'm trying to setup a paging query in SQL server 2000 and I'm having a real pain in the butt time with it.
What I want to do is pass in the text of the query and execute it.
So @SQLFrom would contain " From [tablename] "
but I keep getting
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.
any ideas would be great.
Thanks!
Alter PROCEDURE spXtest
(
@SQLText varchar(8000),
@SQLFrom varchar(8000),
@PrimaryKey varchar(100),
@PageSize INT,
@PageIndex INT = 1,
@SortField varchar(100),
@sortdirection varchar(4),
@QueryFilter VARCHAR(100) = NULL,
@ordersite bigint=205
)
AS
DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)
SET @SQLText= CONVERT(VARCHAR,@SQLText)
SET @SQLFrom= CONVERT(VARCHAR,@SQLFrom)
SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))
IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN
EXEC ( 'SELECT * ' + @SQLFrom + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP '+ @SizeString +' '+ @PrimaryKey + ' '+ @SQLFrom + ' WHERE ' + @PrimaryKey +' NOT IN
(SELECT TOP '+ @prevstring +' '+ @PrimaryKey + ' ' + @SQLFrom + ' ORDER BY '+ @SortField+ ' '+ @SortDirection+' )
ORDER BY '+ @SortField+ ' '+ @SortDirection+' )
ORDER BY '+ @SortField+ ' '+ @SortDirection
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount ' + @SQLFrom )
END
ELSE
BEGIN
EXEC ( 'SELECT * FROM ECCustomer INNER JOIN ECOrder ON ECCustomer.customer_code = ECOrder.order_customer WHERE order_id IN
(SELECT TOP '+ @SizeString +' order_id FROM ECCustomer INNER JOIN ECOrder ON ECCustomer.customer_code =ECOrder.order_customer WHERE ' + @QueryFilter + ' AND order_site=' + @ordersite +' and order_id NOT IN
(SELECT TOP '+ @prevstring +' order_id FROM ECCustomer INNER JOIN ECOrder ON ECCustomer.customer_code =ECOrder.order_customer WHERE ' + @QueryFilter + ' AND order_site=' + @ordersite +' ORDER BY '+ @SortField+ ' '+ @SortDirection+' )
ORDER BY '+ @SortField+ ' '+ @SortDirection+' )
ORDER BY '+ @SortField+ ' '+ @SortDirection
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ECCustomer INNER JOIN ECOrder ON ECCustomer.customer_code = ECOrder.order_customer WHERE ' + @QueryFilter + ' AND order_site=' + @Ordersite)
END
What I want to do is pass in the text of the query and execute it.
So @SQLFrom would contain " From [tablename] "
but I keep getting
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.
any ideas would be great.
Thanks!
Alter PROCEDURE spXtest
(
@SQLText varchar(8000),
@SQLFrom varchar(8000),
@PrimaryKey varchar(100),
@PageSize INT,
@PageIndex INT = 1,
@SortField varchar(100),
@sortdirection varchar(4),
@QueryFilter VARCHAR(100) = NULL,
@ordersite bigint=205
)
AS
DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)
SET @SQLText= CONVERT(VARCHAR,@SQLText)
SET @SQLFrom= CONVERT(VARCHAR,@SQLFrom)
SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))
IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN
EXEC ( 'SELECT * ' + @SQLFrom + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP '+ @SizeString +' '+ @PrimaryKey + ' '+ @SQLFrom + ' WHERE ' + @PrimaryKey +' NOT IN
(SELECT TOP '+ @prevstring +' '+ @PrimaryKey + ' ' + @SQLFrom + ' ORDER BY '+ @SortField+ ' '+ @SortDirection+' )
ORDER BY '+ @SortField+ ' '+ @SortDirection+' )
ORDER BY '+ @SortField+ ' '+ @SortDirection
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount ' + @SQLFrom )
END
ELSE
BEGIN
EXEC ( 'SELECT * FROM ECCustomer INNER JOIN ECOrder ON ECCustomer.customer_code = ECOrder.order_customer WHERE order_id IN
(SELECT TOP '+ @SizeString +' order_id FROM ECCustomer INNER JOIN ECOrder ON ECCustomer.customer_code =ECOrder.order_customer WHERE ' + @QueryFilter + ' AND order_site=' + @ordersite +' and order_id NOT IN
(SELECT TOP '+ @prevstring +' order_id FROM ECCustomer INNER JOIN ECOrder ON ECCustomer.customer_code =ECOrder.order_customer WHERE ' + @QueryFilter + ' AND order_site=' + @ordersite +' ORDER BY '+ @SortField+ ' '+ @SortDirection+' )
ORDER BY '+ @SortField+ ' '+ @SortDirection+' )
ORDER BY '+ @SortField+ ' '+ @SortDirection
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ECCustomer INNER JOIN ECOrder ON ECCustomer.customer_code = ECOrder.order_customer WHERE ' + @QueryFilter + ' AND order_site=' + @Ordersite)
END