SQL Anywhere Conditional select statement

Question

I have created this procedure that fetches all the procedures within a database generates a document (via comments) and lists all the "in" and "out" params - this is fine it works perfect!

The problem I am facing is, within the application I'm building I want to select ONE of these procedures (so when a user clicks on one of the items from this list) and output its details (creating a second procedure to handle this is not an option)

Here is the original code (ignore the p***** - this is hidden on purpose (but is never used in the procedure - its for logging)):

ALTER PROCEDURE "USER"."NetDefineProcs"
/* DOC
  Everything between the DOC tags are used to generate the user document.
*/
( in "p*****" char(20), in "pSearchTerm" long varchar, in "pSelectOne" char(1) default null) 
result( "proccessName" long varchar, "Doc" long varchar, "paramName" long varchar,"paramIO" long varchar,"paramDefault" long     varchar,"paramType" long varchar ) 
begin
    SELECT "proc_name" as "proccessName",
    (SELECT substr(source, charindex('/* DOC', source)+6, charindex('*/', source) - charindex('/* DOC', source)-6 ) FROM     "sys"."sysprocedure" where "proc_name" = "proccessName" AND charindex('/* DOC', source) > 0) as "Doc",
    "LIST"("parm_name" order by "parm_type" asc,"parm_id" asc) as "paramName",
    "LIST"("parm_type" order by "parm_type" asc,"parm_id" asc) as "paramIO",
    "LIST"("isnull"("default",'null') order by "parm_type" asc,"parm_id" asc) as "paramDefault",
    "LIST"("isnull"("domain_name",'null') order by "parm_type" asc,"parm_id" asc) as "paramType"
    from "sys"."sysprocedure" as "pr" key join "SYS"."SYSPROCPARM" as "pa" key join "SYS"."SYSDOMAIN"
  -- Below code requires sybase 16 - temporary version 11 friendly version above
  --  "LIST"("isnull"("base_type_str",'null') order by "parm_type" asc,"parm_id" asc) as "paramType"
  --  from "sys"."sysprocedure" as "pr" key join "SYS"."SYSPROCPARM" as "pa" --where pr.proc_name = o.proc_name  ;
  --  WHERE "proc_name" like 'net%' AND "proc_name" like '%' + pSearchTerm + '%'

    WHERE "proc_name" = pSearchTerm OR "proc_name" like 'net%' AND "proc_name" like '%' + pSearchTerm + '%'

    group by "proc_name"
    order by 1 asc
end

The code above works fine for fetching all of them, now here is the code i wrote to attempt to fetch just ONE

ALTER PROCEDURE "USER"."NetDefineProcs"
/* DOC
  Everything between the DOC tags are used to generate the user document.
*/
( in "p*****" char(20), char(20), in "pSearchTerm" long varchar, in "pSelectOne" char(1) default null) 
result( "proccessName" long varchar, "Doc" long varchar, "paramName" long varchar,"paramIO" long varchar,"paramDefault" long varchar,"paramType" long varchar ) 
begin

    CASE
        WHEN pSelectOne = '1' THEN SELECT FIRST "proc_name" as "proccessName",
        ELSE SELECT "proc_name" as "proccessName",
    END

    (SELECT substr(source, charindex('/* DOC', source)+6, charindex('*/', source) - charindex('/* DOC', source)-6 ) FROM "sys"."sysprocedure" where "proc_name" = "proccessName" AND charindex('/* DOC', source) > 0) as "Doc",
    "LIST"("parm_name" order by "parm_type" asc,"parm_id" asc) as "paramName",
    "LIST"("parm_type" order by "parm_type" asc,"parm_id" asc) as "paramIO",
    "LIST"("isnull"("default",'null') order by "parm_type" asc,"parm_id" asc) as "paramDefault",
    "LIST"("isnull"("domain_name",'null') order by "parm_type" asc,"parm_id" asc) as "paramType"
    from "sys"."sysprocedure" as "pr" key join "SYS"."SYSPROCPARM" as "pa" key join "SYS"."SYSDOMAIN"
  -- Below code requires sybase 16 - temporary version 11 friendly version above
  --  "LIST"("isnull"("base_type_str",'null') order by "parm_type" asc,"parm_id" asc) as "paramType"
  --  from "sys"."sysprocedure" as "pr" key join "SYS"."SYSPROCPARM" as "pa" --where pr.proc_name = o.proc_name  ;
  --  WHERE "proc_name" like 'net%' AND "proc_name" like '%' + pSearchTerm + '%'

    WHERE "proc_name" = pSearchTerm OR "proc_name" like 'net%' AND "proc_name" like '%' + pSearchTerm + '%'

    group by "proc_name"
    order by 1 asc
end

Now i know there is a syntax error because of the select statements having the trailing ',' but is there any way i can have something similar to what I'm trying to achieve without it getting to messy?

Answer 1

Solved it!

Added this to the "in" params

in "pSelectOne" char(1) default null

and changed the where clause to

WHERE (pSelectOne IS NOT NULL and "proc_name" = pSearchTerm) or (pSelectOne IS NULL and "proc_name" like 'net%' AND "proc_name" like '%' + pSearchTerm + '%')

and it works.