Execute statement with optional parameters
------------------------------------------
Key: CORE-5658
URL: http://tracker.firebirdsql.org/browse/CORE-5658
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Reporter: Vladimir Arkhipov
I want to suggest "optional" directive in execute statement with named
parameters:
EXECUTE STATEMENT (SQL_TEXT) (PARAM1 := VALUE1, OPTIONAL PARAM2 := VALUE2)
If PARAM2 is not found by name then simply don't set optional PARAM2 value in
statement instead of "input parameters mismatch" error.
We have very complex stored procedures, where some parts of sql (joins,
conditions, derived tables and etc) can be added dynamically depending on input
procedure parameters.
For example (the real procedures are more complex):
create or alter procedure TEST_CLIENTS_LIST
(
search_name varchar(255),
search_phone varchar(64) = ''
)
returns
(
pcode bigint,
fullname varchar(255)
)
as
declare sql_text varchar(1024);
begin
sql_text = 'select c.pcode, c.fullname from clients c';
-- дополнительная фильтрация по номеру телефона
if (search_phone > '') then
sql_text = sql_text || ' left join clphones p on c.pcode = p.pcode';
-- обязательный поиск по имени
sql_text = sql_text || ' where c.fullname starting with :search_name';
-- дополнительная фильтрация по номеру телефона
if (search_phone > '') then
sql_text = sql_text || ' and p.phone = :search_phone';
for execute statement (sql_text)
(search_name := search_name,
search_phone := search_phone)
into pcode, fullname do
suspend;
end
After selecting from this procedure with "search_name" parameter without
"search_phone" we get error "input parameters mismatch" because there is no
"search_phone" parameter in sql text.
Unfortunately at present in these cases we often have to set parameter value
directly in sql text without using parameters or make many "if" sections for
executing sql with different parameters.
Although we could use "optional" syntax in these cases.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
