Help me convert this stored proc to do a join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help me convert this stored proc to do a join

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-05-04 : 05:02:48

Hello,

I am using this sql script to achieve paging functionality for a site. IT works great for the most part, except when I need to return a result set that includes other tables.

How can I add another parameter to this to allow joined table to be a paramter? I'd like to pass in the join table name and columns to join on, or a custom select statement I guess?


CREATE PROCEDURE GetSortedPage(  
  @TableName VARCHAR(50),  
  @PrimaryKey VARCHAR(25),  
  @SortField VARCHAR(100),  
  @PageSize INT,  
  @PageIndex INT = 1,  
  @QueryFilter VARCHAR(100) = NULL  
) AS  
SET NOCOUNT ON  
  
DECLARE @SizeString AS VARCHAR(5)  
DECLARE @PrevString AS VARCHAR(5)  
  
SET @SizeString = CONVERT(VARCHAR, @PageSize)  
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))  
  
IF @QueryFilter IS NULL OR @QueryFilter = ''  
BEGIN  
  
  EXEC(  
  'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN  
    (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN  
      (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')  
    ORDER BY ' + @SortField + ')  
  ORDER BY ' + @SortField  
  )  
  EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)  
  
END  
ELSE  
BEGIN  
  
  EXEC(  
  'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN  
    (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN  
      (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')  
    ORDER BY ' + @SortField + ')  
  ORDER BY ' + @SortField  
  )  
  EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)  
  
END  
  
RETURN 0

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-04 : 05:20:03

Why everything is dynamic here? You are not only losing performance but also increasing chances of security threats.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-04 : 05:50:37

Read this
www.sommarskog.se/Dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-05-04 : 14:12:10

I'm doing this all dynamic because... well, I'm lazy. I'd like to reusing this one function for all my ajax pagination needs. Its so easy!

I guess I could rewrite this to be specific for my table, but I didn't realize it I'd take a performance and security hit......

BTW that link didn't work.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-04 : 14:56:00

Are you using SQL 2000 or 2005 ? if you are using SQL 2005, paging is very, very easy and can be done much more efficient than what you are trying to do. see:

http://www.sqlteam.com/item.asp?ItemID=26455

- Jeff
http://weblogs.sqlteam.com/JeffS

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-05-06 : 15:17:15

Hey Jeff, thanks for the advice. I'll take advantage of SQL 2005's new paging stuff for sure!

I'm getting an error when I run this:

WITH PagedPhotos AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY dateUploaded DESC) AS RowNumber
FROM wisetopic_image
)
SELECT RowNumber, *
FROM wisetopic_image
WHERE RowNumber BETWEEN 1 AND 20
ORDER BY RowNumber ASC;

The error Is :

Invalid column name 'RowNumber'.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-06 : 16:07:23

WITH PagedPhotos AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY dateUploaded DESC) AS RowNumber
FROM wisetopic_image
)
SELECT RowNumber, *
FROM ~~wisetopic_image~~ PagedPhotos
WHERE RowNumber BETWEEN 1 AND 20
ORDER BY RowNumber ASC;

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources