PoshCode Logo PowerShell Code Repository

New-StoredProcFunction (modification of post by Steven Murawski view diff)
diff | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1011"></script>download | new post

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.

  1. # New-StoredProcFunction.ps1
  2. # Steven Murawski
  3. # http://blog.usepowershell.com
  4. # 04/08/2009
  5. # Replaced the parsing of the stored procedure text and use Information_Schema.Parameters to get the parameter information
  6. # Thanks to Chad Miller for the idea.
  7.  
  8. # Example: ./New-StoredProcFunction.ps1 'Data Source=MySqlServer;Database=Northwind;User=AnythingButSa;Password=abc123' sp_createnewcustomer
  9. # Example 'sp_createnewcustomer | ./New-StoredProcFunction.ps1 'Data Source=MySqlServer;Database=Northwind;User=AnythingButSa;Password=abc123'
  10.  
  11. param($ConnectionString, [String[]]$StoredProc= $null)
  12. BEGIN
  13. {
  14.         if ($StoredProc.count -gt 0)
  15.         {
  16.                 $StoredProc | ./New-StoredProcFunction $ConnectionString
  17.         }
  18.         function Invoke-SQLQuery()
  19.         {
  20.                 param ($ConnectionString, $Query)
  21.                 $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
  22.                 $command = New-Object System.Data.SqlClient.SqlCommand $query,$connection
  23.                 $connection.Open()
  24.                 $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
  25.                 $dataset = New-Object System.Data.DataSet
  26.                 [void] $adapter.Fill($dataSet)
  27.                 $connection.Close()
  28.                 $dataSet.Tables | ForEach-Object {$_.Rows}
  29.         }
  30.        
  31.         function Get-FunctionParameter()
  32.         {
  33.                 param($FunctionName, $ConnectionString)
  34.                 $query = @"
  35. SELECT parameter_Name, data_type, character_maximum_length, parameter_mode
  36. FROM INFORMATION_SCHEMA.Parameters
  37. WHERE specific_NAME LIKE '$FunctionName'
  38. "@
  39.                 $Rows = Invoke-SQLQuery $ConnectionString $Query
  40.  
  41.                 foreach ($Row in $Rows)
  42.                 {
  43.                         $Parameter = "" | Select-Object Name, DataType, Length, IsOutput
  44.                         $Parameter.Name = $row.parameter_Name
  45.                         $Parameter.DataType = $Row.data_type
  46.                         $Parameter.Length = $Row.character_maximum_length
  47.                         $Parameter.IsOutput = if ($Row.parameter_mode -eq 'INOUT'){$true} else {$false}
  48.                         $Parameter
  49.                 }
  50.         }
  51. }
  52. PROCESS
  53. {
  54.         if ($_ -ne $null)
  55.         {
  56.                 $FunctionName = $_
  57.                 $Parameters = Get-FunctionParameter $FunctionName $ConnectionString
  58.                
  59.                 [String[]]$InputParamNames = $Parameters | where {-not $_.IsOutput} | ForEach-Object {$_.Name -replace '@' }
  60.                 [String[]]$OutputParameterNames = $Parameters | Where-Object {$_.IsOutput} | ForEach-Object {$_.Name -replace '@'}
  61.                
  62.                 $ScriptText = ' '
  63.                
  64.                 if ($InputParamNames.count -gt 0)
  65.                 {
  66.                         $OFS = ', $'
  67.                         $ScriptText += 'param (${0})' -f $InputParamNames
  68.                         $ScriptText += "`n"
  69.                         $OFS = ', '
  70.                 }
  71.                
  72.                 $BodyTemplate = @'
  73. $connection = New-Object System.Data.SqlClient.SqlConnection('{0}')
  74. $command = New-Object System.Data.SqlClient.SqlCommand('{1}', $connection)
  75. $command.CommandType = [System.Data.CommandType]::StoredProcedure
  76.  
  77. '@
  78.                 $ScriptText += $BodyTemplate -f $ConnectionString, $FunctionName
  79.                 if ( ($Parameters -ne $null) -or ($Parameters.count -gt 1) )
  80.                 {
  81.                        
  82.                         if ($OutputParameterNames.count -gt 0)
  83.                         {
  84.                                 $ReturnText = ""
  85.                                 $CommandOutput = "" | select $OutputParameterNames
  86.                         }
  87.                         #Add the parameters    
  88.                         foreach ($param in $Parameters)
  89.                         {
  90.                                 if ($param.length -isnot [DBNull])
  91.                                 {
  92.                                         $ParamTemplate = '$command.Parameters.Add("{0}", "{1}", {2})  | out-null '
  93.                                         $ScriptText += "`n"
  94.                                         $ScriptText += $ParamTemplate -f $param.name, $param.datatype, $param.length
  95.                                 }
  96.                                 else
  97.                                 {
  98.                                         $ParamTemplate = '$command.Parameters.Add("{0}", "{1}")  | out-null '
  99.                                         $ScriptText += "`n"
  100.                                         $ScriptText += $ParamTemplate -f $param.name, $param.datatype
  101.                                 }
  102.                                
  103.                                 if ($param.IsOutput)
  104.                                 {
  105.                                         $ScriptText += "`n"
  106.                                         $ScriptText += '$command.Parameters["{0}"].Direction = [System.Data.ParameterDirection]::Output ' -f $param.Name
  107.                                         $ReturnText += "`n"
  108.                                         $ReturnText += '$CommandOutput.{1} = $command.Parameters["{0}"].Value' -f $param.name, ($param.name -replace '@')
  109.                                 }
  110.                                 else
  111.                                 {
  112.                                         $ScriptText += "`n"
  113.                                         $ScriptText += '$command.Parameters["{0}"].Value = ${1} ' -f $param.name, ($param.name -replace '@')
  114.                                 }
  115.                         }                              
  116.                 }
  117.                
  118.                 $ScriptText += "`n"
  119.                 $ScriptText += @'
  120. $connection.Open()  | out-null
  121. $command.ExecuteNonQuery() | out-null
  122.  
  123. '@     
  124.                 if ($OutputParameterNames.count -gt 0)
  125.                 {
  126.                         $ScriptText += $ReturnText
  127.                 }
  128.                
  129.                 $ScriptText += @'
  130.                
  131. $connection.Close() | out-null
  132. return $CommandOutput
  133. '@
  134.                
  135.                 #$ScriptText
  136.                 Set-Item -Path function:global:$FunctionName -Value $scripttext
  137.         }
  138. }

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me