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

rsbarro · Accepted Answer · 2011-08-12 04:25:52Z

3

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"

edited Aug 12, 2011 at 4:25

answered Aug 10, 2011 at 19:46

rsbarro

27.4k9 gold badges73 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

odez213 Over a year ago

I'll try that route however I am using scripting variable. So my variable is as follow: :SETVAR MyVar "MyValue"

rsbarro Over a year ago

OK. If my suggestion doesn't work can you post a sample of your SQL script in the question?

odez213 Over a year ago

Posted my sql script in the question

rsbarro Over a year ago

I'd be happy to update my answer, but your update to the question is a little confusing. Are you using SETVAR or not?

odez213 Over a year ago

I updated my sql script. The SetVar I am trying to define it from SQLCMD using the -v flag. Hence in the SQL script the SetVar is commented out.

|

Philip Kelley · Accepted Answer · 2011-08-11 14:00:27Z

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)'

answered Aug 11, 2011 at 14:00

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

3 Comments

odez213 Over a year ago

That will script it out as MyValueNoQuotes. I am hoping there is an escape key to put the double quotes in.

odez213 Over a year ago

It seems that at sqlcmd level when I pass in as """MyValue""" it will out put as ""MyValue"". While I wanted to get "MyValue" and I am not able to do so.

Philip Kelley Over a year ago

I'm missing a point here: why do the double quotes need to be included in the SQLCMD variable? I'm proposing to "move" them into the SQL code, such that they don't need to be part of the variable. In your code you are only using them to delimit the string value being written to the Description column.

Collectives™ on Stack Overflow

SQLCMD passing in double quote to scripting variable

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related