USE [SDS_DevSchoolDistrict]
GO
/****** Object:  UserDefinedFunction [dbo].[MetadataOfProcedureParameters]    Script Date: 05/16/2011 21:23:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--SELECT * FROM MetadataOfProcedureParameters('AddAssessment')
-- =================================================================================
-- Copyright:   School Data Solutions, LLC
-- =================================================================================
-- =================================================================================

-- Sample Execution
/*


SELECT * FROM MetadataOfTableFields('Schools')
    SELECT
              *
    FROM      dbo.MetadataOfProcedureParameters('AddStudentAssessmentTestScore')

*/
-- =================================================================================
ALTER FUNCTION [dbo].[MetadataOfProcedureParameters]
(
      @ProcedureName                    sysname
)
--DECLARE @ProcedureName sysname        SET @ProcedureName = 'AddAssessment'
--DECLARE @OutputTable  TABLE
RETURNS @OutputTable    TABLE
(
      ParameterName                     sysname
    , ParameterOrdinal                  int
    , ParameterDataType                 sysname
    , ParameterValueType                sysname
    , ParameterInterfaceType            sysname

    , ParameterMaximumLength            int DEFAULT('')
    , ParameterIsNumeric                bit
    , ParameterIsRequired               bit
    , ParameterDefaultValue             varchar(50) DEFAULT('')
    , ParameterRequiresSelection        bit  DEFAULT('')
    , ParameterConstraint               varchar(500)  DEFAULT('')
    , ParameterIsOutput                 bit

    , ParameterSelectionSource          sysname
    --, CorrespondingTableName            sysname
    --, CorrespondingFieldName            sysname
)
AS
BEGIN
    -------   ======================================================================


    -------   ======================================================================
    -------   Declarations
    -------   ======================================================================
    DECLARE   @PrimaryKeyFields          TABLE
    (
              TableName                 sysname
            , PrimaryKeyFieldName       sysname
          --  , RepresentativeFieldName sysname
    )
    -------   ======================================================================




    -------   ======================================================================
    -------   Declarations
    -------   ======================================================================
    DECLARE   @ProcedureParameterDefaultValues          TABLE
    (
              ParameterName                 sysname
            , ParameterType                 varchar(50)
            , ParameterDefaultValue         varchar(100)
            , ParameterIsOutput             bit
            , ParameterLine                 varchar(1000)
    )

    -------   ----------------------------------------------------------------------
    DECLARE   @ProcedureText        nvarchar(max)
    SET       @ProcedureText    =   ''


    -------   ======================================================================
    -------   Parse Procedure Parameter List
    -------   ======================================================================

    -------   Retrieve Procedure Definition
    SELECT
              @ProcedureText =  ProcedureDefinitions.definition
    FROM      sys.procedures    Procedures
    JOIN      sys.sql_modules   ProcedureDefinitions
              on  ProcedureDefinitions.object_id    = Procedures.object_id
    WHERE     Procedures.name       = @ProcedureName


    -------   Collapse White Space to Single Spaces Only
    SET       @ProcedureText = dbo.RegExReplace(@ProcedureText, '\s+' , ' ')


    -------   Trap Procedure Parameters, discarding unnecessary text
    SET       @ProcedureText = dbo.RegExReplace(@ProcedureText, '(^.*CREATE\s+PROC.*\[' + @ProcedureName + '\]\s+)|(AS\s+BEGIN.*$)', '')


    -------    Remove in-line comments in parameters
    SET       @ProcedureText = dbo.RegExReplace(@ProcedureText, '--(\s*[^,])*(,){0,1}' , '$2')

    -------    Remove comments blocks in parameters
    SET       @ProcedureText = dbo.RegExReplace(@ProcedureText, '/\*.*\*/' , '')


    -------   ======================================================================
    -------   Populate Table Variable With Parameter Default Values
    -------   ======================================================================
    INSERT    @ProcedureParameterDefaultValues
    SELECT
              dbo.RegExReplace([Value], '(\S*)\s.*', '$1')                              AS  ParameterName
            , dbo.RegExReplace([Value], '(\S*)\s*(\S*).*', '$2')                        AS  ParameterDataType
            , CASE  dbo.RegExReplace([Value], '(.*)=\s*(.*)(OUTPUT|OUT)*', '$2')
                    WHEN    [Value]
                        THEN    NULL
                    ELSE        dbo.RegExReplace([Value], '(.*)=\s*(.*)(OUTPUT|OUT)*', '$2')
              END                                                                       AS  ParameterDefaultValue
            , CASE
                    WHEN    dbo.RegExReplace([Value], '.*(OUTPUT|OUT)\s*', '$1') IN ('OUTPUT', 'OUT')
                        THEN    1
                    ELSE        0
              END                                                                       AS  ParameterIsOutput
            , [Value]                                                                   As  ParameterLine
    FROM      dbo.SplitList(@ProcedureText, ',')
    -------   -----------------------------------------------------------------------
    -- SELECT 'defaults', * FROM @ProcedureParameterDefaultValues
    -------   ======================================================================







    -------   ======================================================================
    -------   Populate Return Table
    -------   ======================================================================
    INSERT    @OutputTable (
                  ParameterName
                , ParameterOrdinal
                , ParameterDataType
                , ParameterValueType
                , ParameterInterfaceType

                , ParameterMaximumLength
                , ParameterIsNumeric
                , ParameterIsRequired
                , ParameterDefaultValue
                , ParameterRequiresSelection
                , ParameterConstraint
                , ParameterIsOutput

                , ParameterSelectionSource
            --  , CorrespondingTableName
            --  , CorrespondingFieldName
            )
    -------   ----------------------------------------------------------------------
    SELECT    REPLACE(PARAMETER_NAME, '@', '')                  AS  ParameterName
            , ORDINAL_POSITION                                  AS  ParameterOrdinal
            , ISNULL(DATA_TYPE, '')                             AS  ParameterDataType
            , ISNULL(USER_DEFINED_TYPE_NAME, '')                AS  ParameterValueType
            , dbo.InterfaceType (     DATA_TYPE
                                    , USER_DEFINED_TYPE_NAME
                                    , CASE WHEN(D.ParameterDefaultValue  IS NULL) THEN 0 ELSE 1 END
                                    , CHARACTER_MAXIMUM_LENGTH
                                )                                           AS  ParameterInterfaceType
            , CHARACTER_MAXIMUM_LENGTH                                      AS  ParameterMaximumLength
            , dbo.DatatypeIsNumeric(DATA_TYPE)                              AS  ParameterIsNumeric
            , CASE WHEN(D.ParameterDefaultValue IS NULL)THEN 1 ELSE 0 END   AS  ParameterIsRequired
            , D.ParameterDefaultValue                                       AS  ParameterDefaultValue
            , NULL                                                          AS  ParameterRequiresSelection
            , NULL                                                          AS  ParameterConstraint
            , CASE WHEN(PARAMETER_MODE = 'IN') THEN 0 ELSE 1 END            AS  ParameterIsOutput
            , ISNULL(NULL, '')                                              AS  SelectionSource
            --, ISNULL(NULL, '')                                                AS  CorrespondingTableName
            --, ISNULL(NULL, '')                                                AS  CorrespondingFieldName
    FROM    INFORMATION_SCHEMA.PARAMETERS
            --------------------------------------------------------------------------
    JOIN    @ProcedureParameterDefaultValues D -- DefaultValues
      ON    D.ParameterName = PARAMETER_NAME
            --------------------------------------------------------------------------
    WHERE   SPECIFIC_NAME = @ProcedureName
    --AND   PARAMETER_MODE = 'IN'
    ORDER
       BY   ORDINAL_POSITION
    -------   --------------------------------------------------------------------------



    ----------------------------------------------------------------------
    -- If there are any parameters of a "Reference" type
    -- we need to retrieve the table and fields neccessary for selecting a value
    ----------------------------------------------------------------------
    IF(EXISTS(SELECT 1 FROM @OutputTable WHERE ParameterValueType LIKE '%Reference%'))
    BEGIN
            -------   ======================================================================
            -------   First we try to match parameter names against existing Primary-Key fields
            -------   ======================================================================
            INSERT @PrimaryKeyFields
            (       TableName,
                    PrimaryKeyFieldName
            )
            SELECT  pk.table_name,
                    column_name as 'primary_key'
            FROM    information_schema.table_constraints pk
            JOIN    information_schema.key_column_usage c
              ON    c.table_name = pk.table_name
             AND    c.constraint_name = pk.constraint_name
            JOIN    (
                        SELECT  pk.table_name,
                                COUNT(DISTINCT column_name) PrimaryKeyFieldCount
                        FROM    information_schema.table_constraints pk
                        JOIN    information_schema.key_column_usage c
                          ON    c.table_name = pk.table_name
                         AND    c.constraint_name = pk.constraint_name
                        WHERE   constraint_type = 'primary key'
                        GROUP
                           BY   pk.table_name
                        HAVING  COUNT(DISTINCT column_name) = 1
                    ) E -- ElementTables (single primary key field
              ON    c.table_name = E.table_name
            WHERE   constraint_type = 'primary key'
            -------   ======================================================================



            UPDATE  @OutputTable
            SET     ParameterSelectionSource = PK.TableName,
                    ParameterRequiresSelection = 1
            --      CorrespondingTableName = PK.TableName,
            --      CorrespondingFieldName = PK.PrimaryKeyFieldName
            --SELECT    PK.*, P.*
            FROM    @OutputTable P
            JOIN    @PrimaryKeyFields PK
              ON    P.ParameterName = PK.PrimaryKeyFieldName
             AND    P.ParameterValueType LIKE '%Reference%'

    END
    ----------------------------------------------------------------------
    -- SELECT * FROM @OutputTable
    -------   ======================================================================
    RETURN
END