invoke-sqlcmd empty variable

Question

I am trying to perform an SQL query using Invoke-SqlCmd launching script as inputfile which contains SQLCMD variables, as $(varPROJECTNAME).

For example: CREATE DATABASE [$(varPROJECTNAME)$(varDBNAME)] In such a case, i want to be able to set an empty string as varPROJECTNAME value.

This request will run successfully with classical SQLCMD tool, but with Invoke-SqlCmd, i get error, script variable varPROJECTNAME is undefined.

Invoke-Sqlcmd  -Username $LoginSQL -Password $PasswordSQL -ServerInstance $InstanceSQL -Verbose  -InputFile "$rootPackage\DB\UpdateDB\00-initSql\00-SubsTechCreateDatabase.sql"  -Variable "varPROJECTNAME=$projectName","varDBNAME=$DatabaseName"

In case above, if $projectName="", There will be an error

Is it possible to set a default value calling Invoke-SqlCmd, or from sql script point of view, assign a default value to the variable when it's undefined?

Many thanks in advance for your help

Answer 1

In your case, the Invoke-SQLCommand tool is very helpfully running some error checking, ensuring that all of the variables which you've defined here are passed along to SQL to setup your database just so.

I would propose a change, let's add some logic to your script to see if you've specified a $ProjectName value, and if so, pass that value along. If not, run a slightly different command instead.

If($projectName -ne $null){
 $variables = "varPROJECTNAME=$projectName","varDBNAME=$DatabaseName"
   }
 else{
 $variables = "varDBNAME=$DatabaseName"
  }

Invoke-Sqlcmd  -Username $LoginSQL -Password $PasswordSQL -ServerInstance $InstanceSQL `
  -Verbose  -InputFile "$rootPackage\DB\UpdateDB\00-initSql\00-SubsTechCreateDatabase.sql" `
  -Variable $variables

Edit

Could you try this example instead?

In your current code, replace this

-Variable "varPROJECTNAME=$projectName"

with

-Variable "varPROJECTNAME=`"$($projectName)`""

Answer 2

Coalescing to '""' in PowerShell and then checking for that in SQL worked for me.

PowerShell:

# coalesce command if undefined, whitespace or empty
if ("$($step.command)".Trim().Length -eq 0) {
    $command = '""'
} else {
    $command = $($step.command)
}

$variables = @(
    "job_name=$($job.name)",
    "step_name=$($step.name)",
    "step_id=$($step.id)",
    "package_path=$($step.package)",
    "command=$command",
    ...
)

Invoke-Sqlcmd -InputFile "$PSScriptRoot\upsert_job_step.sql" -Variable $variables -ConnectionString $connString -Verbose -OutputSqlErrors $true

SQL:

DECLARE @command varchar(2047) = CASE
  WHEN NULLIF(TRIM('$(command)'), '""') IS NOT NULL
  THEN '$(command)'
  ELSE  FORMATMESSAGE('/ISSERVER "\"\SSISDB\$(package_path)\"" /ENVREFERENCE %i ..., @referenceId)
END;

The value can be anything (say undefined or null) it just needs to be the same in both scripts.