pears.NetDefineProcs [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetDefineProcs ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NetDefineProcs"( 
  /* DOC
<strong>Created:</strong> 06/01/2015 <br /><strong>Updated:</strong> 03/05/2016 <br /><strong>Details:</strong> Fetches the procedures and generates the documentation for themit will also fetch just one procedure when pSelectOne is not null<h4>IN</h4><ul><li><strong>pSearchTerm</strong> - The search string</li><li><strong>pSelectOne</strong> - if is not null will return the first item</li></ul><h4>OUT</h4><ul><li><strong>procccessName</strong> - Name of the procedure</li><li><strong>paramName</strong> - Name of the parameter (both inputs and outputs listed)</li><li><strong>paramIO</strong> - Determines if the paramater is an input or output (0=IN | 1-OUT)</li><li><strong>paramID</strong> - The order that the params come within the procedure (1=FIRST | >1 LAST)</li><li><strong>paramDefault</strong> - The default value of that parameter</li><li><strong>paramType</strong> - The datatype of that param</li></ul><strong>Author:</strong> Gareth <br /><strong>Version:</strong> 1.2
*/
  in "pWebUserID" 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(("pSelectOne" is not null and "proc_name" = "pSearchTerm" and "proc_name" like 'net%') or("pSelectOne" is null and "proc_name" like 'net%' and "proc_name" like '%'+"pSearchTerm"+'%'))
    group by "proc_name"
    order by 1 asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetDefineProcs" IS 
{create PROCEDURE pears."NetDefineProcs"
/* DOC
<strong>Created:</strong> 06/01/2015 <br /><strong>Updated:</strong> 03/05/2016 <br /><strong>Details:</strong> Fetches the procedures and generates the documentation for themit will also fetch just one procedure when pSelectOne is not null<h4>IN</h4><ul><li><strong>pSearchTerm</strong> - The search string</li><li><strong>pSelectOne</strong> - if is not null will return the first item</li></ul><h4>OUT</h4><ul><li><strong>procccessName</strong> - Name of the procedure</li><li><strong>paramName</strong> - Name of the parameter (both inputs and outputs listed)</li><li><strong>paramIO</strong> - Determines if the paramater is an input or output (0=IN | 1-OUT)</li><li><strong>paramID</strong> - The order that the params come within the procedure (1=FIRST | >1 LAST)</li><li><strong>paramDefault</strong> - The default value of that parameter</li><li><strong>paramType</strong> - The datatype of that param</li></ul><strong>Author:</strong> Gareth <br /><strong>Version:</strong> 1.2
*/
( in "pWebUserID" 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 (pSelectOne IS NOT NULL and "proc_name" = pSearchTerm and "proc_name" like 'net%') or (pSelectOne IS NULL and "proc_name" like 'net%' AND "proc_name" like '%' + pSearchTerm + '%')
            group by "proc_name"
            order by 1 asc
end
}
</code>