Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2009
    Location
    Selangor, Malaysia
    Posts
    5

    Question Unanswered: Paging Stored Procedure

    I need to create a paging result in SQL Server 2000. I already create the stored proc but the result is error. "Invalid Syntax near keyword FROM"

    This is the stored Proc.
    CREATE PROCEDURE UserPaging(@sqlStr varchar(5000), @orderBy varchar(1000), @startRow int, @endRow int)
    AS
    DECLARE @Out int, @SQLSTRING varchar(5000)
    DECLARE @fieldName varchar(1000)
    DECLARE @tableName varchar(1000)
    DECLARE @pageSize int

    SET @pageSize = @endRow - @startRow
    SET @fieldName = SUBSTRING(@sqlStr, 8, PATINDEX('%from%', @sqlStr) - 8)
    SET @tableName = SUBSTRING(@sqlStr, PATINDEX('%from%', @sqlStr), 4)

    SET NOCOUNT ON

    SET @SQLSTRING = N'select '+@fieldName+' from ( SELECT TOP '+ CAST(@pageSize as varchar(10)) + ' ' + @fieldName + ' from (SELECT TOP ' + CAST(@endRow as varchar(10)) +
    ' ' + @fieldName + ' FROM (SELECT TOP ' + CAST(@endRow as varchar(10)) +
    ' ' + @fieldName + ' FROM ' +@tableName + ' as T1
    ORDER BY ' + @orderBy + ') AS T2 ORDER BY ' + @orderBy + ' DESC ) AS T3) As T4 ORDER BY ' + @orderBy + ' ASC'

    SET @SQLSTRING = @fieldName
    EXEC(@SQLSTRING)
    RETURN
    GO

    The Java application that call this stored proc can only provide this parameter : String sql, String orderBy, int startRow, int endRow

    From what i see, im quite sure that the error is most probably came from the substring function that i use. But when i test in query designer "select SUBSTRING('select id, name, email from user', 8, PATINDEX('%from%', 'select id, name, email from user') - 8)", there is no error....

    Or, if anyone have other best way to create stored proc for paging?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try adding PRINT @SQLString before you exec, to aid in debugging.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and this stored procedure is just begging to be hacked by sql injection.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman View Post
    Try adding PRINT @SQLString before you exec, to aid in debugging.
    That's your advice?

    Dynamic paging for dynamic queries?

    oye
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Apart from the possible SQL-injection vulnerability, I don't think the logic behind your query is correct. Somewhere you should filter out records that come before @startRow. I see three "SELECT TOP" in a row, it just doesn't give me a warm feeling about the correctness.

    Just first try to make a query that does what you want, then start thinking of how to embed it in a sproc.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    in my experience, whatever you do, server side paging in sql 2000 is ugly and slow. With CTEs in 2K5, it is awesome. In 2000 and I must have done it 8 different ways, it always stinks.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    well it would be easier if you knew what table you were working on....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser View Post
    That's your advice?

    Dynamic paging for dynamic queries?

    oye
    That's my advice for debugging dynamic code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser View Post
    well it would be easier if you knew what table you were working on....
    That's your advice?
    (Sorry, could not resist.)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey Look who found their cane and made it in the door

    Quote Originally Posted by blindman View Post
    That's your advice?
    (Sorry, could not resist.)
    Yeah

    It would help if I had the DDL of the table (including Indexes and constraints) that I was working with
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2009
    Location
    Selangor, Malaysia
    Posts
    5

    Red face

    Thanks all for your reply, i cannot specify which table because this project is our product and the table name is always changing depend to client's database. Currently, we support database for DB2, MySql, SQL Server 2005, and now im working on SQL Server 2000.

    I already remove the stored proc from the db and put the sql query inside the Java code and it work. The only concern is the time. It getting slower and slower when moving to the last page. It took about 1.5 second to query for the last page that have 1million records!!

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    CREATE PROCEDURE dbo.UserPaging(
    	@sqlStr varchar(5000), 
    	@orderBy varchar(1000), 
    	@startRow int, 
    	@endRow int
    )
    AS
    	SET NOCOUNT ON
    
    	DECLARE @SQLSTRING varchar(5000)
    	DECLARE @fieldName varchar(1000)
    	DECLARE @tableName varchar(1000)
    
    	SET @fieldName = SUBSTRING(@sqlStr, 8, PATINDEX('%from%', @sqlStr) - 8)
    	SET @tableName = RIGHT(@sqlStr, PATINDEX('%from%', @sqlStr) + 6)
    
    	SET @SQLSTRING = N'WITH cte (' + @fieldName + '
             ,___RowNUmber___) AS
     (SELECT ' + @fieldName + ', ROW_NUMBER() OVER (ORDER BY ' + @orderBy + ') AS ___RowNUmber___ 
    ' + @tableName + ')
      SELECT ' + @fieldName + '
      FROM cte 
      WHERE ___RowNUmber___ BETWEEN ' + CAST(@startRow as varchar(12)) + ' AND ' + CAST(@endRow as varchar(12)) + ''
    
    --	PRINT '@fieldName = ' + @fieldName
    --	PRINT '@tableName = ' + @tableName
    --	PRINT @SQLSTRING
    	EXEC(@SQLSTRING)
    
    	SET NOCOUNT OFF
    	RETURN
    GO
    
    CREATE TABLE DaTable(
    	Id int	identity (1, 1) not null,
    	column1	char(10) NOT NULL,
    	CONSTRAINT PK_DaTable PRIMARY KEY (Id)
    )
    GO 
    
    CREATE FUNCTION dbo.GenerateNumList(
    	@MaxNum Int
    )
    RETURNS TABLE 
    AS
    RETURN 
    	WITH cte (num) AS
    	(SELECT 0
    		UNION ALL
    	SELECT num + 1
    	FROM cte
    	WHERE num < @MaxNum
    	)
    	SELECT num
    	FROM cte
    GO
    
    INSERT INTO DaTable(column1)
    SELECT 'bla' + CAST(num AS CHAR(10))
    FROM dbo.GenerateNumList(10)
    
    EXECUTE dbo.UserPaging 'SELECT Id, column1 FROM DaTable where Id > -1', 'Id', 7, 10
    GO
    
    /*WITH cte (Id, column1, ___RowNUmber___) AS
    (SELECT Id, column1
    		, ROW_NUMBER() OVER (ORDER BY column1) as ___RowNUmber___
    FROM DaTable
    )
    SELECT Id, column1
    FROM cte 
    WHERE ___RowNUmber___ BETWEEN 7 AND 10
    */
    
    DROP PROCEDURE dbo.UserPaging
    GO
    DROP TABLE DaTable
    GO
    DROP FUNCTION dbo.GenerateNumList
    GO
    It works, but it has some severe limitations:
    - vulnerability for SQL injection
    - vulnerability due to weak parsing logic of the SQL query (suppose you have a column that is called NomDeFromage (CheeseName) appearing in the SELECT. How would the resulting "paging SQL" look like?) At least alter the interface so you don't have to parse for the @fieldName and @tableName in the sproc. You already did it for the ORDER BY part.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is throwing me for a loop:
    Code:
    SET @fieldName = SUBSTRING(@sqlStr, 8, PATINDEX('%from%', @sqlStr) - 8)
    SET @tableName = SUBSTRING(@sqlStr, PATINDEX('%from%', @sqlStr), 4)
    It looks very fragile and hackish. Can you give an example of the input for the @sqlStr parameter.
    I really think your approach to this needs to be reconsidered.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Nov 2009
    Location
    Selangor, Malaysia
    Posts
    5
    Quote Originally Posted by blindman View Post
    This is throwing me for a loop:
    Code:
    SET @fieldName = SUBSTRING(@sqlStr, 8, PATINDEX('%from%', @sqlStr) - 8)
    SET @tableName = SUBSTRING(@sqlStr, PATINDEX('%from%', @sqlStr), 4)
    It looks very fragile and hackish. Can you give an example of the input for the @sqlStr parameter.
    I really think your approach to this needs to be reconsidered.
    Yeah i understand that bro. example of sql provided is "select user_id, name, email, age from users". From what i know, clients database field name could be named from reserved keyword. That is the problem. Let say if they have a field name "from". So, to query that table, "select user_id, name, from, email from users". That is the weakness of using my method.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    And it will also fail if they type double spaces between words, and who knows when else.
    This approach is doomed to failure. Reexamine your business requirements and come up with something more robust than parsing strings entered by users.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •