Dynamic Stored Procedure Problem. (1 Viewer)

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

got it..my text was being padded with whitespace and the convert was cutting the text at 50 characters. I removed the convert for the @SQLText and @SQLFor and used a regex to replace 2 or more whitespaces with just one whitespace and passed that to the procedure.. bicketty bam!