• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Stored Proc w/dynamic sql

 
Dave Bosky
Ranch Hand
Posts: 72
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
------------------
CallableStatement cstmt = con.prepareCall("{call dbo.rc(?,?,?,?,?,?,?)}");
cstmt.setString(1,Fieldnames);
cstmt.setString(2,TableName);
cstmt.setString(3,PrimaryKey);
cstmt.setString(4,SortField);
cstmt.setInt(5,PageSize);
cstmt.setInt(6,currentPage);
cstmt.setString(7,QueryFilter);
cstmt.execute();
-------
CREATE PROCEDURE dbo.rc
@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 + ' 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('+@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 + ' 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic