double quotes on a sql server code inside powershell - in visual code - how to make it work?

Question

double quotes are used in sql server, and their behaviour can be defined by the option SET QUOTED_IDENTIFIER ON\ OFF

double quotes are also used in powershell.

SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.

this is a partial view of a stored procedure that I want to deploy to multiple servers, however, because of the quoted-identifier, or double quotes this character - " - it is not working in powershell, in the visual code that I use to deploy.

DECLARE @WebXConfigurationID INT,
                @stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
                @stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
                @cur_Properties XML;
        PRINT 'WebX Configuration: ' + CAST(@WebXConfigurationID AS VARCHAR(20));

        IF (ISNULL(@stored_URL, '') <> ISNULL(@cur_URL, ''))
        BEGIN
            PRINT ' - Base URL needs updating'
        END 
        ELSE
        BEGIN
            PRINT ' - Base URL does not need updating'
        END;

        IF (ISNULL(@stored_OpsServiceURL, '') <> ISNULL(@cur_OPSServiceURL, '') AND @cur_OPSServiceURL IS NOT NULL)
        BEGIN
            PRINT ' - OPS Service URL needs updating'
            SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')
        END 
        ELSE
        BEGIN
            PRINT ' - OPS Service URL does not need updating'
        END;

this is the error message:

"' in expression or statement.
At C:\sp_PosRestore.ps1:636 char:114
+ ... ServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')
+                                                 ~~~~~~~~~~~~~~~~~~~~~
The splatting operator '@' cannot be used to reference variables in an expression. '@stored_OpsServiceURL' can be used only as an argument to a command. To reference variables in an expression use      
'$stored_OpsServiceURL'.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : UnexpectedToken

The Powershell code:

$SQLQuery = 
"
DECLARE @WebXConfigurationID INT,
                @stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
                @stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
                @cur_Properties XML;
        PRINT 'WebX Configuration: ' + CAST(@WebXConfigurationID AS VARCHAR(20));

        IF (ISNULL(@stored_URL, '') <> ISNULL(@cur_URL, ''))
        BEGIN
            PRINT ' - Base URL needs updating'
        END 
        ELSE
        BEGIN
            PRINT ' - Base URL does not need updating'
        END;

        IF (ISNULL(@stored_OpsServiceURL, '') <> ISNULL(@cur_OPSServiceURL, '') AND @cur_OPSServiceURL IS NOT NULL)
        BEGIN
            PRINT ' - OPS Service URL needs updating'
            SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')
        END 
        ELSE
        BEGIN
            PRINT ' - OPS Service URL does not need updating'
        END;

"

Answer 1

Double quotes are interpolated strings, single quotes are non-interpolated. Since you have quotes within quotes, you need to escape them. And if you have @ involved then it will try to interpolate.

So you need single quotes around the string, and any single quotes within that need to be doubled. See here for more info.

$SQLQuery = '
DECLARE @WebXConfigurationID INT,
                @stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
                @stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
                @cur_Properties XML;
        PRINT ''WebX Configuration: '' + CAST(@WebXConfigurationID AS VARCHAR(20));

        IF (ISNULL(@stored_URL, '''') <> ISNULL(@cur_URL, ''''))
        BEGIN
            PRINT '' - Base URL needs updating'';
        END 
        ELSE
        BEGIN
            PRINT '' - Base URL does not need updating'';
        END;

        IF (ISNULL(@stored_OpsServiceURL, '''') <> ISNULL(@cur_OPSServiceURL, '''') AND @cur_OPSServiceURL IS NOT NULL)
        BEGIN
            PRINT '' - OPS Service URL needs updating'';
            SET @cur_Properties.modify(''replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")'');
        END 
        ELSE
        BEGIN
            PRINT '' - OPS Service URL does not need updating'';
        END;

';

The other option is a Here-string, surrounded with @' and '@.

$SQLQuery = @'
DECLARE @WebXConfigurationID INT,
                @stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
                @stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
                @cur_Properties XML;
        PRINT 'WebX Configuration: ' + CAST(@WebXConfigurationID AS VARCHAR(20));

        IF (ISNULL(@stored_URL, '') <> ISNULL(@cur_URL, ''))
        BEGIN
            PRINT ' - Base URL needs updating';
        END 
        ELSE
        BEGIN
            PRINT ' - Base URL does not need updating';
        END;

        IF (ISNULL(@stored_OpsServiceURL, '') <> ISNULL(@cur_OPSServiceURL, '') AND @cur_OPSServiceURL IS NOT NULL)
        BEGIN
            PRINT ' - OPS Service URL needs updating';
            SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")');
        END 
        ELSE
        BEGIN
            PRINT ' - OPS Service URL does not need updating';
        END;

'@;

---

SET QUOTED_IDENTIFIER has nothing to do with any of this. That strictly refers to the use of "" to delimit identifiers, rather than strings. And it should always be ON anyway. It is not relevant to any of the code you posted.

Answer 2

I have changed this line:

SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')

to this (basically I added the backtick character (`) before the double-quote:

SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable(`"@stored_OpsServiceURL`")')

all works fine now. Backtick worked fine

however what worked best for me was the @' and '@ as per charlieface answer