SQL Paging with VS2005

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??


.

Relevant Pages

  • Performance concerns in SPs and Tables
    ... 1- Is there anything wrong to use varchar all the time in stored procedures? ... I would not want to create a very long stored procedure when I ... 6-I know I can declare up to 1,024 parameters in a stored procedure. ... specify the data type when assigning values to the SP's parameters. ...
    (microsoft.public.sqlserver.programming)
  • Re: Displaying Stored Procedures using Grid View
    ... Declare @ticketid as varchar ... Declare @Technician as varchar ... create a new parameter insidethe stored procedure ...
    (microsoft.public.dotnet.framework.aspnet)
  • Must declare the variable error
    ... I have written a very simple stored procedure which returns the count ... Must declare the variable @count. ... if @dateofbirth '' ...
    (microsoft.public.sqlserver.programming)
  • Cursor Not Running Correctly.. Need Help/Suggestions...
    ... DECLARE @NewOrderID INT ... DECLARE @CompanyName VARCHAR ... INNER JOIN Orders ... DECLARE @SubTotal MONEY ...
    (microsoft.public.sqlserver.programming)
  • Failed to export to a text file
    ... declare @scrip varchar ... Estoy exportar datos de una tabla a un archivo de texto, ...
    (microsoft.public.sqlserver.dts)
Loading