hi,

sorry if this may be a little too off topic, kinda need help....

 

i don't want to do dynamic SQL so im finding out ways to figure out dynamic sorting for the stored proc:

 

here's what i have:

 

ORDER BY
                            CASE WHEN @sort = 0 THEN
                                CASE WHEN @sortby = 'Date' THEN item_DATEPOST END
                                CASE WHEN @sortby =  'Price' THEN item_PRICE END
                                CASE WHEN @sortby =  'Itemname' THEN item_NAME END
                            ELSE
                                CASE WHEN @sortby =  'Date' THEN item_DATEPOST END
                                CASE WHEN @sortby =  'Price' THEN item_PRICE END
                                CASE WHEN @sortby =  'Itemname' THEN item_NAME END
                            END

 

well, my first lines were in just one CASE statement, although i found out that you cant join two or more different columns with different data types. so i resorted in making each CASE statements for each columns.

i also tried putting CAST or CONVERT to each column on their specific datatype, but still nothing worked.

 

help. thanks very much!

 

 

bmongtangco:

hi,

sorry if this may be a little too off topic, kinda need help....

 

i don't want to do dynamic SQL so im finding out ways to figure out dynamic sorting for the stored proc:

 

here's what i have:

 

ORDER BY
                            CASE WHEN @sort = 0 THEN
                                CASE WHEN @sortby = 'Date' THEN item_DATEPOST END
                                CASE WHEN @sortby =  'Price' THEN item_PRICE END
                                CASE WHEN @sortby =  'Itemname' THEN item_NAME END
                            ELSE
                                CASE WHEN @sortby =  'Date' THEN item_DATEPOST END
                                CASE WHEN @sortby =  'Price' THEN item_PRICE END
                                CASE WHEN @sortby =  'Itemname' THEN item_NAME END
                            END

 

well, my first lines were in just one CASE statement, although i found out that you cant join two or more different columns with different data types. so i resorted in making each CASE statements for each columns.

i also tried putting CAST or CONVERT to each column on their specific datatype, but still nothing worked.

help. thanks very much!

 

hi bmongski, the code you posted seems alright unless something above these lines caused the issue you are experiencing, I dont know. It can be:

1. The variable @sort contains wrong values and the script generated an error.
2. The columns you are using in the case belongs to more than 1 table. Better use aliasing always.

Take a look at this post:
.NET: Advanced Data Paging with Sorting In SqlServer 2005

 

hi, thanks for the reply...

 

hmm... the @sort is a smallint datatype. ive also tried removing it 

 

if im going to do dynamic SQL, can i either just EXEC('ORDER BY ' + @sort + ' ' + @sortby) within a non-dynamic SQL stored proc?

wow,

the lines are very much the same is mine, although i've placed the order by case inside the ROW_NUMBER.

 

ive tried putting the ORDER BY outside it.

 

unfortunately, my

SELECT ROW_NUMBER() OVER (ORDER BY i.item_ID) AS Rows

always wins the QUERY.... how do i prevent this? 

Server Error in '/' Application.

---

Conversion failed when converting character string to smalldatetime data type.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting character string to smalldatetime data type.

Source Error:

 

ORDER BY
                        CASE @sort
                            WHEN 0 THEN
                                CASE @sortby
                                    WHEN 'Date' THEN datepost
                                    WHEN 'Price' THEN price
                                    WHEN 'Itemname' THEN itemname
                                END
                            END
                            ASC,
                        CASE @sort
                            WHEN 1 THEN
                                CASE @sortby
                                    WHEN 'Date' THEN datepost
                                    WHEN 'Price' THEN price
                                    WHEN 'Itemname' THEN itemname
                                END
                            END
                            DESC;

 

also tried the CONVERT and CAST, still unlucky. 

 

also did this

 

ORDER BY
                        CASE @sort
                            WHEN 0 THEN
                                CASE @sortby WHEN 'Date' THEN datepost END
                                CASE @sortby WHEN 'Price' THEN price END
                                CASE @sortby WHEN 'Itemname' THEN itemname END
                            END
                            ASC,
                        CASE @sort
                            WHEN 1 THEN
                                CASE @sortby WHEN 'Date' THEN datepost END
                                CASE @sortby WHEN 'Price' THEN price END
                                CASE @sortby WHEN 'Itemname' THEN itemname END
                            END
                            DESC;

 

but im encountering more errors... 

TIP: When testing stored procedures you dont have to build and load your pages each time. Its better to have a "unit test" harness code above your stored procedures with some paramters and variables initialize already. This case you have isolated the issue into the script only and any error, you will know its is just within in this boundary.

The error you experience I think has nothing to do with those CASE and WHEN statements, I think it is either wrong datatype on your parameters and local variables which is hidden in your post. I advise you better check your table's column datatypesa and variables and do matching and testing.

Its hard to guess your issue at this point. You may post your whole stored procedure. 

like I've said in phissug... please see datatype precedence in BOL...

set @startRowIndex = (@PageIndex * @NumRows) + 1;

With queryEntries as (
                        SELECT ROW_NUMBER() OVER (ORDER BY i.forumThread_ID) AS Rows,
                                    i.forumCat_ID AS cat,
                                    i.forumSCat_ID AS scat,
                                    i.forumThread_ID AS thread,
                                    i.forumThread_SUBJ AS subj,
                                    tblUser.user_UNAME AS poster,
                                    i.forumThread_DATEPOST AS dateposted
                        FROM    (
                                    SELECT  DISTINCT tblForumThread.*
                                    FROM    tblForumThread,
                                            dbo.splitText(@text, @delimiter) ts
                                    WHERE   tblForumThread.forumThread_SUBJ LIKE ISNULL('%' + ts.theValue + '%', tblForumThread.forumThread_SUBJ)
                                    OR ISNULL(ts.theValue, tblForumThread.forumThread_SUBJ) LIKE '%' + tblForumThread.forumThread_SUBJ + '%'
                                    ) i INNER JOIN tblUser ON i.user_ID = tblUser.user_ID   
                        WHERE    
                                (i.forumThread_BLOCK_CHK = 0) AND
                                (tblUser.user_VALIDATION_CHK = 1) AND
                                (tblUser.user_BLOCK_CHK = 0) AND
                                (i.forumCat_ID = ISNULL(@cat, i.forumCat_ID)) AND
                                (i.forumSCat_ID = ISNULL(@scat, i.forumSCat_ID))
                        )

                    SELECT cat, scat, thread, subj, poster, dateposted
                    FROM  queryEntries WHERE Rows between @startRowIndex and @StartRowIndex + @NumRows - 1
                    ORDER BY
                        CASE @sorthow
                            WHEN 0 THEN
                                CASE @sortby
                                    WHEN 'Date' THEN dateposted
                                    WHEN 'Subj' THEN subj
                                    WHEN 'Poster' THEN poster
                                END
                            END
                            ASC,
                        CASE @sorthow
                            WHEN 1 THEN
                                CASE @sortby
                                    WHEN 'Date' THEN dateposted
                                    WHEN 'Subj' THEN subj
                                    WHEN 'Poster' THEN poster
                                END
                            END
                            DESC;

TIP: Check your .NET code how you passed and assign the parameters. I think you assign a wrong value to a parameter. Its like you declare a smalldatetime parameter on your procedure but you passed string on your .net code. no offense brother, but how many times would I suggest you use break points and step through debugging on your code

no offense taken bro.

 

i like it when you guys really push me to fix my codes. its how i maintain myself to the edge. ill try looking through my .net code then. since you guys didn't see any error, ill check on it right away!

 

thanks! 

 

ive also removed the WHEN 'Date' THEN dateposted from the stored proc, and it ran well. i really think its this "WHEN 'Date' THEN dateposted" that's causing all the bugs.

that's very strange behaviour, interesting, can you please put an Alia on your columns, lets try that. "Lets say, WHEN 'Date' THEN th.dateposted".

I tried executing this on the code I have written on my blog and it works perfectly fine. this is sorting by date also.

--execute stored product for testing
DECLARE @totalRows INT;
EXEC [usp_query_all_product]
        @sortColumn = 'LastUpdated',  -- SortOrder | LastUpdated | Price | AverageRating | ItemSold
        @sortDirection ='DESC',               -- ASC DESC
        @pageIndex= 1,
        @pageSize=5,
        @totalRows = @totalRows OUTPUT;
SELECT @totalRows;
 

very wierd indeed.

 

i executed it on my SQL and it works fine. when it comes to the webpage, it doesn't do asc and desc. 

@Rodel:Try adding the ProductName as your SORT parameter, the error will appear.

I've been burned with this behavior before. Although I haven't seen your .net code, I don't think that is the problem. Let's take the data precedence rule of Sql Server(unless noted, the rule is regardless of version)

udt -user defined type (highest) (sql 2005)
sql_variant
xml (sql 2005)
datetime
smalldatetime
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar
nchar
varchar
char
varbinary
binary (lowest)

Let's say your sort columns are the following data types:

datetime
int
decimal
bit
varchar
nvarchar
char

If you specify, either datetime, decimal, int, bit column types as your sort order, there will have no problem. However, once you specificy either (n)varchar/chars columns you will get the error conversion type error. The reason for this although you were able to specify the column to sort, the CASE statement's return value is determined by data type precedence. So it will try to implicitly type cast the varchar to datetime, hence the error.

You can try the following simple test:

declare @p datetime
declare @v varchar(10)
declare @i int

select @i = 0, @p=getdate(), @v='text' -- change @i = 1 and there will be no error

select case @i when 1 then @p else @v end

What's the fix then? Explicitly cast the columns to varchars, but then again you'll not get the correct sort order(ie: cast int->varchar will result to sorting order of 1,10,2 not 1,2,10). I suggest  in this case, use dynamic T-Sql as this is perfect for the situation. I don't even think you'll get the performance benefit of static sql as it will most of the time rely on index scan or worse table scan.

 

 

wow, and i thought i was the problem.

 

yeah, im burnt too. but im used to it. i let my hands do the programming now, i dont even think. well, ill just move on and fix on this sometime. thanks guys for all your help! ive been very spoiled in this forums and i really appreciate all your help! 

master bonski is correct, as you can see the query below, the ASC and DESC sort column have multiple specified columns with different data types.

 
 

                        ORDER BY
                        CASE @sorthow
                            WHEN 0 THEN
                                CASE @sortby
                                    WHEN 'Date' THEN CAST(dateposted as smalldatetime)
                                    WHEN 'Subj' THEN CAST(subj as varchar(200))
                                    WHEN 'Poster' THEN CAST(poster as varchar(15))
                                END
                            END
                            ASC,
                        CASE @sorthow
                            WHEN 1 THEN
                                CASE @sortby
                                    WHEN 'Date' THEN CAST(dateposted as smalldatetime)
                                    WHEN 'Subj' THEN CAST(subj as varchar(200))
                                    WHEN 'Poster' THEN CAST(poster as varchar(15))
                                END
                            END
                            DESC;
                        

 

another solution for this is to separate each inner case within the ASC and DESC.. the query would then look like this..

SELECT     cat
    , scat
    , thread
    , subj
    , poster
    , dateposted
FROM      queryEntries
WHERE     Rows between @startRowIndex and @StartRowIndex + @NumRows - 1
ORDER BY
        CASE WHEN @sorthow = 0 AND @sortby = 'Date' THEN
            CAST(dateposted as smalldatetime)
    END ASC
    , CASE WHEN @sorthow = 0 AND @sortby = 'Subj' THEN
            CAST(subj as varchar(200))
    END ASC
    , CASE WHEN @sorthow = 0 AND @sortby = 'Poster' THEN
            CAST(poster as varchar(15))
    END ASC
    , CASE WHEN @sorthow = 1 AND @sortby = 'Date' THEN
            CAST(dateposted as smalldatetime)
    END DESC
    , CASE WHEN @sorthow = 1 AND @sortby = 'Subj' THEN
            CAST(subj as varchar(200))
    END DESC
    , CASE WHEN @sorthow = 1 AND @sortby = 'Poster' THEN
            CAST(poster as varchar(15))
    END DESC

 

 

@rhamille, hehe it'll get really ugly once the criteria increases..

true, but based on his current requirement, i think its tolerable.. not elegant though hehehe

 

pwede makisawsaw ng thread? para di sayang...sana ok lang din sa TS san po pwede download northwind db for sql 2005? lagi ko kasi nakikia for 2k lang.. kahit scripts.

yung s sql server 2000, back up mo yun

tapos restore mo s sql server 2005.

me backward compatability ang sql server 2005, so any database created in 2000 pwede mo irestore s 2005

wala po kasing sql 2k sa machine ko sir eh.. available lang is 05 na..

what should i do?

may tao naman sa phissug a hehehe!

again bro

may backward capability ang 2005 na maka-open ng 2000 na DB.

 

download mo Northwind then run mo yung script para mag-create ng Northwind DB, compatible naman sya sa 2005.

CryptoKnight:

download mo Northwind then run mo yung script para mag-create ng Northwind DB, compatible naman sya sa 2005.

 

Galing talaga ni CryptoKnight.. hehe

Anyways, in addition to previous discussions above I figured I'd just mentioned that although there are issues with dynamic SQL like errors in the query syntax not immediately apparent when executed or parsed, it still has it's place especially then the number of columns to sort is dynamic and more than one. If you decide to use it however, please do use sp_executesql instead of just EXEC. The former is the paramaterized query implementation in TSQL and would prevent SQL injection. Note that stored procedures do not prevent injection if you still do concatenation inside it. Finally, dynamic queries would also minimize performance overhead of unnecessary (or less necessary) casting and conversions and ofcourse the possibility (note: possibility) of shorter and more maintainable code. :)

ok na mga idol ^_^