Import-StoredProcedure.ps1

function Import-StoredProcedure
{
    <#
    .Synopsis
        Imports a Stored Procedure as a PowerShell function
    .Description
        Imports SQL Stored Procedure as a PowerShell functions
    .Notes
        At present, only stored procedures on SQL Server or in SQL Azure are supported.    
    .Link
        Select-SQL
    .Example
        # Generates functions for all stored procedures in SQLAzure who have a name like *WmiSpy*
        Import-StoredProcedure -Name "*WmiSpy*" -ConnectionStringOrSetting SQLAzureConnectionString -EmbedConnectionString
    #>
    [OutputType([string])]
    param(
    # The names of specific stored procedures
    [Parameter(Position=0,ValueFromPipelineByPropertyName=$true)]
    [string[]]
    $Name,

    # A connection string or setting.    
    [Alias('ConnectionString', 'ConnectionSetting')]
    [string]$ConnectionStringOrSetting,
    
    # If set, the connection string will be embedded in the generated functions.  As long as you're using integrated security, this is perfectly safe.
    [Switch]
    $EmbedConnectionString    
    )

    process {
        #region Get Stored Procedures from SQL
        $SqlParams = @{ConnectionString=$ConnectionStringOrSetting}
        if ($Name) {
            $sqlParams.Where = 
                @(foreach ($n in $name) {
                    "Specific_Name LIKE '$($n.Replace('*','%').Replace("'", "''"))'"
                }) -join " OR " 
        }


        $allSprocs = Select-Sql @SqlParams -FromTable "INFORMATION_SCHEMA.ROUTINES" 
        $sprocsParameters = Select-Sql @SqlParams -FromTable "INFORMATION_SCHEMA.PARAMETERS" 

        if ($EmbedConnectionString -and 
            $script:ConnectionString -and 
            -not $PSBoundParameters.ConnectionStringOrSetting) {
            $ConnectionStringOrSetting = $script:ConnectionString
        }

        $sprocParametersByProc = $sprocsParameters | 
            Group-Object { $_.Specific_Schema + "." + $_.Specific_Name } 

        $procedures = $allSprocs | 
            Group-Object {$_.Specific_Schema + "." + $_.Specific_Name } 

        if ($Name) {
            # Filter procedures by name
            $procedures = $procedures | 
                Where-Object {
                    $procName = @($_.Name -split "\.")[1]
                    foreach ($n in $name) { 
                        $procName -like $n
                    } 
                }
        }
        #endregion Get Stored Procedures from SQL


        $verbs = Get-verb | Select-Object -ExpandProperty Verb
        
        foreach ($sproc in $procedures) {
            $parametersByPosition = $sprocParametersByProc |
                Where-Object{ $_.Name -eq $sproc.Name } |
                Select-Object -ExpandProperty Group | 
                Where-Object {$_.Parameter_Mode -like 'IN*' } | 
                Sort-Object ORDINAL_POSITION 
            $sprocName = @($sproc.Name -split "\.")[1]
            $parametersWithDefaults = @{}
            $ParameterHelp = @{}

            $Definition = $sproc.Group|
                Select-Object -First 1 -ExpandProperty Routine_Definition

            $usesDataTable = $false
            # Skip procedures that declare functions
            if ($Definition -match "CREATE(\s{1,})FUNCTION") {
                continue
            }                

            $definitionLines = @($Definition -split "[$([Environment]::NewLine)]" -ne '')
            
            $procedureStartLine = -1
            $procedureParamEnd = -1
            $paranethesisDepth = 0
            for ($dln =0; $dln -lt $definitionLines.count; $dln++) {
                if ($definitionLines[$dln] -like "CREATE*PROCEDURE*"){
                    $procedureStartLine = $dln 

                    if ($dln -ne 0) {
                        $docLines = $definitionLines[0..($dln-1)] -like "--*" | 
                            ForEach-Object -Begin { $firstLine = $true } -Process { 
                                $l = $_.Trim().TrimStart("--").TrimEnd(":")
                                $headerLine = $false
                                if ($l.Trim()  -like "Synopsis*") {
                                    $l = $l -ireplace "Synopsis", ".Synopsis"
                                    $headerLine  = $true
                                }
                                if ($l.Trim()  -like "Description*") {
                                    $l = $l -ireplace "Description", ".Description"
                                    $headerLine  = $true
                                }
                                if ($l.Trim()  -like "Example*") {
                                    $l = $l -ireplace "Example", ".Example"
                                    $headerLine  = $true
                                }
                                if ($l.Trim()  -like "Link*") {
                                    $l = $l -ireplace "Link", ".Link"
                                    $headerLine  = $true
                                }
                                if ($l.Trim()  -like "Notes*") {
                                    $l = $l -ireplace "Notes", ".Notes"
                                    $headerLine  = $true
                                }
                                if ($firstLine) {
                                    if ($headerLine) {
                                        "#" + $l                                   
                                    } else {
                                        "# " + $l                                
                                    }                                    
                                    $firstLine = $false
                                } else {
                                    if ($headerLine) {
                                        "    #" + $l                                
                                    } else {
                                        "    #    " + $l                                
                                    }
                                    
                                }
                                
                            } 
                    } else {
                        $docLines = ""
                    }
                }
                if ($procedureStartLine -ne -1) {
                    foreach ($char in $definitionLines[$dln].ToCharArray()) {
                        if ($char -eq '(') {
                            $paranethesisDepth++
                        } elseif ($char -eq ')') {
                            $paranethesisDepth--
                        }                
                    }
                }
                

                if ($procedureStartLine -ne -1 -and -not $paranethesisDepth) {
                    $trimmedLine = $definitionLines[$dln].Trim()
                    if ($definitionLines[$dln] -like "*AS*" -and $trimmedLine -notlike "@*" -and $trimmedLine -notlike ",*") {
                        $procedureParamEnd = $dln        
                        break
                    }
                }


            }
            
            $procedureStartLine = $procedureStartLine + 1
            $procedureParamEnd = $procedureParamEnd -1 
            $ParamStatement = 
                if ($procedureStartLine -le $procedureParamEnd) {
                    @($definitionLines[$procedureStartLine..$procedureParamEnd]  | ? { $_.Trim() -ne ''})
                } else {
                    @($definitionLines[$procedureParamEnd..$procedureStartLine]  | ? { $_.Trim() -ne ''})
                }
            
            $paramParts = $ParamStatement 
            foreach ($paramStatementItem in $paramParts) {
                $chunks = @($paramStatementItem -split "[ `t]" -ne '')
                if (-not $chunks) { continue } 
                $parameterName = $chunks[0].Trim().Trim(',').Trim('@').Trim(',')
                try {
                    $parameterType = $chunks[1].Trim('[]')    
                } catch {
                    $err = $_ 
                    $null = $null
                }
                if ($chunks -eq '=') {
                    # Default value present    
                    $parametersWithDefaults[$parameterName] = $true
                }

                $comments = ""
                $comments = for ($i = 2; $i -lt $chunks.Count; $i++) {
                    if ($chunks[$i] -eq '--') {
                        $chunks[($i + 1)..($chunks.Count -1)]
                    }
                }


                $ParameterHelp[$parameterName] = $comments -join ' '
                

            }
            $ThisCommandsVerb = ""
            $ThisCommandsNoun = ""
            
            foreach ($v in $verbs) {
                if ($sprocName -like "$v*") {
                    $ThisCommandsVerb = $v
                    $ThisCommandsNoun = $sprocName.Substring($v.Length)
                    break     
                }
            }


            if (-not $ThisCommandsVerb) { $ThisCommandsVerb = "Invoke" } 
            if (-not $ThisCommandsNoun) { $ThisCommandsNoun = $sprocName } 
            $null = $null

            $paramNamesList =@()
            $ParameterChunk = 
                foreach ($param in $parametersByPosition) {
                    $paramName = $param.Parameter_Name.TrimStart("@")
                    $paramNamesList += $paramName
                    $parameterType = 
                        if ($param.Data_Type -like "*varchar*") {
                            'string'
                        } elseif ($param.Data_Type -eq 'bigint') {
                            'bigint'
                        } elseif ($param.Data_Type -eq 'int') {
                            'int'
                        } elseif ($param.Data_Type -eq 'datetime' -or $param.Data_Type -eq 'smalldatetime') {
                            'datetime'
                        } elseif ($param.Data_Type -eq 'bit') { 
                            'switch'
                        } elseif ($param.Data_Type -eq 'tinyint') {
                            'byte' 
                        } elseif ($param.Data_Type -eq 'char') {
                            'char'
                        } elseif ($param.Data_Type -eq 'table type') {
                            'psobject[]'
                            $usesDataTable = $true
                        } else {
                            'psobject'
                        } 
                    
                    $ParameterHelpText = if($ParameterHelp.$paramName) {
                        $lines = $ParameterHelp.$paramName -split "[$([Environment]::newline)]" -ne ''
                        $firstLine = $true 
                        "$(@(foreach ($l in $lines) {
                        if ($firstLine) {
                            "# $l"
                        } else {
                            "    # $l"
                        }
                        
                        }) -join ([Environment]::NewLine))
    "
                    } else {
                    }

                    "$parameterHelpText[Parameter($(if (-not $parametersWithDefaults.$paramName) { "Mandatory=`$true,"} else {})Position=$($param.ordinal_Position - 1),ValueFromPipelineByPropertyName=`$true)]
    [$parameterType]
    `$$paramName"
                }


    $paramBlock = @"
$($ParameterChunk -join ',
    '),
    $(if (-not $EmbedConnectionString) {
    "[Parameter(Mandatory=`$true)]
    [string]
    `$ConnectionString,"
    })
    # The way the data will be outputted.  
    [ValidateSet("Hashtable", "Datatable", "DataSet", "PSObject")]
    [string]
    `$AsA = "PSObject"
"@

    $paramBlock = $paramBlock.Trim("$([Environment]::Newline)").Trim().TrimStart(",")
            $functionDefinition = @"
function $ThisCommandsVerb-$ThisCommandsNoun {
    $($docLines -join ([Environment]::NewLine))
    param(
    $paramBlock    
    )
    begin {
        `$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        `$SqlConnection.ConnectionString = "$(if ($EmbedConnectionString -and $ConnectionStringOrSetting) { "$ConnectionStringOrSetting".Replace('"', '`"')} else { '$connectionString' })"        
        `$SQLConnection.Open()


        $(if ($usesDataTable) {
"
        function ConvertTo-DataTable {
            $((Get-Command -ErrorAction SilentlyContinue ConvertTo-DataTable).Definition)
        }
"            
        })
    }

    process {
        `$SQLCmd = New-Object System.Data.SqlClient.SqlCommand -Property @{
            CommandText = "$($sproc.Name)"
            Connection  = `$sqlConnection
            CommandType = 'StoredProcedure'
        }

        $(if ($paramNamesList) {
        "
        foreach (`$k in '$($paramNamesList -join "','")') {
            if (-not `$k) { continue } 
            if (`$psBoundParameters.`$k) {
                `$v = `$psBoundParameters.`$k
                if (`$v -is [DateTime]) {
                    `$p= `$SqlCmd.Parameters.AddWithValue(`"@`$k`", [Data.SqlDbType]::DateTime)
                    `$p.Value = `$v
                } elseif (`$v -is [Object[]]) {
                    `$p = `$SqlCmd.Parameters.Add(`"@`$k`", [Data.SqlDbType]::Structured)                    
                    if (`$v -is [Data.DataRow]) {
                        `$p.value = `$v          
                    } else {
                        `$p.value = ConvertTo-DataTable `$v          
                    }
                } elseif (`$v -is [switch]) {
                    if (`$v) {
                        `$null = `$SqlCmd.Parameters.AddWithValue(`"@`$k`", '1')
                    } else {
                        `$null = `$SqlCmd.Parameters.AddWithValue(`"@`$k`", '0')
                    }
                } else {
                    `$null = `$SqlCmd.Parameters.AddWithValue(`"@`$k`", `"`$(`$v)`")
                }
            }
            
        }
        "
        })
        

        `$sqlAdapter= New-Object "Data.SqlClient.SqlDataAdapter" `$sqlCmd
        `$dataSet = New-Object Data.DataSet
        `$rowCount = `$sqlAdapter.Fill(`$dataSet)


        if (`$dataSet) {        
            if (`$AsA -eq 'DataSet') {
                `$dataSet
            } elseif (`$AsA -eq 'DataTable') {
                foreach (`$t in `$dataSet.Tables) {
                    ,`$t
                }
            } elseif (`$AsA -eq 'PSObject') {                        
                foreach (`$t in `$dataSet.Tables) {
            
                    foreach (`$r in `$t.Rows) {
                    
                        if (`$r.pstypename) {                    
                            `$r.pstypenames.clear()
                            foreach (`$tn in (`$r.pstypename -split "\|")) {
                                if (`$tn) {
                                    `$r.pstypenames.add(`$tn)
                                }
                            }
                        
                        }
                        `$null = `$r.psobject.properties.Remove("pstypename")
                
                        `$r
                
                    }
                }
            } elseif (`$AsA -eq 'Hashtable') {
                `$avoidProperties = @{}
                foreach (`$pName in 'RowError', 'RowState', 'Table', 'ItemArray', 'HasErrors') {
                    `$avoidProperties[`$pName] = `$true 
                }
                foreach (`$t in `$dataSet.Tables) {
            
                    foreach (`$r in `$t.Rows) {
                    
                        `$out = @{}
                        
                        foreach (`$prop in `$r.psobject.Properties) {
                            if (`$avoidProperties[`$prop.Name]) {
                                continue
                            }
                            `$out[`$prop.Name] = `$prop.Value
                        }                        
                        

                        `$out
                
                    }
                }
            }
        }

    }

    end {
        `$SqlConnection.Close()
    }
}
"@

            $functionDefinition
        }
    }
}