{"id":1355,"date":"2012-06-19T00:00:00","date_gmt":"2012-06-19T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-posh-dba-the-attributes-of-advanced-functions\/"},"modified":"2021-08-24T13:40:10","modified_gmt":"2021-08-24T13:40:10","slug":"the-posh-dba-the-attributes-of-advanced-functions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/the-posh-dba-the-attributes-of-advanced-functions\/","title":{"rendered":"The PoSh DBA &#8211; The Attributes of Advanced Functions"},"content":{"rendered":"<p class=\"start\">PowerShell advanced functions provide some powerful magic. In <a href=\"http:\/\/www.simple-talk.com\/sql\/sql-tools\/the-posh-dba-grown-up-powershell-functions\/\">the previous article<\/a> we covered some &#8220;features&#8221; that can help us to write better code, and now we are going to focus in on the magic of those features that are to do with the parameters that we pass to advanced functions.<\/p>\n<p>We already know that, when we put the <code>[CmdletBinding()]<\/code> attribute in a function, it changes it into an advanced function, thereby allowing options that you don&#8217;t have in normal functions &#8211; see <a href=\"http:\/\/www.simple-talk.com\/sql\/sql-tools\/the-posh-dba-grown-up-powershell-functions\/\">The PoSh DBA: Grown-Up PowerShell Functions<\/a>. What I didn&#8217;t emphasize then is that all magic comes at a price. In this case, the price is that you must write code that uses the magic correctly; you must be more precise and cautious, just like any magician. To start with, the function&#8217;s parameters should be formally defined. We lose something by this: When we write a function without the <code>[CmdletBinding()]<\/code> attribute, we can be less strict, and pass more parameters than we have defined in the function, because the <code>$args<\/code> variable stores them.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">function ILoveInformalParameters { \r\n        param ($IamFirst,$IamSecond)\r\n        Write-Host \"I am The First Parameter : $($IamFirst)\"\r\n        Write-Host \"I am The Second Parameter : $($IamSecond)\"\r\n        Write-Host \"And I am The Rest : $($args)\"\r\n        \r\n    }<\/pre>\n<p>If we now call our informal function :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">ILoveInformalParameters ObiWanKenoby Yoda DarthVader DarthSidious\r\n\r\n\r\nI am The First Parameter : ObiWanKenoby\r\nI am The Second Parameter : Yoda\r\nAnd I am The Rest : DarthVader DarthSidious\r\n<\/pre>\n<p>But if, without due thought, we add just the <code>[CmdletBinding()]<\/code> attribute to this without any option&#8230;<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">function ILoveInformalParameters { \r\n       [cmdletbinding()]\r\n       param ($IamFirst,$IamSecond)\r\n       .......    \r\n   }<\/pre>\n<p>&#8230;And try to run it using more parameters than we&#8217;ve declared&#8230;<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">ILoveInformalParameters ObiWanKenoby Yoda DarthVader DarthSidious \r\n\r\nILoveInformalParameters : A positional parameter cannot be found that accepts argument 'DarthVader'....... <\/pre>\n<p>Yes. It is an error in the positional parameters. You&#8217;ve just dropped the rabbit from the hat.<\/p>\n<h3>The CmdletBinding arguments<\/h3>\n<p>There are 3 arguments that are concerned with the parameters that can be passed to PowerShell functions. These are :<\/p>\n<ul>\n<li><code>Supports<\/code><code>ShouldProcess<\/code>\n<ul>\n<li>By setting this property to <code>$True <\/code>(the default is <code>$False<\/code>), you are asking the function to be able to support the <code>ShouldProcess <\/code>method. By doing this, you allow the function to implement the <code>Whatif <\/code>common parameter that displays what the function would do, but without actually performing the operation . The <code>Confirm <\/code>option is also enabled by this property.<\/li>\n<\/ul>\n<\/li>\n<li><code>ConfirmImpact<\/code><code><\/code>\n<ul>\n<li>In this argument, you will configure the impact level (&#8220;HIGH&#8221;, &#8220;MEDIUM&#8221; or &#8220;LOW&#8221;) at which the action that you are performing in your function should be preceded by a prompted confirmation request such as &#8220;Are you sure about this?&#8221;. This prompt would only be displayed if the impact level in the argument is equal to, or higher than, the value of the global <code>$<\/code><code>ConfirmPreference  <\/code>shell variable (which defaults to &#8216;HIGH). The default value of the argument is &#8216;Medium&#8217; and, of course, this option only make sense if you specify <code>SupportsShouldProcess <\/code>option as well.<\/li>\n<\/ul>\n<\/li>\n<li><code>DefaultParameterSet<\/code><code>Name<\/code><code><\/code>\n<ul>\n<li>The <code>DefaultParameterSetName <\/code>argument specifies the name of the parameter set that Windows PowerShell will attempt to use when it cannot determine which parameter set to use. (for more information, use <code>about_Functions_CmdletBindingAttribute<\/code>)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>So how should we code these?<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">   Function NowIamDoingRight {\r\n       [cmdletbinding(\r\n           SupportsShouldProcess=&lt;Boolean $True or $False&gt;\r\n           ConfirmImpact=&lt;String 'None' 'Low' 'Medium' 'High'&gt;\r\n           DefaultParameterSetName &lt;String ParameterName&gt;\r\n       )]<\/pre>\n<p>Both <code>SupportsShouldProcess <\/code>and <code>ConfirmImpact <\/code>are part of the advanced functions confirmation methods, or as I like to call them, the &#8220;License to Kill&#8221; methods.<\/p>\n<h2>The License to Kill Options &#8211; Performing Highly Dangerous Operations<\/h2>\n<h3>SupportsShouldProcess Argument<\/h3>\n<h4>First License to Kill parameter &#8211; &#8220;My Name is If&#8230; -WHATIF&#8221;<\/h4>\n<p>We know that there is no such thing as a small mistake in a DBA&#8217;s job. When it happens, it is always a mistake of HUGE proportions.<\/p>\n<p>Fortunately we can use the <code>-<\/code><code>whatif <\/code>common parameter to help reduce mistakes, if our functions support it. This does nothing more than to show you what it would do without actually running the command in the function you are executing. It is something like asking the function: &#8220;What am I about to do?&#8221; rather than asking &#8220;Holy Saints. Do I have a backup?&#8221; after the function does something unexpected.<\/p>\n<p>To illustrate this, we&#8217;ll create two advanced functions, <code>Get<\/code><code>-<\/code><code>MSSQLTable <\/code>and <code>GET-<\/code><code>MSSQLProcedure<\/code>. The former outputs the SQL Server table objects that you specify, and the latter outputs stored procedure objects.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\"> function Get-MSSQLTable { \r\n   [CmdletBinding()] \r\n  \r\n     param( \r\n     [Parameter(Position=0, Mandatory=$true)] [String]$Server, \r\n     [Parameter(Position=1, Mandatory=$true)] [String]$Database, \r\n     [Parameter(Position=2, Mandatory=$false)] [String]$TableName \r\n     ) \r\n  \r\n     begin { \r\n  \r\n         [reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\") | out-null  \r\n          $ServerName=New-Object \"Microsoft.SqlServer.Management.Smo.Server\" $server \r\n      }     \r\n      Process { \r\n       \r\n          $ServerName.Databases | where {$_.name -eq \"$Database\" } | % { \r\n                \r\n              foreach ($table in $_.tables) { \r\n                     if ($tableName) { \r\n                     $tables = $table | where {$_.name -like \"$tablename*\"} \r\n                     } else { \r\n                     $tables = $table \r\n                    } \r\n   \r\n                    Write-Output $tables \r\n              } \r\n   \r\n          } \r\n   \r\n     } \r\n   \r\n  }    \r\n   \r\n  function Get-MSSQLStoredProcedure { \r\n   \r\n      [CmdletBinding()] \r\n   \r\n      param( \r\n          [Parameter(Position=0, Mandatory=$true)] [String]$Server, \r\n          [Parameter(Position=1, Mandatory=$true)] [String]$Database, \r\n          [Parameter(Position=2, Mandatory=$false)] [String]$ProcedureName \r\n   \r\n      ) \r\n      Begin { \r\n          [reflection.assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Smo\") | out-null  \r\n         $ServerName=New-Object \"Microsoft.SqlServer.Management.Smo.Server\" $server \r\n     }     \r\n     Process { \r\n  \r\n         $ServerName.Databases | where {$_.name -eq \"$Database\" } | % { \r\n         foreach ($Proc in $_.storedprocedures) { \r\n             if ($ProcedureName) { \r\n                 $procs = $Proc | where {$_.name -like \"$ProcedureName*\"} \r\n             } else { \r\n                 $procs = $proc \r\n             } \r\n             Write-Output $procs \r\n            } \r\n         }     \r\n     } \r\n }<\/pre>\n<p>There is not much that to say about them. They are simple functions.<\/p>\n<p>Now, we will write a more dangerous function that drops the objects. Now, you&#8217;ll probably have noticed that this is not a pair of related functions; one to drop Tables and other to drop Procedures. This is a function to drop a database object no matter what it is, just as long as the object has the drop method, or will accept being dropped.<\/p>\n<p>The first name that I thought of was <code>Drop-<\/code><code>SQLObject, <\/code>but Drop it is not an approved PowerShell verb, so I replaced the &#8216;Drop&#8217; verb with something similar and approved. <code>Remove-<\/code><code>SQLObject<\/code>. (You can get a list of approved verbs by using the <code>Get-Verb <\/code>cmdlet).<\/p>\n<p>My first step towards using this function was, of course, to enable the <code>-<\/code><code>whatif<\/code>and <code>-<\/code><code>confirm <\/code>parameters by adding <code>SupportsShouldProcess<\/code><code>=$true <\/code>in the <code>CmdletBinding <\/code>attribute:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">function Remove-SQLObject\r\n   {\r\n       [CmdletBinding(SupportsShouldProcess=$true)] <\/pre>\n<p>And I created it so that it receives an SMO object as a parameter and drops the object inside a process block, but I encountered a problem with <code>Foreach <\/code>enumerators in the Pipeline, and to retrieve the Database Objects (tables, stored procedures and so on). In SMO you need to use enumerators, as I&#8217;ve shown in the &#8220;<code>foreach<\/code><code>$<\/code><code>proc<\/code><code>in $_.<\/code><code>StoredProcedures<\/code>&#8221; and &#8220;<code>foreach<\/code><code>$table in $_tables<\/code>&#8221; in the functions above. This will cause problems if you drop an object in the enumeration. This script won&#8217;t work.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">function Remove-SQLObject\r\n   {\r\n       [CmdletBinding(SupportsShouldProcess=$true)] \r\n       param(\r\n           [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]\r\n           [ValidateScript({$_.GetType().Namespace -like \"Microsoft.SqlServer.Management.Smo*\" -and ($_.gettype().getinterfaces()|select -expand name) -contains 'idroppable'})] $SmoObject\r\n       )\r\n       \r\n       Process {\r\n           SmoObject.Drop()\r\n       }\r\n   }    \r\n\r\n\r\n<\/pre>\n<p>Basically, Enumerators can be used to read the data in the collection, but they cannot be used to modify the underlying collection. If you try to run the script, you get an error:<\/p>\n<pre class=\"theme:powershell-output lang:ps\">An error occurred while enumerating through a collection: Collection was modified; enumeration operation may not execute.<\/pre>\n<p>To work around to this limitation, I used an Array <code>$<\/code><code>MyObject <\/code>and populated it completely in the process block: In the End block, I effectively dropped the items of this object one by one. The final code (before I implemented -whatif processing) is:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">  \r\n\r\nFunction Remove-SQLObject\r\n   {\r\n       [CmdletBinding(SupportsShouldProcess=$true)] \r\n       param(\r\n         [Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)]\r\n         [ValidateScript({$_.GetType().Namespace -like \"Microsoft.SqlServer.Management.Smo*\" -and ($_.gettype().getinterfaces()|select -expand name) -contains 'idroppable'})] $SmoObject\r\n       )\r\n       \r\n           begin {\r\n                   $MyObject = @()\r\n           }    \r\n    \r\n           process {\r\n                   if ( $PSCmdlet.ShouldProcess(\"$($SmoObject.GetType().name) - $($SmoObject)\") ) { \r\n                           foreach ($drop in $SmoObject) {\r\n                               $MyObject += $drop\r\n                               Write-verbose \"Dropping $($SmoObject.GetType().name) - $($SmoObject)\"\r\n                           }\r\n                       }    \r\n                   }    \r\n           end {    \r\n                   $count = $MyObject.count \r\n                   for ($i = 0; $i -lt $count; $i++) {\r\n                       $MyObject[$i].Drop()\r\n    \r\n                   }\r\n    \r\n           }    \r\n    \r\n   }<\/pre>\n<p>The code that will eventually allow the <code>-<\/code><code>whatif <\/code>argument to work properly is in the line 14 :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">    if ( $PSCmdlet.ShouldProcess(\"$($SmoObject.GetType().name) - $($SmoObject)\") ) {...} <\/pre>\n<p>So if the&#8217; <code>-<\/code><code>whatif' <\/code>parameter is supplied, then the parameter passed to the<code><\/code><code>ShouldProcess <\/code>method will be displayed, but the code in the scriptblock that follows the IF statement won&#8217;t be executed. Otherwise, the scriptblock after the IF statement will be executed. . In this case I am displaying the type of the object (table, stored procedure&#8230; etc.) and the Schema and Name of the object so you will be clear about what will be executed if you were to leave out the -Whatif.<\/p>\n<p>In my case, because I had to call the drop method on the database object in the end block and not in the Process block, you might think, as I did, that I would need a variable to hold a flag as to whether I&#8217;d passed -whatif or not: Maybe Something like this?<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">   begin {\r\n                   $MyObject = @()\r\n                   $whatif = $true\r\n           }    \r\n    \r\n           process {\r\n                   if ( $PSCmdlet.ShouldProcess(\"$($SmoObject.GetType().name) - $($SmoObject)\") ) { \r\n                           $whatif = $False\r\n                           foreach ($drop in $SmoObject) {\r\n                               $MyObject += $drop\r\n                               Write-verbose \"Dropping $($SmoObject.GetType().name) - $($SmoObject)\"\r\n                          }\r\n                      }    \r\n                  }    \r\n          end {    \r\n                  if ($whatif) {\r\n                      $count = $MyObject.count \r\n                      for ($i = 0; $i -lt $count; $i++) {\r\n                          $MyObject[$i].Drop()\r\n                      }        <\/pre>\n<p>Wrong. Just by passing the -whatif as a parameter, no operation or method will be performed in all the code of the function. I do not need to worry.<\/p>\n<p>The function requires that an SMO object is passed to it. This required validation. I therefore had to add this rather daunting line that checks that the parameter is an SMO object and that it is droppable:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">[ValidateScript({$_.GetType().Namespace -like \"Microsoft.SqlServer.Management.Smo*\" -and ($_.gettype().getinterfaces()|select -expand name) -contains 'idroppable'})] $SmoObject<\/pre>\n<p>Don&#8217;t worry, I will cover the parameters&#8217; metadata, and the <code>ValidateScript <\/code>attribute, in the next article. For now it is good to know that the<code> $SmoObject<\/code> parameter accepts only SMO Objects that can be dropped.<\/p>\n<p>Now it is time to see the magic:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\"> \r\n   create table ThePoshDBA_1 (id int)\r\n   go\r\n   create table ThePoshDBA_2 (id int)\r\n   go\r\n   create table ThePoshDBA_3 (id int)\r\n   go\r\n   create table ThePoshDBA_4 (id int)\r\n   go\r\n   create table ThePoshDBA_5 (id int)\r\n   go\r\n   create table ThePoshDBA_6 (id int)\r\n\r\nGet-MSSQLTable -Server . -Database \"ThePoshDBA\" -TableName \"ThePoshDba*\" | Remove-SQLObject -whatif<\/pre>\n<p>Do not be scared if all that you see displayed is &#8230;.<\/p>\n<pre class=\"theme:powershell-output lang:ps\">What if: Performing operation \"Remove-SQLObject\" on Target \"Table - [dbo].[ThePoshDBA_1]\".\r\nWhat if: Performing operation \"Remove-SQLObject\" on Target \"Table - [dbo].[ThePoshDBA_2]\".\r\nWhat if: Performing operation \"Remove-SQLObject\" on Target \"Table - [dbo].[ThePoshDBA_3]\".\r\nWhat if: Performing operation \"Remove-SQLObject\" on Target \"Table - [dbo].[ThePoshDBA_4]\".\r\nWhat if: Performing operation \"Remove-SQLObject\" on Target \"Table - [dbo].[ThePoshDBA_5]\".\r\nWhat if: Performing operation \"Remove-SQLObject\" on Target \"Table - [dbo].[ThePoshDBA_6]\".<\/pre>\n<p>&#8230; because your tables still will be in the Database ThePoshDBA. No operation was performed; it is just saying to you what it would do.<\/p>\n<p>And of course to effectively drop the tables, you need only remove the <code>-<\/code><code>whatif <\/code>parameter.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-MSSQLTable -Server . -Database \"ThePoshDBA\" -TableName \"ThePoshDba*\" | Remove-SQLObject<\/pre>\n<p>Bear in mind that the problem I faced by using the <code>Foreach <\/code>enumerator when deleting the objects is a particular problem for this function, and it is unlikely that you would ever need to write your -whatif implementation the same way, that is, by using the begin and end blocks of the function. The usual way to implement <code>-<\/code><code>whatif <\/code>processing is:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">   .....\r\n   process {\r\n       if ( $PSCmdlet.ShouldProcess(\"Something In Here to Display\") ) { \r\n           Perform the action\r\n           ......\r\n       }    \r\n   }    \r\n   .....<\/pre>\n<p>In fact, you can take advantage of using Enumerators. Let&#8217;s go a bit off-topic to look at a nice trick. Have you noticed that, for the cmdlet <code>get-<\/code><code>eventlog, <\/code>you can either use an array as the parameter for the Servers or pass it in the pipeline? In this case when a parameter is defined as string[] you can do this &#8230;<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-EventLog Application -computer (Get-Content Servers.txt)<\/pre>\n<p>&#8230; instead of this &#8230;<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-content  Servers.txt | foreach {Get-EventLog Application -computer $_}<\/pre>\n<p>You can implement the same functionality in your Function. Let&#8217;s see the part of the code of Chad Miller&#8217;s Get-SqlWmi; (thanks to him for teaching me this trick). This function uses the WMI <code>ManagedComputer<\/code><code><\/code> cmdlet to get port, instance and service account WMI information for all SQL instances on a computer.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">function Get-SqlWmi\r\n    {\r\n        [CmdletBinding()]\r\n        param(\r\n        [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)]\r\n       [ValidateNotNullorEmpty()]\r\n       [string[]]$ComputerName\r\n       )\r\n    \r\n       #Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer only works on SQL 2005 and higher. If we fail to gather info at least output\r\n       #null values and computername for 2000 servers in catch block\r\n       BEGIN {}\r\n       PROCESS {\r\n           foreach ($computer in $computername) {\r\n               try {\r\n                   $wmi = new-object \"Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer\" $Computer -ErrorAction 'Stop'\r\n                   \r\n   ........\r\n\r\n<\/pre>\n<p>By using the <code>foreach <\/code>enumerator , the parameter<code> $Computername as string[]<\/code><code><\/code>and of course understanding the <code>ValueFromPipelineByName <\/code>and <code>ValueFromPipelineByValue<\/code>, you can simulate the same thing that <code>Get-<\/code><code>EventLog <\/code>does.<\/p>\n<p>You can call the <code>Get-<\/code><code>SQLWmi <\/code>by passing in an array directly :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-sqlwmi (get-content names.txt)<\/pre>\n<p>&#8230;or through the pipeline by value :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\"> Get-content names.txt | Get-sqlwmi <\/pre>\n<p>&#8230;or by through the pipeline name :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Invoke-sqlcmd -ServerInstance \"yourServer\" -database \"YourDb\" -query \"Select server_name AS 'ComputerName' FROM ServersTable\" | Get-SqlWmi <\/pre>\n<p>Don&#8217;t worry my friends, in the next part of these series, we will cover the <code>ValueFromPipeline <\/code>property and the related attributes.<\/p>\n<p>But, back to our subject, according to the Don Jones, there is something else to consider as well:<\/p>\n<p class=\"indented\"><em>&#8220;Don&#8217;t forget that <\/em><code>-confirm<\/code><em> and <\/em><code>-whatif<\/code><em> are also <\/em><code>passthrough <\/code><em>parameters. That is, if your function declares <\/em><code>SupportsShouldProcess=$True,<\/code><em> and someone runs the function with <\/em><code>-whatif<\/code><em> or <\/em><code>-confirm<\/code><em>, those will be passed to any other cmdlets WITHIN YOUR FUNCTION that also support <\/em><code>-confirm<\/code><em> and <\/em><code>-whatif<\/code><em>. Since your change isn&#8217;t being made by a cmdlet, you can&#8217;t take advantage of that, so the If construct and using <\/em><code>$psCmdlet.ShouldProcess()<\/code><em> is indeed the right way to go&#8221;.<\/em><\/p>\n<h4>Second License To Kill parameter- &#8220;Sir, you asked for a martini shaken not stirred. Do you -CONFIRM ?&#8221;<\/h4>\n<p>Sometimes even if we really are sure about an action, why not ask again? For this case we have the common parameter <code>-<\/code><code>confirm<\/code>and as it is also enabled by <code>SupportsShouldProcess <\/code>we can use it as well.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-MSSQLTable -Server . -Database \"ThePoshDBA\" -TableName \"ThePoshDba*\" | Remove-SQLObject -verbose -confirm<\/pre>\n<p><code><\/code><\/p>\n<p>And a prompt will be displayed to allow you to confirm your murder. No problems, we have license to kill.<\/p>\n<h3>ConfirmImpact Argument<\/h3>\n<p>To finish the &#8216;License To Kill&#8217; options, there are some actions that are so dangerous that I want to prompt the user with the same -confirm question, but I want to do it every time that the function is called. I don&#8217;t want the user to be obliged to explicitly pass the -confirm parameter.<\/p>\n<p>In this case, we can use the <code>ConfirmImpact <\/code>option just adding in the <code>[CmdletBinding()]<\/code> code :<\/p>\n<pre class=\"theme:powershell-output lang:ps\">[CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact=LevelOfImpact)] <\/pre>\n<p>As we see in the beginning of this article, The default is &#8216; Medium&#8217; but there are a choice of four impact levels ,&#8217;None&#8217;, &#8216;Low&#8217;, &#8216;Medium&#8217; or &#8216;High&#8217;. We&#8217;ll code our function to specify &#8216;High&#8217;:<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">[CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='High')] <\/pre>\n<p>This operation can be so potentially catastrophic that I am declaring that the impact is HIGH. Every time that I call the function I&#8217;ll be asked to confirm the operation.<\/p>\n<p>&#8216;Ha Laerte&#8217;, I hear you say, &#8216;this function is generic. I need this confirmation all the time, but not for all objects. I need confirmation for my tables, but not for the stored procedures. Is there some way for me to suppress this confirmation?&#8217;<\/p>\n<p>&#8216;Sure!&#8217;, I reply, &#8216;just pass the parameter <code>-confirm:$false<\/code>&#8216;.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Get-MSSQLStoredProcedure -Server . -Database \"ThePoshDBA\" -ProcedureName \"Proc_ThePoshDba*\" | Remove-SQLObject -verbose -confirm:$<\/pre>\n<pre class=\"theme:powershell-output lang:ps\">VERBOSE: Dropping StoredProcedure - [dbo].[Proc_ThePoshDBA_1] \r\nVERBOSE: Dropping StoredProcedure - [dbo].[Proc_ThePoshDBA_2] \r\nVERBOSE: Dropping StoredProcedure - [dbo].[Proc_ThePoshDBA_3] \r\n<\/pre>\n<p>Technically we can create a new parameter <code>-force <\/code>to suppress this prompt, but again, in the words of the Don Jones:<\/p>\n<p class=\"indented\"><em>&#8220;<\/em><em>-Force is usually used to override a permissions problem, read-only, or something else; using it to suppress auto-confirm is a bit nonstandard, but it is comprehensible.<\/em><em>&#8220;<\/em><\/p>\n<p>Colleagues, we are about the business of learning how to write advanced code in PowerShell. It is time to be more than just &#8220;comprehensible&#8221;; right ?<\/p>\n<h3><code>DefaultParameterSetName Argument<\/code><\/h3>\n<p>There are cmdlets that cannot be called with two or more parameters from different Parameter Sets at the same time. Some parameters depend on others in the same set. I confess that this concept was a bit difficult to understand for me, but after a talk with the Jedi Council (Chad, Ravi, Shay, Jeffery), the apple fell on my head. I felt the gravity of the situation.<\/p>\n<p>Yes. It is a privilege to me have the power to invoke the Jedi Cmdlets as Invoke-ChadMiller, Invoke-Ravikanth, Invoke-ShayLevy, Invoke-JefferyHicks, Invoke-Boe, and so on.<\/p>\n<p><strong>(<\/strong><em><strong>Ed:<\/strong> Focus Laerte, let&#8217;s get back to an example<\/em> )<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Function ImAGrownUpManNow {\r\n   \r\n      [cmdletbinding(\r\n          SupportsShouldProcess=.....\r\n          ConfirmImpact=....\r\n          DefaultParameterSet = \"GroupSet1\"\r\n      )]\r\n      param (\r\n          [Parameter(ParameterSet=\"GroupSet1\",....) [String] $P1,\r\n          [Parameter(ParameterSet=\"GroupSet1\",....) [String] $P2,\r\n          [Parameter(ParameterSet=\"GroupSet2\",...) [String] $P3,\r\n          [Parameter(ParameterSet=\"GroupSet2\",...) [String] $P4,\r\n          \r\n      )    \r\n  ....\r\n  }<\/pre>\n<p>The <code>DefaultParameterSetName <\/code>in the <code>GroupSet1t <\/code>means that if you have some positional parameters with default values, and the user doesn&#8217;t specify any parameter names, then the default set will be used.<\/p>\n<p>The <code>DefaultParameterSetName <\/code>is bound to the <code>ParameterSetName <\/code>option that you can specify in the parameter set metadata. There is no sense in using the one without the other.<\/p>\n<p>As you can see, the parameters <code>P1 <\/code>and <code>P2 <\/code>are from <code>GroupSet1<\/code><code>,<\/code>and<code> P3 <\/code>and<code> P4 <\/code>are from <code>GroupSet2<\/code>. This means that <code>P1 <\/code>or <code> P2 <\/code>cannot be passed together with <code>P3 <\/code>or <code>P4<\/code>; but all of them are in the same Function.<\/p>\n<p>I guess that you know the <code>Get-<\/code><code>Process <\/code>cmdlet of course. I can get a process by <code>ID <\/code>OR by <code>Name<\/code>, not both. Without using multiple distinct parameter sets, you&#8217;d need something like <code>Get<\/code><code>-<\/code><code>ProcessByID <\/code>and<code> Get-ProcessByName <\/code>&#8211; Thanks to Shay Levy for this example.<\/p>\n<p>In my case, if I Try &#8230;<\/p>\n<pre class=\"theme:powershell-output lang:ps\">Get-Process -id 1916 <\/pre>\n<pre class=\"theme:powershell-output lang:ps\">Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName \r\n------- ------ ----- ----- ----- ------ -- ----------- \r\n63 3 772 2812 33 0,02 1916 armsvc \r\n<\/pre>\n<p>&#8230;or &#8230;<\/p>\n<pre class=\"theme:powershell-output lang:ps\">Get-Process -Name armsvc <\/pre>\n<pre class=\"theme:powershell-output lang:ps\">Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName \r\n------- ------ ----- ----- ----- ------ -- ----------- \r\n63 3 772 2812 33 0,02 1916 armsvc \r\n\r\n<\/pre>\n<p>But If I try &#8230;<\/p>\n<pre class=\"theme:powershell-output lang:ps\">Get-Process -Id 1916 -Name armsvc <\/pre>\n<pre class=\"theme:powershell-output lang:ps\">Get-Process : Parameter set cannot be resolved using the specified named parameters...... \r\n<\/pre>\n<p>Other great examples are in the <a href=\"http:\/\/sqlpsx.codeplex.com\/\"><code>SQLPSX - Invoke-SQLRestore<\/code><\/a>. Take a look at the code : &#8211; The parts with &#8220;&#8230;&#8221; was truncated by me to fit on the page.<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">  function Invoke-SqlRestore\r\n  {\r\n      param(\r\n      [CmdletBinding(DefaultParametersetName=\"Restore\")]\r\n      [Parameter(Position=0, Mandatory=$true)] $sqlserver,\r\n      [Parameter(ParameterSetName=\"Restore\",...)] [string]$dbname,\r\n      [Parameter(Position=2, Mandatory=$true)] [string]$filepath,\r\n      [Parameter(ParameterSetName=\"Restore\",...)] [...]$action='Database',\r\n      [Parameter(ParameterSetName=\"Restore\",...)] [string]$stopat,\r\n      [Parameter(ParameterSetName=\"Restore\",...)] [hashtable]$relocatefiles,\r\n      [Parameter(ParameterSetName=\"Restore\",...)] [switch]$force,\r\n      [Parameter(ParameterSetName=\"Restore\",...)] [switch]$norecovery,\r\n      [Parameter(ParameterSetName=\"Restore\",...)] [switch]$keepreplication,\r\n      [Parameter(ParameterSetName=\"FileList\",...)] [switch]$FileListOnly\r\n      )<\/pre>\n<p>The parameters <code>$dbname<\/code>, <code>$<\/code><code>action<\/code>, <code>$stopat<\/code>, <code>$realocatefiles<\/code>, <code>$force<\/code>, <code>$norecovery<\/code>, and <code>$keepreplication <\/code>are parts of the Restore <code>ParameterSet <\/code>that is the<code> DefaultParameterSetName<\/code>. The <code>$FileListOnly <\/code>is from <code>FileList<\/code>, <code>$<\/code><code>Sqlserve<\/code>r and <code>Filepath <\/code>are not part of any parameter set.<\/p>\n<p>Why is this useful? Well, if I specify the <code>FileListonly <\/code>in a restore backup , there is no need to use <code>$action<\/code>, <code>$stopat <\/code>or the entire Restore set. I just want <code>FileListOnly <\/code>or, in a T-SQL example a Restore <code>FileListOnly. <\/code>The same applies to <code>FileList<\/code>ParameterSet. But using either Parameters Set in this function also requires a SQL Server Connection and a File path. This is why these options do not belong to any Parameter Set.<\/p>\n<p>One last example: Do you remember when I said that &#8220;if you have some positional parameters with default values, and the user doesn&#8217;t specify any parameter names, the default set will be used&#8221;. Let&#8217;s see how this works. In the first function, the <code>DefaultParameterSetName<\/code> is &#8220;First&#8221; and in the Second <code>ParameterSetName<\/code> is &#8220;Second&#8221; :<\/p>\n<pre class=\"theme:powershell-ise lang:ps\">Function TestingDefaultParameterSetName_ToFirst {\r\n     [CmdletBinding(DefaultParameterSetName = \"First\")]\r\n  \r\n     param (\r\n         [parameter(ParameterSetName=\"First\")] [Int] $IamFirst = 10,\r\n          [parameter(ParameterSetName=\"Second\")] [int] $IamSecond = 20\r\n     )\r\n     \r\n     Write-Host \"------------------------------------------------------------\"\r\n     Write-Host \"I am  The TestingDefaultParameterSetName_ToFirst Function\"\r\n     Write-Host \"Parameter Set : $($PSCmdlet.ParameterSetName)\"\r\n     \r\n     switch ($PSCmdlet.ParameterSetName) {\r\n         \"First\" { write-host \"I am First : $($IamFirst)\" ; break }\r\n          \"Second\" {  write-host \"I am Second : $($IamSecond)\" ; break }\r\n     }    \r\n     \r\n     Write-Host \"------------------------------------------------------------\"\r\n     \r\n  \r\n }\r\n  \r\n Function TestingDefaultParameterSetName_ToSecond {\r\n     [CmdletBinding(DefaultParameterSetName = \"Second\")]\r\n     param (\r\n         [parameter(ParameterSetName=\"First\")] [Int] $IamFirst = 30,\r\n          [parameter(ParameterSetName=\"Second\")] [int] $IamSecond = 40\r\n     )\r\n  \r\n     Write-Host \"------------------------------------------------------------\"\r\n     Write-Host \"I am  The TestingDefaultParameterSetName_ToSecond Function\"\r\n     Write-Host \"Parameter Set : $($PSCmdlet.ParameterSetName)\"\r\n  \r\n\r\n     switch ($PSCmdlet.ParameterSetName) {\r\n         \"First\" { write-host \"I am First : $($IamFirst)\" ; break }\r\n          \"Second\" {  write-host \"I am Second : $($IamSecond)\" ; break }\r\n     }    \r\n  \r\n     Write-Host \"------------------------------------------------------------\"\r\n }\r\n\r\n<\/pre>\n<p>Now let&#8217;s run each function without any parameters:<\/p>\n<pre class=\"theme:powershell-output lang:ps\">TestingDefaultParameterSetName_ToFirst \r\nTestingDefaultParameterSetName_ToSecond \r\n<\/pre>\n<pre class=\"theme:powershell-output lang:ps\">------------------------------------------------------------ \r\nI am The TestingDefaultParameterSetName_ToFirst Function \r\nParameter Set : First \r\nI am First : 10 \r\n------------------------------------------------------------ \r\n------------------------------------------------------------ \r\nI am The TestingDefaultParameterSetName_ToSecond Function \r\nParameter Set : Second \r\nI am Second : 40 \r\n------------------------------------------------------------ \r\n<\/pre>\n<p>Do you see how this works? I am not passing any parameter to them and the parameter set default was that specified at <code>DefaultParameterSetName<\/code><\/p>\n<p>That is it my friends. In this article we covered the famous <code>[<\/code><code>CmdletBinding<\/code><code>(<\/code><code>)] <\/code>attribute and demonstrated how some of its magic is done. In the next article in this series, we will look at the wonderful ways that parameters can be validated with advanced functions.<\/p>\n<h2>References and Acknowlegements<\/h2>\n<p>I would like to thank some gentlemen who spared no effort to share their knowledge, either to me or to the community, and who took part, directly or indirectly, in the creation of this article. To my good friends Chad Miller, Shay Levy, Ravikanth Chaganti; and for his articles, books and blog, Sir Don Jones.<\/p>\n<p>A special thanks to Sir Jeffery Hicks, Sir Phil Factor and Sir Bob Beauchemin that kindly gave your time to tech review and proofread the article.<\/p>\n<p>A huge thanks to my Tech Editors Chis Massey and Andrew Clarke. The real magic is what these guys do for me<\/p>\n<h3>Books:<\/h3>\n<ul>\n<li>Bruce Payette&#8217;s Windows PowerShell in Action, Second Edition<\/li>\n<\/ul>\n<h3>Blogs:<\/h3>\n<ul>\n<li><a href=\"http:\/\/sev17.com\/\">Chad Miller<\/a> &#8211; (@cmille19)<\/li>\n<li><a href=\"http:\/\/sev17.com\/\">Sev17 &#8211; SQL Server, PowerShell and so on<\/a><\/li>\n<li><a href=\"http:\/\/sqlpsx.codeplex.com\/\">Codeplex &#8211; SQLPSX (SQL Server PowerShell Extensions)<\/a><\/li>\n<li><a href=\"http:\/\/www.energizedtech.com\/\">Sean Kearney<\/a> (@energizedtech)<\/li>\n<li><a href=\"http:\/\/www.energizedtech.com\/2011\/06\/implementing-thewhatif-into-an.html\">Implementing the-WHATIF into an Advanced Function in Windows PowerShell<\/a><\/li>\n<li><a href=\"http:\/\/windowsitpro.com\/author\/don-jones\">Don Jones<\/a> (@concentrateddon)<\/li>\n<li><a href=\"http:\/\/jdhitsolutions.com\/blog\/\">Jeffery Hicks<\/a> (@JeffHicks)<\/li>\n<li><a href=\"http:\/\/www.ravichaganti.com\/blog\/\">Ravikanth Chaganti<\/a> (@ravikanth)<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Once you pass that point of just hurriedly writing PowerShell scripts for immediate use and start to write PowerShell functions for reuse, then you&#8217;ll want a robust set of parameters that allow functions to work just like cmdlets. &hellip;<\/p>\n","protected":false},"author":221715,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4170,4635,4150,5651],"coauthors":[6819],"class_list":["post-1355","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database-administration","tag-powershell","tag-sql","tag-the-posh-dba"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1355","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221715"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1355"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1355\/revisions"}],"predecessor-version":[{"id":73717,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1355\/revisions\/73717"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1355"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1355"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}