What in ways could I optimize this SP that uses dynamic sql?
The tables will contain several million rows and I need to return only 20
rows at a time.
-------
CREATE PROCEDURE dbo.usp_paged_recordset
@Fieldnames VARCHAR(2000),
@TableName VARCHAR(500),
@PrimaryKey VARCHAR(100),
@SortField VARCHAR(255),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(500) = NULL
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
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))
SET ROWCOUNT @PageSize
IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN
-- return the number of pages available
EXEC(
'SELECT (COUNT('[EMAIL PROTECTED]') - 1)/' + @SizeString
+ ' + 1 AS PageCount FROM ' + @TableName)
-- return a specific number of records using a page number.
EXEC(
'SELECT '[EMAIL PROTECTED]' 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
)
END
ELSE
BEGIN
-- return the number of pages available
EXEC('SELECT (COUNT('[EMAIL PROTECTED]') - 1)/' + @SizeString
+ ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)
-- return a specific number of records using a page number.
EXEC(
'SELECT '[EMAIL PROTECTED]' 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
)
END
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
RETURN 0
Thanks,
Dave
HTC Disclaimer: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
