SQL Paging with VS2005

[Islamegy®]

I don't know why All my stored Procedure which provide custom pageing don't
work in .Net 2..
First i was using Procedures genrated by CodeSmith and N-Tier.. which create
#temp table with auto identity..
But When i create a dataset it parse my procedure uncorrectly and genrate
sqlexception in runtime even it work fine in Managment Studio..

So i tried to create my own Paging using dynamic SQL

CREATE PROCEDURE [dbo].[GetPagedResult]
-- Add the parameters for the stored procedure here
(
@TableName varchar (20),
@ColumnNamesCollection varchar (200) = '*',
@PageIndex int = 0,
@PageSize int = 5,
@WhereCriteria varchar (200) = NULL,
@SortExpression varchar (200) = NULL
)
AS
BEGIN
DECLARE @WHEREWord varchar (20)
DECLARE @ANDWord varchar (20)
DECLARE @OrderBy varchar (20)
DECLARE @IgnoredRows int
SET @IgnoredRows = @PageSize * @PageIndex
-- Check Where Expression
IF @WhereCriteria != NULL AND @WhereCriteria != ''
Begin
SET @WHEREWord = ' WHERE '
SET @ANDWord = ' AND '
End
ELSE
Begin
SET @WHEREWord = ''
SET @ANDWord = ''
End
-- Check Sorting Expression
IF @SortExpression != NULL AND @SortExpression != ''
SET @OrderBy = ','
ELSE
SET @OrderBy = ''
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- SET NOCOUNT ON;
-- Insert statements for procedure here
IF @PageIndex != 0
Begin
EXEC('SELECT TOP '+ @PageSize + ' ' + @ColumnNamesCollection + ' FROM ' +
@TableName + ' WHERE ID NOT IN ( SELECT TOP '+ @IgnoredRows +
' ID FROM ' + @TableName + ' ' + @WHEREWord + ' ' + @WhereCriteria +
' ORDER BY ID ' + @OrderBy + ' ' + @SortExpression + ' ) ' + @ANDWord + ' '
+
@WhereCriteria + ' ORDER BY ID ' + @OrderBy + ' ' + @SortExpression
)
RETURN @@ROWCOUNT
END
Else
Begin
EXEC('SELECT TOP '+ @PageSize + ' ' + @ColumnNamesCollection + ' FROM ' +
@TableName + ' ' + @WHEREWord + ' ' + @WhereCriteria +
' ' + @OrderBy + ' ' + @SortExpression
)
RETURN @@ROWCOUNT
END
END

but it didn't work also for some reason(!!) and it work fine in sql
managment studio too...
So what's wrong in Dynamic storedprocedure?? why dataset can't work with
them??

 

[Mark Rae]

but it didn't work also for some reason(!!)

1) How do you know it didn't work?

2) What error did you get?