October 13, 2011 at 7:54 am
thanks. I will try this too. Only thing is my procedure will be very log.
How about using style 120 .Any pros/cons?
October 14, 2011 at 8:19 am
Hello,
I think you must start trying to know the reason for this error message, "cannot read next data row for the data set.conversion failed when converting character string to smalldatetime data type".
I tried a sentence like yours and it failed with a similar mesage in SSMS. If you tries to run it,
SELECT starttime, name, productno
from table testA
order by CASE @SortColumn
WHEN 'starttime' THEN starttime
WHEN 'productNo' THEN productno
ELSE name
END
when @SortColumn has value 'starttime' it runs fine, but for another value it fails (incidentally, you didn't mention in your message when it failed or when it ran well).
The reason for that error?, I suppose things succeed in this way:
- The DB Engine tries to know the sorting field type.
- The sorting field, in the first WHEN clause, is starttime, so the DB engine accept that the sorting field type is datetime for the whole instruction.
- When the parameter value is other than 'starttime' the DB engine tries to convert productNo or name to datetime, and that conversion fails.
Next, how to solve it?. Your solution is to cast all sorting columns to varchar, and it is a correct solution, but I prefer the Lynn's hint. Or, if your SELECT is very long or you have too options for ORDER BY, you can code your SELECT as dynamic SQL and then you can code anything you want within the ORDER BY clause, something like
DECLARE @SQLstream AS VARCHAR(8000)
SET @SQLstream = 'SELECT starttime, name, productno from table testA order by '
SET @SQLstream = @SQLstream + CASE @SortColumn
WHEN 'starttime' THEN 'starttime'
WHEN 'productNo' THEN 'productno'
ELSE 'name'
END
EXECUTE (@SQLstream)
Regards,
Francesc
October 14, 2011 at 9:16 am
vick12 (10/13/2011)
The format of my procedure is as follow: if not converting to varchar in orderby then I ma getting error in ssrs 2005create stored procedure testsorting
( @sort varchar(60)
)
as
BEGIN
SELECT starttime,
name,
productno,
from table testA
orderby
CASE
WHEN @SortColumn = 'starttime' THEN (Convert(varchar(20),starttime,102)+ Convert(varchar(20),startime,108))
WHEN @SortColumn = 'productNo' THEN CONVERT(VARCHAR(10), productno)
WHEN @SortColumn = 'Name' THEN CONVERT(VARCHAR(60), name)
END
END
You're not limited to one expression for the order by clause. You can solve this by creating two sort expressions.
SELECT starttime
, name
, productno
FROM TABLE testA
ORDER BY
CASE -- character expressions
WHEN @SortColumn = 'starttime' THEN ''
WHEN @SortColumn = 'productNo' THEN CONVERT(VARCHAR(10), productno)
WHEN @SortColumn = 'Name' THEN CONVERT(VARCHAR(60), name)
END
,CASE -- datetime expressions
WHEN @SortColumn = 'starttime' THEN StartTime
ELSE NULL
END
If the report is to be ordered by start time, then the first expression will evaluate to the same value for all records, and it will use the second expression to set the final order.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 18, 2011 at 8:25 pm
Thank you all for your valuable replies and solution. They are very helpful.:-) I am glad that you were able to understand the problem I was having as I was finding it quite strange and was not sure why it is not working when using SSRS 2005 and I tried many ways to solve it but was not successful so thought to convert all to varchar instead. But now I got why it was happening. Thanks a lot again.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply