"compile"). The query engine will basically have to parse out the
command you are creating every time it runs. Also, you can be at
greater risk for SQL-injection, etc. However, it'll generally work
fine, and only in the most extreme cases will the speed issue be a
significant factor.
-----Original Message-----
From: Bosky, Dave [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 10, 2004 14:53
To: SQL
Subject: MS SQL Stored Proc performance question?
Is it a good to write a store procedure using dynamic sql?
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]
