Solved

Dynamic Order Clause

Posted on 2008-06-25
8
448 Views
Last Modified: 2012-05-05
I am trying to set up a stored procedure based on what the user selects, will sort the result set by the following:
Low to high
High to low
Name

And this is what I have


DECLARE @bigCategoryId INT
DECLARE @intPageLen INT
DECLARE @intPageIndex INT
DECLARE @strSortOrder VARCHAR(10)
DECLARE @intStartRowIndex INT
   
    SET @strSortOrder = 'nme'
    SET @bigCategoryId = 50
    SET @intpagelen = 16
    SET @intPageIndex = 1
    SET @intStartRowIndex = ((@intPageIndex-1) * @intPageLen) + 1;
      
      --Pull back the product listing
    WITH ProductListing AS (
        SELECT ROW_NUMBER() OVER (ORDER BY sic.id ASC) AS row, sic.id,
                   rtrim(sic.invtid) AS invtid,prd.descr,
            dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) AS price
            FROM siteitemcategory sic WITH (NOLOCK)
            INNER JOIN products prd ON prd.invtid = sic.invtid
            WHERE sitecategories_id
            IN (SELECT id FROM siteCategories WHERE menu_parent = @bigCategoryId OR id = @bigCategoryId)
    )
   
    SELECT row,id,invtid,descr, price
            FROM productlisting
            WHERE Row
                        BETWEEN @intStartRowIndex AND @intStartRowIndex+@intPageLen-1
ORDER BY CASE WHEN @strSortOrder = 'low' THEN price
              WHEN @strSortOrder = 'high' THEN price
              WHEN @strSortOrder = 'nme' THEN descr
              ELSE descr
              END


However I am getting the following message all the time
Msg 235, Level 16, State 0, Line 15
Cannot convert a char value to money. The char value has incorrect syntax.

I know right now the desc is at the end of the case statement, but I was hoping to make this part of the case statement result.

I really am trying to avoid writing dynamic sql for this or putting a if clause in place based on the value of the sort type. Any help would be appreciated. Thanks.
0
Comment
Question by:JDEE8297
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:patrikt
ID: 21864613
The CASE in order is not good. For this I suggest to put your CASE as new column into ProductListing (say named Sort). Than just ORDER BY Sort.
Secondly you have to conver all columns tosame type so Sort column will be strict typed (nvarchar for ex.). Be carefull that you will sort on character data, not numeric so order can change depending on format.

Patrik
0
 

Author Comment

by:JDEE8297
ID: 21864636
Sorry, you lost me with putting a new column into productlisting and do the sort on that. Not exactly sure how you would do that.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21864699
I'll try, you will test :)
DECLARE @bigCategoryId INT
DECLARE @intPageLen INT
DECLARE @intPageIndex INT
DECLARE @strSortOrder VARCHAR(10)
DECLARE @intStartRowIndex INT
    
    SET @strSortOrder = 'nme'
    SET @bigCategoryId = 50 
    SET @intpagelen = 16
    SET @intPageIndex = 1
    SET @intStartRowIndex = ((@intPageIndex-1) * @intPageLen) + 1;
      
      --Pull back the product listing
    WITH ProductListing AS (
        SELECT ROW_NUMBER() OVER (ORDER BY sic.id ASC) AS row, sic.id,
                   rtrim(sic.invtid) AS invtid,prd.descr, 
            dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) AS price,
			CASE WHEN @strSortOrder = 'low' THEN cast(price as nvarchar(100))  --other convert should be there
              WHEN @strSortOrder = 'high' THEN cast(price as nvarchar(100))
              WHEN @strSortOrder = 'nme' THEN descr 
              ELSE descr
              END as Sort 
            FROM siteitemcategory sic WITH (NOLOCK)
            INNER JOIN products prd ON prd.invtid = sic.invtid
            WHERE sitecategories_id 
            IN (SELECT id FROM siteCategories WHERE menu_parent = @bigCategoryId OR id = @bigCategoryId)
    )
    
    SELECT row,id,invtid,descr, price 
            FROM productlisting 
            WHERE Row 
                        BETWEEN @intStartRowIndex AND @intStartRowIndex+@intPageLen-1
	ORDER BY Sort

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 12

Expert Comment

by:patrikt
ID: 21864731
I just spoted big problem in your solution. You are doing pageing based on sic.id and then you sort. It will sort only the page content and it is probably not your desired behavior. When doing pageing you should sort first and after that select one page.

Patrik
0
 

Author Comment

by:JDEE8297
ID: 21864882
I am starting to think this is not going to work the way I want it to, I tried what you sent over.

Since I alias the price column I had to change it to the following lines
            CASE WHEN @strSortOrder = 'low' THEN cast(dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) as nvarchar(100))  --other convert should be there
              WHEN @strSortOrder = 'high' THEN cast(dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) as nvarchar(100))

Once that was done I was able to get results, however, still doesn't matter whether I put in low or high or nme...since on this line

  SELECT row,id,invtid,descr, price
            FROM productlisting
            WHERE Row
                        BETWEEN @intStartRowIndex AND @intStartRowIndex+@intPageLen-1
      ORDER BY Sort

I need to tell it if it is "low" then the order clause would be ORDER BY Sort desc or ORDER BY Sort ASC if I want it by high prices.

to the last remark you made where you spotted a big problem in solution. I was thinking of using this

    WITH ProductListing AS (
        SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY sic.id ASC) AS row, sic.id,
                   rtrim(sic.invtid) AS invtid,prd.descr,
            dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) AS price,
                  CASE WHEN @strSortOrder = 'low' THEN cast(dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) as nvarchar(100))  --other convert should be there
              WHEN @strSortOrder = 'high' THEN cast(dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) as nvarchar(100))
              WHEN @strSortOrder = 'nme' THEN descr
              ELSE descr
              END as Sort
            FROM siteitemcategory sic WITH (NOLOCK)
            INNER JOIN products prd ON prd.invtid = sic.invtid
            WHERE sitecategories_id
            IN (SELECT id FROM siteCategories WHERE menu_parent = @bigCategoryId OR id = @bigCategoryId)
            ORDER BY SORT
    )

However, I think I will still send up my same problem, may be I wrong, what do you think.
      DECLARE @bigCategoryId INT
DECLARE @intPageLen INT
DECLARE @intPageIndex INT
DECLARE @strSortOrder VARCHAR(10)
DECLARE @intStartRowIndex INT
    
    SET @strSortOrder = 'low'
    SET @bigCategoryId = 50 
    SET @intpagelen = 16
    SET @intPageIndex = 1
    SET @intStartRowIndex = ((@intPageIndex-1) * @intPageLen) + 1;
      
      --Pull back the product listing
    WITH ProductListing AS (
        SELECT ROW_NUMBER() OVER (ORDER BY sic.id ASC) AS row, sic.id,
                   rtrim(sic.invtid) AS invtid,prd.descr, 
            dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) AS price,
			CASE WHEN @strSortOrder = 'low' THEN cast(dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) as nvarchar(100))  --other convert should be there
              WHEN @strSortOrder = 'high' THEN cast(dbo.func_priceproduct_getprice_itemonly(prd.invtid,1) as nvarchar(100))
              WHEN @strSortOrder = 'nme' THEN descr 
              ELSE descr
              END as Sort 
            FROM siteitemcategory sic WITH (NOLOCK)
            INNER JOIN products prd ON prd.invtid = sic.invtid
            WHERE sitecategories_id 
            IN (SELECT id FROM siteCategories WHERE menu_parent = @bigCategoryId OR id = @bigCategoryId)
    )
    
    SELECT row,id,invtid,descr, price 
            FROM productlisting 
            WHERE Row 
                        BETWEEN @intStartRowIndex AND @intStartRowIndex+@intPageLen-1
	ORDER BY Sort 

Open in new window

0
 
LVL 12

Accepted Solution

by:
patrikt earned 125 total points
ID: 21865027
If you want to fix paging problem you have to put the CASE... inside OVER (ORDER BY ...).
If you need changing ASC / DESC only dynamic SQL will help you.

Patrik
0
 

Author Comment

by:JDEE8297
ID: 21865037
figured it was going to be that. Thank you for your help.
0
 

Author Closing Comment

by:JDEE8297
ID: 31470516
thank you for your help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query - which index being used? 2 52
SQL Server Degrading on Write 13 67
SQL Agent Timeout 5 57
Analysis of table use 7 47
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now