[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Ordering by a Date in Stored Procedure

richard

3/26/2007 10:45:00 AM

I am trying to Order results from a stored procedure depending on the
columns that the user clicks on the DataGrid.

The user end works fine, but I cannot get the sorting correct in my
Stored Procedure.

At the momnent I use a variable called @SortBy and then use this code
in my Stored Procedure:

ORDER BY CASE @SortBy
WHEN 'ApplicantID' THEN CONVERT(varchar(50), ApplicantID)
WHEN 'LastName' THEN CONVERT(varchar(50), LastName)
WHEN 'MaximumPrice' THEN CONVERT(varchar(50), MaximumPrice)
WHEN 'RegistrationDate' THEN CONVERT(varchar(50), RegistrationDate)
WHEN 'Status' THEN CONVERT(varchar(50), Status)
END

It works fine for string fields, but not for numbers or dates.

Can any one help please??
8 Answers

Uri Dimant

3/26/2007 10:58:00 AM

0

Hi
create table #t (c1 int,c2 char(1),c3 datetime)

insert into #t values (10,'a','20070101')
insert into #t values (2,'b','20070201')
insert into #t values (300,'d','20070103')
insert into #t values (9,'c','20060103')


declare @sortby varchar(10)
set @sortby='c1'

select * from #t order by
case when @sortby='c1' then c1 end,
case when @sortby='c2' then c2 end,
case when @sortby='c3' then c3 end



<richard@ultraitsolutions.com> wrote in message
news:1174905893.930284.8090@y80g2000hsf.googlegroups.com...
>I am trying to Order results from a stored procedure depending on the
> columns that the user clicks on the DataGrid.
>
> The user end works fine, but I cannot get the sorting correct in my
> Stored Procedure.
>
> At the momnent I use a variable called @SortBy and then use this code
> in my Stored Procedure:
>
> ORDER BY CASE @SortBy
> WHEN 'ApplicantID' THEN CONVERT(varchar(50), ApplicantID)
> WHEN 'LastName' THEN CONVERT(varchar(50), LastName)
> WHEN 'MaximumPrice' THEN CONVERT(varchar(50), MaximumPrice)
> WHEN 'RegistrationDate' THEN CONVERT(varchar(50), RegistrationDate)
> WHEN 'Status' THEN CONVERT(varchar(50), Status)
> END
>
> It works fine for string fields, but not for numbers or dates.
>
> Can any one help please??
>


Rahul

3/26/2007 10:58:00 AM

0

On Mar 26, 3:44 pm, rich...@ultraitsolutions.com wrote:
> I am trying to Order results from a stored procedure depending on the
> columns that the user clicks on the DataGrid.
>
> The user end works fine, but I cannot get the sorting correct in my
> Stored Procedure.
>
> At the momnent I use a variable called @SortBy and then use this code
> in my Stored Procedure:
>
> ORDER BY CASE @SortBy
> WHEN 'ApplicantID' THEN CONVERT(varchar(50), ApplicantID)
> WHEN 'LastName' THEN CONVERT(varchar(50), LastName)
> WHEN 'MaximumPrice' THEN CONVERT(varchar(50), MaximumPrice)
> WHEN 'RegistrationDate' THEN CONVERT(varchar(50), RegistrationDate)
> WHEN 'Status' THEN CONVERT(varchar(50), Status)
> END
>
> It works fine for string fields, but not for numbers or dates.
>
> Can any one help please??

Use this:
Cast(convert(VarChar(8), RegistrationDate, 112) as Int) desc

Rahul

richard

3/26/2007 11:32:00 AM

0

On 26 Mar, 12:58, "Uri Dimant" <u...@iscar.co.il> wrote:
> Hi
> create table #t (c1 int,c2 char(1),c3 datetime)
>
> insert into #t values (10,'a','20070101')
> insert into #t values (2,'b','20070201')
> insert into #t values (300,'d','20070103')
> insert into #t values (9,'c','20060103')
>
> declare @sortby varchar(10)
> set @sortby='c1'
>
> select * from #t order by
> case when @sortby='c1' then c1 end,
> case when @sortby='c2' then c2 end,
> case when @sortby='c3' then c3 end
>
> <rich...@ultraitsolutions.com> wrote in message
>
> news:1174905893.930284.8090@y80g2000hsf.googlegroups.com...
>
>

This doesn't make sense, if I do it this way, wouldn't @sortby always
be c1?

Uri Dimant

3/26/2007 11:44:00 AM

0

> This doesn't make sense, if I do it this way, wouldn't @sortby always
> be c1?

Hmm, you have to specify a value for this variable according what theb user
pushed. Stored procedure might get a paramer for sorting, that's all

To call stored procedure

EXEC spbalabala @par1=1,@par2=3,@sortby='c2' --'c1'....










<richard@ultraitsolutions.com> wrote in message
news:1174908738.503852.54320@l77g2000hsb.googlegroups.com...
> On 26 Mar, 12:58, "Uri Dimant" <u...@iscar.co.il> wrote:
>> Hi
>> create table #t (c1 int,c2 char(1),c3 datetime)
>>
>> insert into #t values (10,'a','20070101')
>> insert into #t values (2,'b','20070201')
>> insert into #t values (300,'d','20070103')
>> insert into #t values (9,'c','20060103')
>>
>> declare @sortby varchar(10)
>> set @sortby='c1'
>>
>> select * from #t order by
>> case when @sortby='c1' then c1 end,
>> case when @sortby='c2' then c2 end,
>> case when @sortby='c3' then c3 end
>>
>> <rich...@ultraitsolutions.com> wrote in message
>>
>> news:1174905893.930284.8090@y80g2000hsf.googlegroups.com...
>>
>>
>
> This doesn't make sense, if I do it this way, wouldn't @sortby always
> be c1?
>


richard

3/26/2007 11:58:00 AM

0

On 26 Mar, 13:44, "Uri Dimant" <u...@iscar.co.il> wrote:
> > This doesn't make sense, if I do it this way, wouldn't @sortby always
> > be c1?
>
> Hmm, you have to specify a value for this variable according what theb user
> pushed. Stored procedure might get a paramer for sorting, that's all
>
> To call stored procedure
>
> EXEC spbalabala @par1=1,@par2=3,@sortby='c2' --'c1'....
>
> <rich...@ultraitsolutions.com> wrote in message
>
> news:1174908738.503852.54320@l77g2000hsb.googlegroups.com...
>
>
>
> > On 26 Mar, 12:58, "Uri Dimant" <u...@iscar.co.il> wrote:
> >> Hi
> >> create table #t (c1 int,c2 char(1),c3 datetime)
>
> >> insert into #t values (10,'a','20070101')
> >> insert into #t values (2,'b','20070201')
> >> insert into #t values (300,'d','20070103')
> >> insert into #t values (9,'c','20060103')
>
> >> declare @sortby varchar(10)
> >> set @sortby='c1'
>
> >> select * from #t order by
> >> case when @sortby='c1' then c1 end,
> >> case when @sortby='c2' then c2 end,
> >> case when @sortby='c3' then c3 end
>
> >> <rich...@ultraitsolutions.com> wrote in message
>
> >>news:1174905893.930284.8090@y80g2000hsf.googlegroups.com...
>
> > This doesn't make sense, if I do it this way, wouldn't @sortby always
> > be c1?- Hide quoted text -
>
> - Show quoted text -

Thanks to yuo both,

I think I have 'sorted' it. I sort of used a combination of both
answers:

ORDER BY
CASE WHEN @SortBy = 'ApplicantID' THEN Cast(convert(varchar(4),
ApplicantID) as Int) end,
CASE WHEN @SortBy = 'LastName' THEN CONVERT(varchar(50), LastName)
end,
CASE WHEN @SortBy = 'MaximumPrice' THEN Cast(Convert(varchar(4),
MaximumPrice) as Int) end,
CASE WHEN @SortBy = 'RegistrationDate' THEN Cast(convert(VarChar(8),
RegistrationDate, 112) as Int) end,
CASE WHEN @SortBy = 'Status' THEN CONVERT(varchar(50), Status)
END

Uri Dimant

3/26/2007 12:03:00 PM

0

Richard

Yoy don't need CAST to be used since you have a CASE expression for every
column.

declare @sortby varchar(10)
set @sortby='c1'

select * from #t order by case @sortby when 'c1' then c1 end,
case @sortby when 'c2' then c2 end,
case @sortby when 'c3' then c3 end




<richard@ultraitsolutions.com> wrote in message
news:1174910309.809556.108710@l77g2000hsb.googlegroups.com...
> On 26 Mar, 13:44, "Uri Dimant" <u...@iscar.co.il> wrote:
>> > This doesn't make sense, if I do it this way, wouldn't @sortby always
>> > be c1?
>>
>> Hmm, you have to specify a value for this variable according what theb
>> user
>> pushed. Stored procedure might get a paramer for sorting, that's all
>>
>> To call stored procedure
>>
>> EXEC spbalabala @par1=1,@par2=3,@sortby='c2' --'c1'....
>>
>> <rich...@ultraitsolutions.com> wrote in message
>>
>> news:1174908738.503852.54320@l77g2000hsb.googlegroups.com...
>>
>>
>>
>> > On 26 Mar, 12:58, "Uri Dimant" <u...@iscar.co.il> wrote:
>> >> Hi
>> >> create table #t (c1 int,c2 char(1),c3 datetime)
>>
>> >> insert into #t values (10,'a','20070101')
>> >> insert into #t values (2,'b','20070201')
>> >> insert into #t values (300,'d','20070103')
>> >> insert into #t values (9,'c','20060103')
>>
>> >> declare @sortby varchar(10)
>> >> set @sortby='c1'
>>
>> >> select * from #t order by
>> >> case when @sortby='c1' then c1 end,
>> >> case when @sortby='c2' then c2 end,
>> >> case when @sortby='c3' then c3 end
>>
>> >> <rich...@ultraitsolutions.com> wrote in message
>>
>> >>news:1174905893.930284.8090@y80g2000hsf.googlegroups.com...
>>
>> > This doesn't make sense, if I do it this way, wouldn't @sortby always
>> > be c1?- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks to yuo both,
>
> I think I have 'sorted' it. I sort of used a combination of both
> answers:
>
> ORDER BY
> CASE WHEN @SortBy = 'ApplicantID' THEN Cast(convert(varchar(4),
> ApplicantID) as Int) end,
> CASE WHEN @SortBy = 'LastName' THEN CONVERT(varchar(50), LastName)
> end,
> CASE WHEN @SortBy = 'MaximumPrice' THEN Cast(Convert(varchar(4),
> MaximumPrice) as Int) end,
> CASE WHEN @SortBy = 'RegistrationDate' THEN Cast(convert(VarChar(8),
> RegistrationDate, 112) as Int) end,
> CASE WHEN @SortBy = 'Status' THEN CONVERT(varchar(50), Status)
> END
>


Kalen Delaney

3/26/2007 4:50:00 PM

0

Hi Richard

You've got a couple of different suggestions, but as far as I can tell, no
one explained WHY your sorting is not working.

If you convert all values to character string before sorting, SQL Server
will sort according to your character collation, basically, some sort of
alphabetical order. It will NOT sort them chronologically.

So if you have these dates:
March 4, 2007
April 12, 2006
February 22, 2004

They will be sorted this way, because A is sorted before F, which is sorted
before M.

April 12, 2006
February 22, 2004
March 4, 2007

You'll have a similar problem with numbers. Once you convert them to
character, they will be sorted 'alphabetically', and not numerically. So if
you have these values
45
21
113
6

They will be sorted as:
113
21
45
6

Rahul gave you a suggestion for correct sorting of dates. If you convert to
format 112, the dates will be represented in character format like this, for
which the character sort will be the same as the chronological sort:

20040222
20060412
20070304

With this information, hopefully you can figure out how to sort the numeric
columns.

--
HTH
Kalen Delaney, SQL Server MVP
http://s...


<richard@ultraitsolutions.com> wrote in message
news:1174905893.930284.8090@y80g2000hsf.googlegroups.com...
>I am trying to Order results from a stored procedure depending on the
> columns that the user clicks on the DataGrid.
>
> The user end works fine, but I cannot get the sorting correct in my
> Stored Procedure.
>
> At the momnent I use a variable called @SortBy and then use this code
> in my Stored Procedure:
>
> ORDER BY CASE @SortBy
> WHEN 'ApplicantID' THEN CONVERT(varchar(50), ApplicantID)
> WHEN 'LastName' THEN CONVERT(varchar(50), LastName)
> WHEN 'MaximumPrice' THEN CONVERT(varchar(50), MaximumPrice)
> WHEN 'RegistrationDate' THEN CONVERT(varchar(50), RegistrationDate)
> WHEN 'Status' THEN CONVERT(varchar(50), Status)
> END
>
> It works fine for string fields, but not for numbers or dates.
>
> Can any one help please??
>


SivaCh

3/28/2007 12:14:00 AM

0

Hi,
if the fields that are getting sorted, are only few, then you can type cast
them directly. What i mean is, for example if you know the column ApplicantID
is of type integer, then you can directly sort.

ORDER BY CASE @SortBy
WHEN 'ApplicantID' THEN ApplicantID
WHEN 'LastName' THEN CONVERT(varchar(50), LastName)
WHEN 'MaximumPrice' THEN MaximumPrice
WHEN 'RegistrationDate' THEN RegistrationDate
WHEN 'Status' THEN CONVERT(varchar(50), Status)
END

Can you try this option.

Regards,
Siva
"richard@ultraitsolutions.com" wrote:

> I am trying to Order results from a stored procedure depending on the
> columns that the user clicks on the DataGrid.
>
> The user end works fine, but I cannot get the sorting correct in my
> Stored Procedure.
>
> At the momnent I use a variable called @SortBy and then use this code
> in my Stored Procedure:
>
> ORDER BY CASE @SortBy
> WHEN 'ApplicantID' THEN CONVERT(varchar(50), ApplicantID)
> WHEN 'LastName' THEN CONVERT(varchar(50), LastName)
> WHEN 'MaximumPrice' THEN CONVERT(varchar(50), MaximumPrice)
> WHEN 'RegistrationDate' THEN CONVERT(varchar(50), RegistrationDate)
> WHEN 'Status' THEN CONVERT(varchar(50), Status)
> END
>
> It works fine for string fields, but not for numbers or dates.
>
> Can any one help please??
>
>