January 10, 2008 at 1:47 pm
here's my issue. i have a stored proc that returns a result set paged based on the params, and the sort expression and sort direction. i am using toe ROW_NUMBER feature of mssql 2005. in the ROW_NUMBER() over part of the statement, i have a case statement set up to order the results based on a @SortExpression and @SortDirection param, when the sortexpression param tells the statement to sort based on a column of type varchar, i get the following error
Conversion failed when converting the varchar value 'SomeStringValue' to data type int
any help would be appreciated
here is the code -
select param1, param2, Param3, Param4, Param5, Param6
from
( select param1, param2, Param3, Param4, Param5, Param6
ROW_NUMBER()
over(
order by
case @SortDirection
when 'DESC' then
case @SortExpression
when 'Param1' then Param1
when 'Param2' then Param2
when 'Param3' then Param3
when 'Param4' then Param4
when 'Param5' then Param5
when 'Param6' then Param6
end
end desc,
case @SortDirection
when 'ASC' then
case @SortExpression
when 'Param1' then Param1
when 'Param2' then Param2
when 'Param3' then Param3
when 'Param4' then Param4
when 'Param5' then Param5
when 'Param6' then Param6
end
end asc
) as RowNum
from
[Table1]
where
/* conditions go here */
) as Table1Info
where RowNum >= @StartIndex
and RowNum < @StartIndex + @numRows
like i said, when @SortExpression is an int, it works fine, when it is a varchar , it fails
do i need to seperate my case statements based on the types of columns???
is that whats doing it??
anu help would be appreciated.
thanks,
alex
January 10, 2008 at 4:59 pm
ok, i figured it out. i guess you do need to seperate the fields in the case statements based on their datatype.
here is an article
http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply