Thanks Helen.

Is it possible to apply DOMAINs to variables and in/out parameters within a SP in FB 2.0?

CREATE DOMAIN DOM_ITEM_ID AS INTEGER NOT NULL;
CREATE DOMAIN DOM_ITEM_NAME AS VARCHAR(256) CHARACTER SET UNICODE_FSS NOT NULL COLLATE UNICODE_FSS;

CREATE PROCEDURE GET_DATA2(
IN_USER_ID TYPE OF DOM_ITEM_ID
)
RETURNS (
OUT_NAME TYPE OF DOM_ITEM_NAME
)
AS
BEGIN
FOR
SELECT
D.DIR_NAME ITEM_NAME
FROM DIRS D
WHERE D.USER_ID = :IN_USER_ID
INTO
OUT_NAME
DO
BEGIN
SUSPEND;
END
END

Thank you in advance,
Vlad

----- Original Message ----
From: Helen Borrie <helebor@...>
To: firebird-support@yahoogroups.com
Sent: Tuesday, January 9, 2007 3:22:44 PM
Subject: Re: [firebird-support] Dynamic ORDER BY in SP













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]