Order By Variable

[rzirpolo]

CREATE PROCEDURE sp_rpt_data

@xrefid integer,
@order1 integer,
@order2 integer,
@order3 integer

AS

select sid.number, sid.date_of_call, sid.time_of_call
from data sid
where sid.station_xref_id = convert(varchar(6),@xrefid)
order by @order1, @order2, @order3

I cannot create the above procedure as I get the standard 'Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position.'

I know you can't order by a variable so I have configured this to use CASE and WHEN statements so it works like this...



CREATE PROCEDURE sp_rpt_data

@xrefid integer,
@order1 integer,
@order2 integer,
@order3 integer

AS

select sid.number, sid.date_of_call, sid.time_of_call
from data sid
where sid.station_xref_id = convert(varchar(6),@xrefid)
order by

case when @order1 = 1 or @order2 = 1 or @order3 = 1
then sid.number else null end,

case when @order1 = 2 or @order2 = 2 or @order3 = 2
then sid.date_of_call else null end,

case when @order1 = 3 or @order2 = 3 or @order3 = 3
then sid.time_of_call else null end

end

If you can picture the software which calls this procedure. There are three text boxes for the number, date_of_call and time_of_call.

The way a user selects the sort order is by entering the numbers 1 to 3 which relates to the sort order. These are passed into the variables @order1, @order2 and @order3. I thought this all worked ok but have come across a big problem. If a user specifies (for example) @order1 = 3, @order2 = 2 and @order3 = 1 the sort order will not reflect this as my sort order of columns is pre-defined.

I am really stuck and an't find a solution. Ideally I want to be able to sort by numbers and not the column name but I don't know how I can specify this. If I try and use the variable I'm back to square one with the Error 1008.

Any help would be much appreciated.

 

[philhege]

Your case statement seems to nullify the choice presented by three options. The query would always be ordered by sid.number.

Try this ORDER BY statement:

order by

case when @order1 = 1
then sid.number
when @order1 = 2
then sid.date_of_call
when @order1 = 3
then sid.time_of_call
else null
end,

case when @order2 = 1
then sid.number
when @order2 = 2
then sid.date_of_call
when @order2 = 3
then sid.time_of_call
else null
end,
case when @order3 = 1
then sid.number
when @order3 = 2
then sid.date_of_call
when @order3 = 3
then sid.time_of_call
else null
end

Think about this and see if you can find the flaw. Clue: you may have to use dynamic SQL to get this to work.





Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[rzirpolo]

I did start making the same amendment you have explained. And it seemed to work at first but the further I test I think there are flaws.

Dynamic SQL is not an option, the reason being that for Security I only want users to have access to the stored procedure. Using Dynamic SQL they will need select permissions on the table specified.

 

[TheJon]

you need to use some dynamic SQL. Try something like this:

CREATE PROCEDURE sp_rpt_data

@xrefid    integer,
@order1    integer,
@order2    integer,
@order3    integer
@strSQL    varchar(255)
AS

set @strSQL = 'select sid.number, sid.date_of_call, sid.time_of_call from data sid where sid.station_xref_id =  convert(varchar(6),' + convert(varchar, @xrefid) + 'order by '
-- sort criteria 1
if @order1 = 1 set @strSql = @strSql + 'sid.number, '
if @order2 = 1 set @strSql = @strSql + 'sid.date_of_call, '
if @order3 = 1 set @strSql = @strSql + 'sid.time_of_call, '
-- sort criteria 2
if @order1 = 2 set @strSql = @strSql + 'sid.number, '
if @order2 = 2 set @strSql = @strSql + 'sid.date_of_call, '
if @order3 = 2 set @strSql = @strSql + 'sid.time_of_call, '
-- sort criteria 3
if @order1 = 3 set @strSql = @strSql + 'sid.number'
if @order2 = 3 set @strSql = @strSql + 'sid.date_of_call'
if @order3 = 3 set @strSql = @strSql + 'sid.time_of_call'

exec (@strSql)

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[rzirpolo]

What you have explained will work but Dynamic SQL is not an option. The whole reason I am doing this is because this procedure was originally written using Dynamic SQL. The flaw with Dynamic SQL is that a user/role needs select permissions on the table specified.

Security permissions are only given to Stored Procedures not directly to the tables.

 

[TheJon]

Ok, try this lead from books online regarding execute permissions and 2 stored procedures. could be messy, but could be just what you want.

However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure.

I have not tested this, but it could mean that if you own the stored procedure calling the stored procedure containing the execute command, then the execute is performed under your security, not that of the user.

Let me know if it works!

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[rzirpolo]

This doesn't sound like a good option. The stored procedure in question is accessed by a number of different roles.

There must be a way I can use the numbers passed into the variables in the order by clause.

 

[TheJon]

I keep looking at philhege's answer and think it's almost right. I assume that your order by choices are controlled by a gui which ensures that the three options are always selected? Then this slightly amended code might work:

order by
    
    case     when @order1 = 1 
        then sid.number 
             when @order2 = 1 
        then sid.date_of_call       
             when @order3 = 1 
        then sid.time_of_call
    end, 

 case     when @order1 = 2 
        then sid.number 
             when @order2 = 2 
        then sid.date_of_call       
             when @order3 = 2 
        then sid.time_of_call
    end,
 case     when @order1 = 3 
        then sid.number 
             when @order2 = 3 
        then sid.date_of_call       
             when @order3 = 3 
        then sid.time_of_call
    end

you could set defaults for @order1, @order2, @order3 if they do not match your criteria, or check their values in the procedure.


[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[rzirpolo]

I couldn't get your syntax to work but have come across something very strange but remarkably works. All I have done is use different formulas to calculate the number I wish to validate the variables by in the CASE statement. Take a look...

order by

case when @order1 = 1
then sid.number else null end,

case when @order1 = 2
then sid.date_of_call else null end,

case when @order1 = 3
then sid.time_of_call else null end

case when @order2 = (2-1) --1
then sid.number else null end,

case when @order2 = (3-1) --2
then sid.date_of_call else null end,

case when @order2 = (4-1) --3
then sid.time_of_call else null end

case when @order3 = (4-3) --1
then sid.number else null end,

case when @order3 = (4-2) --2
then sid.date_of_call else null end,

case when @order3 = (5-2) --3
then sid.time_of_call else null end
end


I don't like the look of the code to be honest but it's working to my amazement. I'm doing initial testing and it all looks fine.

 

[RiverGuy]

http://www.talkroot.com/archive/topic/46184-1.html