Powershell Invoke-sqlcmd In-script Variable override

Question

I am using the following:

Powershell Command: Invoke-Sqlcmd -AbortOnError -InputFile "C:\FullPath\SQLQuery.sql" -ServerInstance WPU8V9011532 -Variable "PREF = 'NeededVal'"

SQLQuery.sql file content:

:setvar PREF "InternalVal"

USE $(PREF)MyDB Select * from ABC

What I need: To make the execution use "NeededVal" instead of "InternalVal".

Due to precedence rules, it doesn't work. Is it possible to override them? There are 100s of such files to execute and cannot be edited. Also, the servers of execution are multiple and repeatedly required (executions).

Answer 1

There's just no way to do this: SQLCMD, command-line variables and script :setvar

Microsoft: https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-with-scripting-variables?view=sql-server-ver15#variable-precedence-low-to-high

Answer 2

I think your issue is the Variable parameter expects a string array. Also the use of single quotes does not make for valid SQL syntax. Try:

Invoke-Sqlcmd -AbortOnError -InputFile "C:\FullPath\SQLQuery.sql" -ServerInstance WPU8V9011532 -Variable @("PREF=NeededVal","ANOTHERVAR=ANOTHERVALUE")

UPDATE

As per the OP’s comment I addressed syntax issues. The answer is no, script variables have the highest precedence as per SQLCMD Variable Precedence.

One solution is to remove variables from the scripts and have all callers provide the values. Another is to coalesce SQLCMD variable values into SQL variables. The point is that there’s no getting around editing the SQL scripts.