This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes Stored Proc w/dynamic sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Stored Proc w/dynamic sql" Watch "Stored Proc w/dynamic sql" New topic
Author

Stored Proc w/dynamic sql

Dave Bosky
Ranch Hand

Joined: Dec 16, 2003
Posts: 72
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stored Proc w/dynamic sql