SQLCMD passing in double quote to scripting variable

Question

I am trying to pass in double quote to a scripting variable in SQLCMD. Is there a way to do this?

sqlcmd -S %serverName% -E -d MSDB -i MyScript.sql -m 1 -v Parameter="\""MyValueInDoubleQuote\""" 

And my sql script is as follow:

--This Parameter variable below is commented out since we will get it from the batch file through sqlcmd
--:SETVAR Parameter "\""MyValueInDoubleQuote\"""


INSERT INTO [MyTable]
           ([AccountTypeID]
           ,[Description])
     VALUES
           (1
           ,$(Parameter))
GO

Solution 1

If you have your sql script set up in this fashion:

DECLARE @myValue VARCHAR(30)
SET @myValue = $(MyParameter)
SELECT @myValue

Then you can get a value surrounded by double quotes into @myValue by just enclosing your parameter in single quotes:

sqlcmd -S MyDb -i myscript.sql -v MyParameter='"123"'

This works because -v is going to replace the $(MyParameter) string with the text '"123"'. The resulting script will look like this before it is executed:

DECLARE @myValue VARCHAR(30)
SET @myValue = '"123"'
SELECT @myValue

Hope that helps.

EDIT
This sample is working for me (tested on SQL Server 2008, Windows Server 2K3). It inserts a record into the table variable @MyTable, and the value in the Description field is enclosed in double quotes:

MyScript.sql (no need for setvar):

DECLARE @MyTable AS TABLE([AccountTypeID] INT, [Description] VARCHAR(50))

INSERT INTO @MyTable ([AccountTypeID] ,[Description])
VALUES(1, $(Parameter))

SELECT * FROM @MyTable

SQLCMD:

sqlcmd -S %serverName% -E -d MSDB -i MyScript.sql -m 1 -v Parameter='"MyValue"'

If you run that script, you should get the following output, which I think is what you're looking for:

(1 rows affected)
AccountTypeID Description
------------- --------------------------------------------------
            1 "MyValue"

Solution 2

Based on your example, you don't need to include the quotes in the variable, as they can be in the sql command, like so:

sqlcmd -S %serverName% -E -d MSDB -i MyScript.sql -m 1 -v Parameter="MyValueNoQuotes"

and

INSERT INTO [MyTable]
            ([AccountTypeID]
            ,[Description])
      VALUES
            (1
            ,"$(Parameter)")

(Though I am more accustomed to use single quotes, as in ,'$(Parameter)'