Dynamic Order By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Order By

Author

Topic

KHeon
Posting Yak Master

135 Posts

Posted - 2002-07-10 : 11:37:47

Hello!

I have a dynamic sql statement within one of my stored procedures (as detailed below):



CREATE PROCEDURE dbo.usp_FetchMediaByPage
(
  @AccountNo INT,
  @Page INT = 1,
  @PageSize INT = 25,
  @Sort VARCHAR(25) = 'MEDIAID',
  @SortOrder VARCHAR(4) = 'ASC'
)

AS

  SET NOCOUNT ON

  DECLARE @StartPos INT
  DECLARE @MaxNumRecords INT
  DECLARE @SQL NVARCHAR(500)

  -- Sets the first record to begin with.
  SET @StartPos = (@Page - 1) * @PageSize + 1
  SET @MaxNumRecords = (@Page * @PageSize)

  -- Return total record count (first recordset).
  SELECT COUNT(*) AS RecordCount
  FROM uvw_OffsiteMedia
  WHERE AccountNo = @AccountNo

  -- Create temp table.
  CREATE TABLE #mediaTable (
    RowNum INT IDENTITY(1,1) NOT NULL,
    MediaInfoID INT NOT NULL
  )

  SET @SQL = N'SET ROWCOUNT ' + CAST(@MaxNumRecords AS NVARCHAR(20))
  SET @SQL = @SQL + N' INSERT INTO #mediaTable (MediaInfoID)'
  SET @SQL = @SQL + N' SELECT MediaInfoID'
  SET @SQL = @SQL + N' FROM uvw_OffsiteMedia'
  SET @SQL = @SQL + N' WHERE AccountNo = ' + CAST(@AccountNo AS NVARCHAR(10))
  SET @SQL = @SQL + N' ORDER BY ' + CAST(@Sort AS NVARCHAR(25)) + ' ' + CAST(@SortOrder AS NVARCHAR(4))

  EXECUTE sp_executesql @SQL

  -- Return single page of records (second recordset).
  SELECT mi.MediaInfoID, 
       mi.MediaID, 
       mi.CreateDate, 
       Recalled = CASE WHEN mi.RecallDate IS NULL THEN 'N' 
                       ELSE 'Y' END, 
       mi.ReturnDate, 
       mi.LastUpdated 
  FROM #mediaTable mt, 
       uvw_OffsiteMedia mi 
  WHERE mt.RowNum BETWEEN @StartPos AND (@StartPos + @PageSize) 
  AND mt.MediaInfoID = mi.MediaInfoID
  OPTION (MERGE JOIN)

  -- Clean up
  DROP TABLE #mediaTable

  SET NOCOUNT OFF

Sorting isn't working properly.

The MediaID field is a varchar field, which could have any number of characters and/or numbers up to 30 chars in length. Problem is when I pass in a sort of MEDIAID ad a sort order of DESC the XP type will not sort descending properly.

I don't think I'm explaining this properly but what I need is for a way to suck a page of data into a temp table, sorted either by one of a select list of columns -- one varchar(30), the rest are smalldatetime fields.

Any suggestions?

Also, I tried to recreate the sorting scripts to be dynamic using the article found at [url]http://www.sqlteam.com/ItemPrint.asp?ItemID=2209[url] but I'm getting type diff errors (failing on the date fields).

EDIT: Formatting was off.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Edited by - KHeon on 07/10/2002 11:39:41

MakeYourDaddyProud

184 Posts

Posted - 2002-07-10 : 11:41:38

Is the reason you are using dyn sql is because the order by changes? It that is the case this need not be a dyn sql workout for you. With use of CASE it can be avoided. More info please...

Daniel Small MIAP
www.danielsmall.com IT Factoring

KHeon
Posting Yak Master

135 Posts

Posted - 2002-07-10 : 11:46:15

Yes, the reasons is because I have to have dynamic sort and sort orders. I have tried to use the CASE to do this but everything fails once I start adding in the smalldatetime columns (ie: it works with MediaID, but fails for the rest). I get the following:


Syntax error converting character string to smalldatetime data type.

Thanks for the reply.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Edited by - KHeon on 07/10/2002 11:46:44

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-10 : 12:23:33

it's been brought up here before that items in ORDER BY segments when involved with CASE constructs, ALL need to be of the same datatype.....

do a search on CASE, ORDER BY, TYPE....some helpful topics should emerge....I know I've seen some topics fairly recently on this.....

KHeon
Posting Yak Master

135 Posts

Posted - 2002-07-10 : 15:51:46

Well I figured out how to fix the problem, now I'm hoping someone can explain *why* it happened. My understanding of how joins and such work can't figure out the issue:

The only thing I had to add to the stored procedure displayed above is an ORDER BY RowNum statement to the final, resultset returning SQL join. Here is the full proc:


ALTER   PROCEDURE dbo.usp_FetchMediaByPage
(
  @AccountNo INT,
  @Page INT = 1,
  @PageSize INT = 25,
  @Sort VARCHAR(25) = 'MEDIAID',
  @SortOrder VARCHAR(4) = 'ASC'
)

AS

  SET NOCOUNT ON

  DECLARE @StartPos INT
  DECLARE @MaxNumRecords INT
  DECLARE @SQL NVARCHAR(500)

  -- Sets the first record to begin with.
  SET @StartPos = (@Page - 1) * @PageSize + 1
  SET @MaxNumRecords = (@Page * @PageSize)

  -- Return total record count (first recordset).
  SELECT COUNT(*) AS RecordCount
  FROM uvw_OffsiteMedia
  WHERE AccountNo = @AccountNo

  -- Create temp table.
  CREATE TABLE #mediaTable (
    RowNum INT IDENTITY(1,1) NOT NULL,
    MediaInfoID INT NOT NULL
  )

  SET @SQL = N'SET ROWCOUNT ' + CAST(@MaxNumRecords AS NVARCHAR(20))
  SET @SQL = @SQL + N' INSERT INTO #mediaTable (MediaInfoID)'
  SET @SQL = @SQL + N' SELECT MediaInfoID'
  SET @SQL = @SQL + N' FROM uvw_OffsiteMedia'
  SET @SQL = @SQL + N' WHERE AccountNo = ' + CAST(@AccountNo AS NVARCHAR(10))
  SET @SQL = @SQL + N' ORDER BY ' + CAST(@Sort AS NVARCHAR(25)) + ' ' + CAST(@SortOrder AS NVARCHAR(4))

  EXECUTE sp_executesql @SQL

  -- Return single page of records (second recordset).
  SELECT mi.MediaInfoID, 
       mi.MediaID, 
       mi.CreateDate, 
       Recalled = CASE WHEN mi.RecallDate IS NULL THEN 'N' 
                       ELSE 'Y' END, 
       mi.ReturnDate, 
       mi.LastUpdated 
  FROM #mediaTable mt, 
       uvw_OffsiteMedia mi 
  WHERE mt.RowNum BETWEEN @StartPos AND (@StartPos + @PageSize) 
  AND mt.MediaInfoID = mi.MediaInfoID
  ORDER BY RowNum
  OPTION (MERGE JOIN)

  -- Clean up
  DROP TABLE #mediaTable

  SET NOCOUNT OFF

The results in the temp table were ordered as I'd expect.

It wasn't until they were joined back with the full table for the results I return to the client that the ordering fell apart.

Once I added the ORDER BY RowNum everything displayed properly.

Any logical explanation oh SQL Gurus?

Thanks!

OFF TOPIC: Why is Snitz truncating my sentences after I use a code block?

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources