Invoke Nz Powershell script similar to Nz VBA function

By:   |   Updated: 2009-07-26   |   Comments (3)   |   Related: More > PowerShell

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,

-------------------------------- CODE ONLY BELOW --------------------------------

# See the Get-Usage function contents in the begin{} section for usage/comment details
begin {
function Get-Usage {

You can use the Nz function to return a specified value when an input value is null/empty/unspecified.


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.

Value to check for null/empty/etc. - if not present, the -nullValue is returned

Required? True
Position? 1
Default value
Accept pipeline? True
Accept wildcards? False

A Variant that supplies a value to be returned if the variant value argument is not present.

Required? False
Position? 2
Default value
Accept pipeline? False
Accept wildcards? False


Either `$value or `$nullValue, depending on if the `$value is present.

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;
if ($MyInvocation.InvocationName -ne '.') { Write-Debug "Invoke-Nz::BEGIN"; }
$InPipeline = $false;

function Invoke-Nz {
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 '.') {

# 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 Updated: 2009-07-26

get scripts

next tip button

About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

View all my tips
Related Resources

Comments For This Article

Wednesday, January 19, 2011 - 6:06:46 AM - Lan Back To Top (12637)

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

Tuesday, January 18, 2011 - 5:09:52 PM - Chad Boyd Back To Top (12634)

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.



Tuesday, January 11, 2011 - 12:15:06 PM - Lan Back To Top (12573)


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:-

cd c:\temp
. .\Invoke-Nz.ps1
dir *.ps1 | Nz -n "NOFILE"

I got the following error message:-

The term 'Nz' is not recognized as the name of a cmdlet, function, script file,
 or operable program. Check the spelling of the name, or if a path was included
, verify that the path is correct and try again.
At line:1 char:15
+ dir *.ps1 | Nz <<<<  -n "NOFILE"
    + CategoryInfo          : ObjectNotFound: (Nz:String) [], CommandNotFoundE
    + FullyQualifiedErrorId : CommandNotFoundException

Do you minding casting any light on this ?

thanks, Lan



Recommended Reading

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files

Bulk Copy Data from Oracle to SQL Server

Execute SQL Server Stored Procedures from PowerShell

get free sql tips
agree to terms

Learn more about SQL Server tools