• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Stored Proc w/dynamic sql

 
Ranch Hand
Posts: 72
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
So you made a portal in time and started grabbing people. This tiny ad thinks that's rude:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic