# encoding: ascii
# api: powershell
# title: New-StoredProcFunction
# description: Create functions that wrap chosen stored procedures and surface their input parameters as function parameters. Output parameters are returned in a custom object with a property name for each output parameter.
# version: 0.1
# type: function
# author: Steven Murawski
# license: CC0
# function: Get-StoredProcText
# x-poshcode-id: 1007
# x-archived: 2017-03-11T19:06:27
# x-published: 2010-04-08T15:02:00
#
#
# New-StoredProcFunction.ps1
# Steven Murawski
# http://blog.usepowershell.com
# 04/08/2009
# Example: ./New-StoredProcFunction.ps1 'DataSource=MySqlServer;Database=Northwind;User=AnythingButSa;Password=abc123' sp_createnewcustomer
# Example 'sp_createnewcustomer | ./New-StoredProcFunction.ps1 'DataSource=MySqlServer;Database=Northwind;User=AnythingButSa;Password=abc123'
param($ConnectionString, [String[]]$StoredProc= $null)
BEGIN
{
if ($StoredProc.count -gt 0)
{
$StoredProc | New-StoredProcFunction $ConnectionString
}
function Get-StoredProcText()
{
param ($ProcName, $ConnectionString)
$query = @'
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_BODY = 'SQL' AND ROUTINE_NAME LIKE '$_'
'@
$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$command = New-Object System.Data.SqlClient.SqlCommand $query,$connection
$connection.Open()
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$result = $dataSet.Tables | ForEach-Object {$_.Rows}
$connection.Close()
return $Result.ROUTINE_DEFINITION
}
function Get-FunctionParameter()
{
param ($Text)
[regex]$EndRegex = '\)\s+AS'
[regex]$ParamRegex = '@(?<Parameter>\w+?)\s+(?<DataType>\w+(\(\d+\))*)(,|\s+)*(?<Output>out)*'
$ParamStart = $Text.indexof('(')
$ParamEnd = $EndRegex.Match($text).index
$ParamText = $Text.Substring($ParamStart, ($ParamEnd-$ParamStart))
$RegMatches = $ParamRegex.matches($ParamText)
foreach ($RegMatch in $RegMatches)
{
$Parameter = "" | Select-Object Name, DataType, IsOutput
$Parameter.Name = $RegMatch.Groups[3].value
$Parameter.DataType = $RegMatch.Groups[4].Value
[bool]$Parameter.IsOutput = $RegMatch.Groups[5].Value
$Parameter
}
}
}
PROCESS
{
if ($_ -ne $null)
{
$FunctionName = $_
$StoredProcText = Get-StoredProcText $FunctionName $ConnectionString
$Parameters = Get-FunctionParameter $StoredProcText
[String[]]$InputParamNames = $Parameters | where {-not $_.IsOutput} | ForEach-Object {$_.Name}
[String[]]$OutputParameterNames = $Parameters | Where-Object {$_.IsOutput} | ForEach-Object {$_.Name}
$ScriptText = ' '
if ($InputParamNames.count -gt 0)
{
$OFS = ', $'
$ScriptText += 'param (${0})' -f $InputParamNames
$ScriptText += "`n"
$OFS = ', '
}
$BodyTemplate = @'
$connection = New-Object System.Data.SqlClient.SqlConnection('{0}')
$command = New-Object System.Data.SqlClient.SqlCommand('{1}', $connection)
$command.CommandType = [System.Data.CommandType]::StoredProcedure
'@
$ScriptText += $BodyTemplate -f $ConnectionString, $FunctionName
if ( ($Parameters -ne $null) -or ($Parameters.count -gt 1) )
{
if ($OutputParameterNames.count -gt 0)
{
$ReturnText = ""
$CommandOutput = "" | select $OutputParameterNames
}
#Add the parameters
foreach ($param in $Parameters)
{
if ($param.datatype -match '(?<type>\w+)\((?<nbr>\d+)\)')
{
$ParamTemplate = '$command.Parameters.Add("@{0}", "{1}", {2}) | out-null '
$ScriptText += "`n"
$ScriptText += $ParamTemplate -f $param.name, $matches.type, $matches.nbr
}
else
{
$ParamTemplate = '$command.Parameters.Add("@{0}", "{1}") | out-null '
$ScriptText += "`n"
$ScriptText += $ParamTemplate -f $param.name, $param.datatype
}
if ($param.IsOutput)
{
$ScriptText += "`n"
$ScriptText += '$command.Parameters["@{0}"].Direction = [System.Data.ParameterDirection]::Output ' -f $param.Name
$ReturnText += "`n"
$ReturnText += '$CommandOutput.{0} = $command.Parameters["@{0}"].Value' -f $param.name
}
else
{
$ScriptText += "`n"
$ScriptText += '$command.Parameters["@{0}"].Value = ${0} ' -f $param.name
}
}
}
$ScriptText += "`n"
$ScriptText += @'
$connection.Open() | out-null
$command.ExecuteNonQuery() | out-null
'@
if ($OutputParameterNames.count -gt 0)
{
$ScriptText += $ReturnText
}
$ScriptText += @'
$connection.Close() | out-null
return $CommandOutput
'@
#$ScriptText
Set-Item -Path function:global:$FunctionName -Value $scripttext
}
}