pears.NetDefineProcs

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
}