Re: FETCH and SORT - SELECT Statement Problem.

From: Prasad Koukuntla (prasad.koukuntla_at_scapromo.com)
Date: 06/15/04 

Date: Tue, 15 Jun 2004 14:59:45 -0500

This one works but the earlier example would not work as it had the column
numbers instead of the column names.

To specify the column names in the order by, we can use.

DECLARE @sortOrder varchar(20)
SEt @sortOrder = 'au_id'

SELECT * FROM Authors
ORDER BY
CASE WHEN @sortOrder = 'au_id' Then @sortOrder Else au_lname end

"Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message
news:%23YuQGGxUEHA.412@TK2MSFTNGP10.phx.gbl...
>
> use pubs
> GO
>
>
> DECLARE @sortOrder int
> SEt @sortOrder = 2
>
> SELECT * FROM Authors
> ORDER BY
> CASE WHEN @sortOrder = 1 Then au_id Else au_lname end
>
>
> --
> Roji. P. Thomas
> SQL Server Programmer
> "Prasad Koukuntla" <prasad.koukuntla@scapromo.com> wrote in message
> news:uUSnSAxUEHA.2484@TK2MSFTNGP11.phx.gbl...
> > This may not work as it retuns the following error. You may want to use
> > dynamic SQL.
> >
> > Server: Msg 1008, Level 15, State 1, Line 8
> > The SELECT item identified by the ORDER BY number 1 contains a variable
as
> > part of the expression identifying a column position. Variables are only
> > allowed when ordering by an expression referencing a column name.
> >
> >
> > "Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message
> > news:OFznb7wUEHA.1764@TK2MSFTNGP10.phx.gbl...
> > > 1. You can achieve the same using a SET based Solution instead of
using
> > > CURSOR.
> > >
> > > 2. If you want to sort your output based on on input parameter then
use
> > the
> > > CASE construct. like
> > >
> > > ORDER BY Case when @sortorder = 1 then 1 ELse 2 end
> > >
> > >
> > > --
> > > Roji. P. Thomas
> > > SQL Server Programmer
> > > "Aric Levin" <ariclevinNOSPAM@hotmail.com> wrote in message
> > > news:uwa%23UrwUEHA.1048@tk2msftngp13.phx.gbl...
> > > > I am trying to create a stored procedure that will implement the
> > > following:
> > > > I want to retrieve a certain amount of records from the database
> Sorted
> > > out
> > > > (View Comment after SELECT Statement).
> > > >
> > > >
> > > > -- CODE FOR STORED PROCEDURE
> > > >
> > > > declare @CatalogID smallint, @Keywords varchar(75), @SortOrder
> > > > varchar(25)
> > > >
> > > > SET @CatalogID = 1
> > > > SET @Keywords = 'DOG BARK'
> > > > SET @SortOrder = 'ItemID ASC'
> > > >
> > > > -- Temporary table to store FETCH Results
> > > > CREATE TABLE #MyTempTable (ItemID int,
> > > > SupplierCode int,
> > > > Description varchar(255),
> > > > FileChannels smallint,
> > > > FileLength smallint,
> > > > PreviewURL varchar(50))
> > > >
> > > > declare @ItemID int, @SupplierCode int, @Description varchar(255),
> > > > @FileChannels smallint, @FileLength smallint, @PreviewURL
varchar(50)
> > > >
> > > > DECLARE Search_Cursor CURSOR FOR
> > > > SELECT ItemID, SupplierCode, Description, FileChannels, FileLength,
> > > > PreviewURL
> > > > FROM CatalogItems
> > > > WHERE CategoryCode BETWEEN (@CatalogID * 10000) AND (@CatalogID *
> 10000
> > > > +9999)
> > > > AND Keywords LIKE '%' + @Keywords + '%'
> > > > -- I would like to add here ORDER BY @SortOrder
> > > >
> > > > OPEN Search_Cursor
> > > >
> > > > -- Fetch Records and put in Temporary Table
> > > > FETCH NEXT FROM Search_Cursor
> > > > INTO @ItemID, @SupplierCode, @Description, @FileChannels,
@FileLength,
> > > > @PreviewURL
> > > >
> > > > INSERT INTO #MyTempTable VALUES(@ItemID, @SupplierCode,
@Description,
> > > > @FileChannels, @FileLength, @PreviewURL)
> > > >
> > > > WHILE @@FETCH_STATUS = 0
> > > > BEGIN
> > > >
> > > > FETCH NEXT FROM Search_Cursor
> > > > INTO @ItemID, @SupplierCode, @Description, @FileChannels,
> > @FileLength,
> > > > @PreviewURL
> > > >
> > > > INSERT INTO #MyTempTable VALUES(@ItemID, @SupplierCode,
> > @Description,
> > > > @FileChannels, @FileLength, @PreviewURL)
> > > > END
> > > >
> > > > CLOSE Search_Cursor
> > > > DEALLOCATE Search_Cursor
> > > >
> > > > -- Return the Data from Temporary Table
> > > > SELECT * FROM #MyTempTable
> > > >
> > > > DROP TABLE #MyTempTable
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>