Order By

[rohithere]

Hi,

While saving stored procedure i get the following error:

Error 1008: 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.

Actually Iam passing column name for the order by clause as a parameter to the stored procedure.

Please explain to me what it is all about.How it can be rectified.

Thankx.

 

[nigelrivett]

you probably need something like

order by
case when @colname = 'col1' then col1 end ,
case when @colname = 'col2' then col2 end ,
case when @colname = 'col3' then col3 end


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

Thankx.

But can I use else in when then end eg:

case when @orderByType = 'grp.groupID ASC' then grp.groupName ASC else if 'grp.groupID DESC' then grp.groupName DESC end ,

 

[TheGreenOne]

It would be:
case
when @orderByType = 'grp.groupID ASC' then grp.groupName ASC
when @orderByType = 'grp.groupID DESC' then grp.groupName DESC
end

I gave up trying to dynamically change the order by clause.
Perhaps my problem was that I needed to sort by 2 columns.
Let me know if you get this to work.

 

[rohithere]

ok & thanks

 

[nigelrivett]

You can't use asc/desc in the middle of a statement so

case when @orderByType = 'grp.groupID ASC' then grp.groupName end ASC ,
case when @orderByType = 'grp.groupID DESC' then grp.groupName end DESC

Note if the case statement does not match then the value returned is null so that statement will not affect the ordering.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

Hi,
Thanks.

Say if i have 15 columns,then would i have to write like this 30 times in every if end statements??
Because my Stored Procedure contains 4 if Ends evaluating 4 different senarios.

Do we have a workaround this??

 

[crodwell]

Try this

declare @SQLStatement as varchar(500)

set @SQLStatement ='select * from customers order by ' + @OrderBy

exec (@SQLStatement)

 

[rohithere]

The below is the stored procedure code:


CREATE PROCEDURE [ReportAllSpecified] @partnerID int,@groupID int,@destinationID int,@fromDate varchar(50),@toDate varchar(50),@reportType varchar(50),@orderByType varchar(50)
AS

If (@reportType = 'All')
Begin
Select grp.groupName,grp.vendorid, pm.NimbusPartnerCode, loc.locationName, ord.orderDate,ct.rate,ord.attendedBy,ct.rechargable, ct.ControlID,
ct.orderId,ct.PINNo,cust.name,ord.userid,ct.cardName, cust.customerID
From orders ord Inner Join customerTransaction ct On ord.orderid=ct.orderid And CAST(ord.orderDate As datetime) between @fromDate And @toDate
Inner join batch bth On ct.batchid=bth.batchid Inner join groupp grp on bth.groupid=grp.groupid And grp.groupid=@groupID
inner Join location loc On bth.locationid=loc.locationid And loc.locationid=@destinationID Inner join customer cust
on ord.userid=cust.userid Inner Join partnerMaster pm On ord.partnerid=pm.partnerid And pm.partnerid=@partnerID
Group By grp.groupName,grp.vendorid, pm.NimbusPartnerCode, loc.locationName,
ord.orderDate,ct.rate,ord.attendedBy,ct.rechargable, ct.ControlID,
ct.orderId,ct.PINNo,cust.name,ord.userid,ct.cardName,
cust.customerID
Order By
case when @orderByType = 'grp.groupID ASC' then grp.groupName end ASC,
case when @orderByType = 'grp.groupID DESC' then grp.groupName end DESC ,
case when @orderByType = 'ord.orderDate ASC' then ord.orderDate end ASC,
case when @orderByType = 'ord.orderDate DESC' then ord.orderDate end DESC,
case when @orderByType = 'pm.NimbusPartnerCode ASC' then pm.NimbusPartnerCode end ASC,
case when @orderByType = 'pm.NimbusPartnerCode DESC' then pm.NimbusPartnerCode end DESC,
case when @orderByType = 'ct.ControlID ASC' then ct.ControlID end ASC,
case when @orderByType = 'ct.ControlID DESC' then ct.ControlID end DESC,
case when @orderByType = 'ct.rechargable ASC' then ct.rechargable end ASC,
case when @orderByType = 'ct.rechargable DESC' then ct.rechargable end DESC,
case when @orderByType = 'grp.vendorID ASC' then grp.vendorID end ASC,
case when @orderByType = 'grp.vendorID DESC' then grp.vendorID end DESC,

--case when @orderByType = 'ord.OrderId ASC' then ord.OrderId end ASC ,
--case when @orderByType = 'ord.OrderId DESC' then ord.OrderId end DESC ,
case when @orderByType = 'ct.PINNo ASC' then ct.PINNo end ASC,
case when @orderByType = 'ct.PINNo DESC' then ct.PINNo end DESC,

case when @orderByType = 'cust.name ASC' then cust.name end ASC,
case when @orderByType = 'cust.name DESC' then cust.name end DESC,

case when @orderByType = 'ct.cardName ASC' then grp.groupName end ASC,
case when @orderByType = 'ct.cardName DESC' then grp.groupName end DESC,

case when @orderByType = 'cust.customerID ASC' then cust.customerID end ASC,
case when @orderByType = 'cust.customerID DESC' then cust.customerID end DESC,

case when @orderByType = 'loc.locationName ASC' then loc.locationName end ASC,
case when @orderByType = 'loc.locationName DESC' then loc.locationName end DESC,

case when @orderByType = 'ord.attendedBy ASC' then ord.attendedBy end ASC,
case when @orderByType = 'ord.attendedBy DESC' then ord.attendedBy end DESC,

case when @orderByType = 'ct.rate ASC' then ct.rate end ASC,
case when @orderByType = 'ct.rate DESC' then ct.rate end DESC

End
If (@reportType = 'Bought')
Begin
Select grp.groupName,grp.vendorid, pm.NimbusPartnerCode, loc.locationName,
ord.orderDate,ct.rate,ord.attendedBy,ct.rechargable, ct.ControlID,
ct.orderId,ct.PINNo,cust.name,ord.userid,ct.cardName,
cust.customerID
From orders ord
Inner Join customerTransaction ct On ord.orderid=ct.orderid And (ct.cardName Not LIKE 'Recharge%' And ct.cardName Not LIKE 'Redeem%') And CAST(ord.orderDate As datetime) between @fromDate And @toDate
Inner join batch bth On ct.batchid=bth.batchid Inner join groupp grp on bth.groupid=grp.groupid And grp.groupid=@groupID
inner Join location loc On bth.locationid=loc.locationid And loc.locationid=@destinationID Inner join customer cust
on ord.userid=cust.userid Inner Join partnerMaster pm On ord.partnerid=pm.partnerid And pm.partnerid=@partnerID
Group By grp.groupName,grp.vendorid, pm.NimbusPartnerCode, loc.locationName,
ord.orderDate,ct.rate,ord.attendedBy,ct.rechargable, ct.ControlID,
ct.orderId,ct.PINNo,cust.name,ord.userid,ct.cardName,
cust.customerID
Order By
case when @orderByType = 'grp.groupID ASC' then grp.groupName end ASC,
case when @orderByType = 'grp.groupID DESC' then grp.groupName end DESC ,
case when @orderByType = 'ord.orderDate ASC' then ord.orderDate end ASC,
case when @orderByType = 'ord.orderDate DESC' then ord.orderDate end DESC,
case when @orderByType = 'pm.NimbusPartnerCode ASC' then pm.NimbusPartnerCode end ASC,
case when @orderByType = 'pm.NimbusPartnerCode DESC' then pm.NimbusPartnerCode end DESC,
case when @orderByType = 'ct.ControlID ASC' then ct.ControlID end ASC,
case when @orderByType = 'ct.ControlID DESC' then ct.ControlID end DESC,
case when @orderByType = 'ct.rechargable ASC' then ct.rechargable end ASC,
case when @orderByType = 'ct.rechargable DESC' then ct.rechargable end DESC,
case when @orderByType = 'grp.vendorID ASC' then grp.vendorID end ASC,
case when @orderByType = 'grp.vendorID DESC' then grp.vendorID end DESC,

--case when @orderByType = 'ord.OrderId ASC' then ord.OrderId end ASC ,
--case when @orderByType = 'ord.OrderId DESC' then ord.OrderId end DESC ,
case when @orderByType = 'ct.PINNo ASC' then ct.PINNo end ASC,
case when @orderByType = 'ct.PINNo DESC' then ct.PINNo end DESC,

case when @orderByType = 'cust.name ASC' then cust.name end ASC,
case when @orderByType = 'cust.name DESC' then cust.name end DESC,

case when @orderByType = 'ct.cardName ASC' then grp.groupName end ASC,
case when @orderByType = 'ct.cardName DESC' then grp.groupName end DESC,

case when @orderByType = 'cust.customerID ASC' then cust.customerID end ASC,
case when @orderByType = 'cust.customerID DESC' then cust.customerID end DESC,

case when @orderByType = 'loc.locationName ASC' then loc.locationName end ASC,
case when @orderByType = 'loc.locationName DESC' then loc.locationName end DESC,

case when @orderByType = 'ord.attendedBy ASC' then ord.attendedBy end ASC,
case when @orderByType = 'ord.attendedBy DESC' then ord.attendedBy end DESC,

case when @orderByType = 'ct.rate ASC' then ct.rate end ASC,
case when @orderByType = 'ct.rate DESC' then ct.rate end DESC

End

If (@reportType = 'Recharges')
Begin
Select grp.groupName,grp.vendorid, pm.NimbusPartnerCode, loc.locationName,
ord.orderDate,ct.rate,ord.attendedBy,ct.rechargable, ct.ControlID,
ct.orderId,ct.PINNo,cust.name,ord.userid,ct.cardName,
cust.customerID
From orders ord
Inner Join customerTransaction ct
On ord.orderid=ct.orderid And (ct.cardName LIKE 'Recharge%') And CAST(ord.orderDate As datetime) between @fromDate And @toDate
Inner join batch bth
On ct.batchid=bth.batchid
Inner join groupp grp
on bth.groupid=grp.groupid And grp.groupid=@groupID
inner Join location loc
On bth.locationid=loc.locationid And loc.locationid=@destinationID
Inner join customer cust
on ord.userid=cust.userid
Inner Join partnerMaster pm
On ord.partnerid=pm.partnerid And pm.partnerid=@partnerID
Group By grp.groupName,grp.vendorid, pm.NimbusPartnerCode, loc.locationName,
ord.orderDate,ct.rate,ord.attendedBy,ct.rechargable, ct.ControlID,
ct.orderId,ct.PINNo,cust.name,ord.userid,ct.cardName,
cust.customerID
Order By
case when @orderByType = 'grp.groupID ASC' then grp.groupName end ASC,
case when @orderByType = 'grp.groupID DESC' then grp.groupName end DESC ,
case when @orderByType = 'ord.orderDate ASC' then ord.orderDate end ASC,
case when @orderByType = 'ord.orderDate DESC' then ord.orderDate end DESC,
case when @orderByType = 'pm.NimbusPartnerCode ASC' then pm.NimbusPartnerCode end ASC,
case when @orderByType = 'pm.NimbusPartnerCode DESC' then pm.NimbusPartnerCode end DESC,
case when @orderByType = 'ct.ControlID ASC' then ct.ControlID end ASC,
case when @orderByType = 'ct.ControlID DESC' then ct.ControlID end DESC,
case when @orderByType = 'ct.rechargable ASC' then ct.rechargable end ASC,
case when @orderByType = 'ct.rechargable DESC' then ct.rechargable end DESC,
case when @orderByType = 'grp.vendorID ASC' then grp.vendorID end ASC,
case when @orderByType = 'grp.vendorID DESC' then grp.vendorID end DESC,

--case when @orderByType = 'ord.OrderId ASC' then ord.OrderId end ASC ,
--case when @orderByType = 'ord.OrderId DESC' then ord.OrderId end DESC ,
case when @orderByType = 'ct.PINNo ASC' then ct.PINNo end ASC,
case when @orderByType = 'ct.PINNo DESC' then ct.PINNo end DESC,

case when @orderByType = 'cust.name ASC' then cust.name end ASC,
case when @orderByType = 'cust.name DESC' then cust.name end DESC,

case when @orderByType = 'ct.cardName ASC' then grp.groupName end ASC,
case when @orderByType = 'ct.cardName DESC' then grp.groupName end DESC,

case when @orderByType = 'cust.customerID ASC' then cust.customerID end ASC,
case when @orderByType = 'cust.customerID DESC' then cust.customerID end DESC,

case when @orderByType = 'loc.locationName ASC' then loc.locationName end ASC,
case when @orderByType = 'loc.locationName DESC' then loc.locationName end DESC,

case when @orderByType = 'ord.attendedBy ASC' then ord.attendedBy end ASC,
case when @orderByType = 'ord.attendedBy DESC' then ord.attendedBy end DESC,

case when @orderByType = 'ct.rate ASC' then ct.rate end ASC,
case when @orderByType = 'ct.rate DESC' then ct.rate end DESC

End

If (@reportType = 'LBRedeemed')
Begin
Select grp.groupName,grp.vendorid, pm.NimbusPartnerCode, loc.locationName,
ord.orderDate,ct.rate,ord.attendedBy,ct.rechargable, ct.ControlID,
ct.orderId,ct.PINNo,cust.name,ord.userid,ct.cardName,
cust.customerID
From orders ord
Inner Join customerTransaction ct
On ord.orderid=ct.orderid And (ct.cardName LIKE 'Redeem%') And CAST(ord.orderDate As datetime) between @fromDate And @toDate
Inner join batch bth
On ct.batchid=bth.batchid
Inner join groupp grp
on bth.groupid=grp.groupid And grp.groupid=@groupID
inner Join location loc
On bth.locationid=loc.locationid And loc.locationid=@destinationID
Inner join customer cust
on ord.userid=cust.userid
Inner Join partnerMaster pm
On ord.partnerid=pm.partnerid And pm.partnerid=@partnerID
Group By grp.groupName,grp.vendorid, pm.NimbusPartnerCode, loc.locationName,
ord.orderDate,ct.rate,ord.attendedBy,ct.rechargable, ct.ControlID,
ct.orderId,ct.PINNo,cust.name,ord.userid,ct.cardName,
cust.customerID
Order By
case when @orderByType = 'grp.groupID ASC' then grp.groupName end ASC,
case when @orderByType = 'grp.groupID DESC' then grp.groupName end DESC ,
case when @orderByType = 'ord.orderDate ASC' then ord.orderDate end ASC,
case when @orderByType = 'ord.orderDate DESC' then ord.orderDate end DESC,
case when @orderByType = 'pm.NimbusPartnerCode ASC' then pm.NimbusPartnerCode end ASC,
case when @orderByType = 'pm.NimbusPartnerCode DESC' then pm.NimbusPartnerCode end DESC,
case when @orderByType = 'ct.ControlID ASC' then ct.ControlID end ASC,
case when @orderByType = 'ct.ControlID DESC' then ct.ControlID end DESC,
case when @orderByType = 'ct.rechargable ASC' then ct.rechargable end ASC,
case when @orderByType = 'ct.rechargable DESC' then ct.rechargable end DESC,
case when @orderByType = 'grp.vendorID ASC' then grp.vendorID end ASC,
case when @orderByType = 'grp.vendorID DESC' then grp.vendorID end DESC,

--case when @orderByType = 'ord.OrderId ASC' then ord.OrderId end ASC ,
--case when @orderByType = 'ord.OrderId DESC' then ord.OrderId end DESC ,
case when @orderByType = 'ct.PINNo ASC' then ct.PINNo end ASC,
case when @orderByType = 'ct.PINNo DESC' then ct.PINNo end DESC,

case when @orderByType = 'cust.name ASC' then cust.name end ASC,
case when @orderByType = 'cust.name DESC' then cust.name end DESC,

case when @orderByType = 'ct.cardName ASC' then grp.groupName end ASC,
case when @orderByType = 'ct.cardName DESC' then grp.groupName end DESC,

case when @orderByType = 'cust.customerID ASC' then cust.customerID end ASC,
case when @orderByType = 'cust.customerID DESC' then cust.customerID end DESC,

case when @orderByType = 'loc.locationName ASC' then loc.locationName end ASC,
case when @orderByType = 'loc.locationName DESC' then loc.locationName end DESC,

case when @orderByType = 'ord.attendedBy ASC' then ord.attendedBy end ASC,
case when @orderByType = 'ord.attendedBy DESC' then ord.attendedBy end DESC,

case when @orderByType = 'ct.rate ASC' then ct.rate end ASC,
case when @orderByType = 'ct.rate DESC' then ct.rate end DESC

End
GO

Is it correct to write such a long SP?Moreover when I pass the para to the SP from my asp page it gives the foll error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function ReportAllSpecified has too many arguments specified.

/controlpannel/generateReport.asp, line 240


Which was not there until I added one more para ie.@orderByType To my SP for the value for order by clause.

Please help me as where iam lacking.

Thankz.

 

[nigelrivett]

You could shorten it a bit by grouping all sort fields of the same type into the same case statements.
or select the resultset into a temp table and then use dynamic sql to return the sorted result set.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

1)Could u give an example of that.

2)Moreover when I pass the para to the SP from my asp page it gives the foll error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function ReportAllSpecified has too many arguments specified.

/controlpannel/generateReport.asp, line 240


Which was not there until I added one more para ie.@orderByType To my SP for the value for order by clause.

Please help me solve this problem.



thankz.

 

[nigelrivett]

select .....
into #a
from ....

declare @sql varchar(1000)
select @sql = 'select * from #a order by ' + @orderByType
exec (@sql)


The error is just a parameters mismatch.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

Thankz.

But in my case I have no control over the field names in the order by clause.It is choosed dynamically by the user from the report in the form of which heading he wants to be sorted as ascending order or desending order.

 

[nigelrivett]

Oops - the order by variable contains the originating table so it would have to be

select @orderByType = right(@orderByType, len(@orderByType) - charindex('.',@orderByType ))

declare @sql varchar(1000)
select @sql = 'select * from #a order by ' + @orderByType
exec (@sql)

It doesn't matter that you don't have control of the contents of the variable as long as the field referenced is in the result set.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

What does this step do:

select @orderByType = right(@orderByType, len(@orderByType) - charindex('.',@orderByType ))

 

[nigelrivett]

It gets rid of the table

'grp.groupID DESC'

becomes
'groupID DESC'

As you have everything in a temp table you cannot use the source table qualifier for the field.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

Ok.

But do I have to write the foll code :

select @orderByType = right(@orderByType, len(@orderByType) - charindex('.',@orderByType ))

declare @sql varchar(1000)
select @sql = 'select * from #a order by ' + @orderByType
exec (@sql)


in my SP or in the asp page??Because if the include it in the SP then i will have the same prob of passing the col name as para to the order by clause in the SP.

What do you suggest??

 

[nigelrivett]

Yes it has to be in the SP as that's where the temp table is created - it will be dropped on exit.

You need to find out why the parameter is failing to be passed to the SP.

Use the profiler to see what is sent.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rohithere]

Thankx.

But by storing the result in an local variable eg:

declare @sql varchar(1000)
select @sql = 'select * from #a order by ' + @orderByType
exec (@sql)



Would be risky as the result set might contain 5 rows or 500 rows.So for that declaring the size of the variable assuming that the result set cannot be bigger than say @sql varchar(15000)would pose a prob if the result set happens to be of 25000.

Is there a another way to execute the above query ???

 

[nigelrivett]

The resultset is being stored in a temp table #a which is held in tempdb.

Create tyhe temp table by removing the oreder by clause from the select and putting

into #a

after the select clause.
The variable is just to hold the select statement which is exec'd (dynamic sql) to return the result set.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.