Hi Max,

I'd rather only have one SP that does everything I want it to do.

In any case, IN_ORDER_BY is a varchar(32) variable and not the actual column names/aliases.

Vlad

----- Original Message ----
From: Max Renshaw-Fox <maxrf@...>
To: firebird-support@yahoogroups.com
Sent: Tuesday, January 9, 2007 4:12:55 PM
Subject: Re: [firebird-support] Dynamic ORDER BY in SP













Is it not possible to:



SELECT * FROM GET_DATA(IN_ USER_ID) ORDER BY IN_ORDER_BY



ie order outside the SP?



MAx

> At 06:30 AM 10/01/2007, you wrote:

>>Hi All,

>>

>>I would like to be able to pass in an IN_ORDER_BY parameter into the

>>SP and have it sort the resultset based on that parameter.

>>

>>My SP(simplified version):

>>

>>CREATE PROCEDURE GET_DATA(

>> IN_USER_ID integer,

>> IN_ORDER_BY varchar(32)

>>)

>>RETURNS (

>> OUT_NAME varchar(256) character set unicode_fss,

>> OUT_SIZE integer

>>)

>>AS

>>BEGIN

>> FOR

>> SELECT

>> D.DIR_NAME ITEM_NAME,

>> 0 ITEM_SIZE

>> FROM DIRS D

>> WHERE D.USER_ID = :IN_USER_ID

>> ORDER BY :IN_ORDER_BY

>> INTO

>> OUT_NAME,

>> OUT_SIZE

>> DO

>> BEGIN

>> SUSPEND;

>> END

>>END

>

> Not possible to do it this way. You can't pass parameters that alter

> the structure of a DSQL specification. ...so you can't have a

> replaceable output list or ordering or grouping criterion. You can

> parameterise WHERE criteria only.

>

> In Fb 1.5 and higher you can construct the entire SELECT statement as

> a string and pass that string to EXECUTE STATEMENT. You can

> alternatively do what we used to do before we had EXECUTE

> STATEMENT: branch your procedure according to the value of some

> input argument and write conditional SELECT statements. This

> obviously requires you to restrict the allowable values of this input

> variable to a condition that your procedure can handle.

>

>

>>Is it possible to get something like this to work:

>> execute procedure GET_DATA(1, 'ITEM_NAME DESC');

>> execute procedure GET_DATA(1, 'ITEM_NAME DESC, ITEM_SIZE ASC');

>

> Sure, using one of the approaches mentioned above.

>

>

>>I need to be able to ORDER BY column aliases(ITEM_ NAME) and not the

>>actual columns(D.DIR_ NAME).

>

> That's a different issue. When you alias a column, you are

> specifying a derived output field that is created from an

> expression. In v.1.5 you have to use the degree number (position

> number) in the ORDER BY clause, or otherwise restate the

> expression. (If you are using an alias merely to rename the column,

> then you should specify the ordering on the qualified actual column

> identifier or the degree number, since there is no expression to

> restate...)

>

> In v.2.0, you can use the alias identifier directly in the Order By

> clause.

>

> ./heLen

>

<!--

#ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;
}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;
}
#ygrp-vitnav{
padding-top:10px;
font-family:Verdana;
font-size:77%;
margin:0;
}
#ygrp-vitnav a{
padding:0 1px;
}
#ygrp-actbar{
clear:both;
margin:25px 0;
white-space:nowrap;
color:#666;
text-align:right;
}
#ygrp-actbar .left{
float:left;
white-space:nowrap;
}
.bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;
font-size:77%;
padding:15px 0;
}
#ygrp-ft{
font-family:verdana;
font-size:77%;
border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;
}

#ygrp-vital{
background-color:#e0ecee;
margin-bottom:20px;
padding:2px 0 8px 8px;
}
#ygrp-vital #vithd{
font-size:77%;
font-family:Verdana;
font-weight:bold;
color:#333;
text-transform:uppercase;
}
#ygrp-vital ul{
padding:0;
margin:2px 0;
}
#ygrp-vital ul li{
list-style-type:none;
clear:both;
border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;
color:#ff7900;
float:right;
width:2em;
text-align:right;
padding-right:.5em;
}
#ygrp-vital ul li .cat{
font-weight:bold;
}
#ygrp-vital a {
text-decoration:none;
}

#ygrp-vital a:hover{
text-decoration:underline;
}

#ygrp-sponsor #hd{
color:#999;
font-size:77%;
}
#ygrp-sponsor #ov{
padding:6px 13px;
background-color:#e0ecee;
margin-bottom:20px;
}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;
margin:0;
}
#ygrp-sponsor #ov li{
list-style-type:square;
padding:6px 0;
font-size:77%;
}
#ygrp-sponsor #ov li a{
text-decoration:none;
font-size:130%;
}
#ygrp-sponsor #nc {
background-color:#eee;
margin-bottom:20px;
padding:0 8px;
}
#ygrp-sponsor .ad{
padding:8px 0;
}
#ygrp-sponsor .ad #hd1{
font-family:Arial;
font-weight:bold;
color:#628c2a;
font-size:100%;
line-height:122%;
}
#ygrp-sponsor .ad a{
text-decoration:none;
}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;
}
#ygrp-sponsor .ad p{
margin:0;
}
o {font-size:0;}
.MsoNormal {
margin:0 0 0 0;
}
#ygrp-text tt{
font-size:120%;
}
blockquote{margin:0 0 0 4px;}
.replbq {margin:4;}
-->







__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]