 |
|
Invoke Nz Powershell script similar to Nz VBA function
|
By: Chad Boyd | Read Comments (3) | Print Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server. Related Tips: More |
|
I got a few pings about the Restore-SqlDb script requiring the Invoke-Nz script to execute - here it is. Similar functionality to that of the Nz function in VBA - basically pass along 2 values and the function will return the first value if it is present (i.e. non-null), otherwise the second (could also say it's similar to the isnull() tsql function, or a coalesce with only 2 parameters). Sorry I missed this with the original Restore-SqlDb posting.
Thanks to everyone who reminded me,
Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.
-------------------------------- CODE ONLY BELOW --------------------------------
# See the Get-Usage function contents in the begin{} section for usage/comment details
param
(
$value,
$nullValue
)
begin {
function Get-Usage {
@"
NAME
Invoke-Nz
SYNOPSIS
You can use the Nz function to return a specified value when an input value is null/empty/unspecified.
SYNTAX
Nz <value> <nullValue>
DETAILED DESCRIPTION
You can use this function to convert a null/empty value to another value and prevent it from propagating
through an expression. If the value parameter is not present/null, the nullValue value is returned.
If the value of variant isn't Null, then the Nz function returns the value of variant.
You can often use the Nz function as an alternative to the IIf function. For example, in the following code,
two expressions including the IIf function are necessary to return the desired result. The first expression
including the IIf function is used to check the value of a variable and convert it to zero if it is Null.
PARAMETERS
-value <any>
Value to check for null/empty/etc. - if not present, the -nullValue is returned
Required? True
Position? 1
Default value <required>
Accept pipeline? True
Accept wildcards? False
-nullValue <any>
A Variant that supplies a value to be returned if the variant value argument is not present.
Required? False
Position? 2
Default value <required>
Accept pipeline? False
Accept wildcards? False
INPUT TYPE
Any,Any
RETURN TYPE
Either `$value or `$nullValue, depending on if the `$value is present.
NOTES
Alias created called "Nz"
-------------------------- EXAMPLE 1 --------------------------
Nz $testObj "this is null"
-------------------------- EXAMPLE 2 --------------------------
dir *.ps1 | Nz -n "NOFILE"
"@
} # Get-Usage
if (($MyInvocation.InvocationName -ne '.' -and $MyInvocation.InvocationName -ne '&') -and
($Args[0] -eq "-?" -or $Args[0] -eq "/?" -or $Args[0] -eq "-help" -or $Args[0] -eq "/help")) {
$showUsage = $true;
&Get-Usage;
return;
}
if ($MyInvocation.InvocationName -ne '.') { Write-Debug "Invoke-Nz::BEGIN"; }
$InPipeline = $false;
function Invoke-Nz {
param
(
$value,
$nullValue
)
begin {} # Invoke-Nz::begin
process {
# Store meta-data depending on pipeline vs. invoked processing...
if (($_) -or ($_ -eq 0)) { $InPipeline = $true; }
# If we don't have a $value value, figure it out now
if (-not $value) {
if ($_) {
$value = $_;
} else {
# No pipeline input - so, if we are invoked throw an error, otherwise the $Try will be nothing...
if ($MyInvocation.InvocationName -eq '&') {
# Invoked, throw an exception
Throw "The parameter -value is required. [$($MyInvocation.InvocationName)] [$($MyInvocation.MyCommand.Name)] [$_]";
}
}
}
# If we have a value, show it if debugging, otherwise show a warning
if ($value) {
Write-Debug "Invoke-Nz::Have `$value value of [$value] [$_]";
} else {
# Not invoked, show a warning - don't throw an error, as that will stop pipeline processing if we're in the pipe...
# Write-Warning "Invoke-Nz::No value could be determined for the `$value parameter"
# NOTE: Traditionally I'd throw this warning, but since the entire purpose for this particular command is to return
# a value when a particular value isn't present, kind of makes sense not to do so here...
}
# Process the test
if (($value) -or ($value -eq 0)) { $value; } else { $nullValue; }
} # Invoke-Nz::process
end {} # Invoke-Nz::end
}
if (-not (Get-Alias -Name nz -ErrorAction SilentlyContinue)) {
Set-Alias -Name nz -Value Invoke-Nz -Description 'Nz function in VBA - if value parameter is not present, nullValue is returned.';
}
} # Invoke-Nz::begin
process {
# No processing if we are dot-sourced or if we were just asked for a little help...
if ($showUsage -or $MyInvocation.InvocationName -eq '.') {
return;
}
# pass processing to the function via pipelining or invoke...
if (($_) -or ($_ -eq 0)) {
$InPipeline = $true;
$_ | Invoke-Nz $value $nullValue;
} else { # if ($_)
Invoke-Nz $value $nullValue
} # if (($_) -or ($_ -eq 0))
} # Invoke-Nz::process
end {
if ((-not $showUsage) -and ($MyInvocation.InvocationName -ne '.')) { Write-Debug "Invoke-Nz::END"; }
} # Invoke-Nz::end
Last Update: 7/26/2009
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Tuesday, January 11, 2011 - 12:15:06 PM - Lan |
|
|
Hi, New to powershell funciton with begin/process/end, so not quite sure how to invoke this command. If I save this NZ script to C:\temp and run the following command in powershell:- c: I got the following error message:- The term 'Nz' is not recognized as the name of a cmdlet, function, script file, Do you minding casting any light on this ? thanks, Lan
|
|
| Tuesday, January 18, 2011 - 5:09:52 PM - Chad Boyd |
|
|
Hi Lan - looks like the "Usage" section shows an old version of my function. Before I converted it to be pipeline-enabled (that's what the begin/process/end syntax helps enable), I used it as stricktly a local function called "Nz" - upon conversion I renamed it to better match teh powershell cmdlet naming conventions, and hence it now should be called via the full name of "Invoke-Nz" rather than just "Nz". You do have the option of adding an alias for the cmdlet (create an alias named Nz pointing to the Invoke-Nz script). So, if you simply change your syntax to the following it should work: dir *.ps1 | Invoke-Nz -n "NOFILE"
Let me know if it continues to give you any trouble, I'll update the usage section and repost.
Chad |
|
| Wednesday, January 19, 2011 - 6:06:46 AM - Lan |
|
|
Hi Chad, Thanks for taking time replying. Yes, it works. I also read your another article (Restore-SqlDb) which seems very benefiticaly . I occassionally carry out Dev environment refresh, and this seems a great way to fully automated the task. Unfortunately, the script download link seems not working, is there a chance that you can forward me a copy via email ? just run the below TSQL Select CAST(0x6C796E6E5F6C5F7875407961686F6F2E636F2E756B as varchar(50)) As [Email]
Regards, Lan |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |