performance using SP w/dynamic sql?

Question

performance using SP w/dynamic sql?

dbosky

Grasshopper

Points: 21
More actions
February 10, 2004 at 2:51 pm

#84155

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.
Help? It seems to be working fine and fast but everyone tells me that I lose all the performance benefits of using stored procedures when I use dynamic sql.
-------
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('+@PrimaryKey+') - 1)/' + @SizeString
+ ' + 1 AS PageCount FROM ' + @TableName)
-- return a specific number of records using a page number.
  EXEC(
'SELECT '+@Fieldnames+' FROM ' + @TableName + '
WHERE ' + @primarykey-2 + ' IN(
SELECT TOP ' + @SizeString + ' ' +
@primarykey-2 + ' FROM ' + @TableName + ' WHERE ' + @primarykey-2 + ' NOT IN
(SELECT TOP ' + @PrevString
+ ' ' + @primarykey-2 + ' FROM ' + @TableName + ' ORDER BY ' + @SortField
+ ')
ORDER BY ' + @SortField + ')
  ORDER BY ' + @SortField
&nbsp
END
ELSE
BEGIN
-- return the number of pages available
  EXEC('SELECT (COUNT('+@PrimaryKey+') - 1)/' + @SizeString
+ ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)
-- return a specific number of records using a page number.
  EXEC(
  'SELECT '+@Fieldnames+' FROM ' + @TableName + ' WHERE ' +
@primarykey-2 + ' IN
(SELECT TOP ' + @SizeString + ' ' + @primarykey-2 + '
FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @primarykey-2 +
'
NOT IN
  (SELECT TOP ' + @PrevString + ' ' + @primarykey-2 +
' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' +
@SortField
+ ')
ORDER BY ' + @SortField + ')
  ORDER BY ' + @SortField
&nbsp
END
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
RETURN 0
Thanks,
Dave

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

No writing Dynamic SQL opens many variables for exploitation. You have to give extra rights to the underlying data tables which is a security risk. However all said you sometimes cannot get around doing this and so you are stuck unless you can find another way. I suggest writing covering queries for all circumstances but again a lot of possibilites may make impractical. If fast then I don't see much without getting more specifics on the input that can occurr with the code.