Passing multiple values into a SQLCmd script

Question

Post reply

Passing multiple values into a SQLCmd script

Weegee2017

SSC Eights!

Points: 994
More actions
September 25, 2019 at 4:24 pm

#3683352

I want to be able to pass multiple values into a sqlCmd script which calls a stored procedure
The stored procedure in the sqlCmd Script is called as follows:
Exec GenerateExtractFile @StoredProcedureRunTrackerID = 1,
@CarURN = N'$(varCarURN)',
@MethodURNList = '200,210',
@FromDate = '01-Jan-2018',
@ToDate = '02-Jan-2018'
I tried the following statement which worked:
set @Statement = 'sqlcmd -v varCarURN=5766 -E -h-1 -S MAVERICK\SQLINST1 -d Portland -i"\\Server1\\Live_Run\sqlcmdTransferExtract.sql"
I then tried the following statement which didn't work
set @Statement = 'sqlcmd -v varCarURN=5766 varURNList = 200,210 -E -h-1 -S MAVERICK\SQLINST1 -d Portland -i"\\Server1\\Live_Run\sqlcmdTransferExtract.sql"
How can I get this to work with multiple parameters?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247037 More actions · Answer 1

Have you tried adding double quotes around the second param?

"200,210"

Weegee2017 SSC Eights! Points: 994 More actions · Answer 2

Hi Phil

that worked perfectly!

so I amended my statement as follows

set @Statement = 'sqlcmd -v varClientURN=5766 varOfferingURNList = "200,210" varFromDate ="01-Jan-2018" varToDate = "02-Jan-2018"

Thanks for your help

chanab Newbie Points: 1 More actions · Answer 3

Hi,

I'm also trying to pass multiple parameters and get an error.

The .bash file:

echo "Update RCM Database"

sqlcmd -v module_name=$5 version_major=$6 version_minor=$7 version_sub=$8 version_build=$9 -S $1 -U $2 -P $3 -d $4 -i updateRcmDatabase.sql

The command line I'm using to run the bash:

./runUpdateVersionsDBScript.bash x.x.x sa yyyyyy1 chana25_RCM Studio 3 5 0 0

The sql file:

IF NOT EXISTS (SELECT * FROM [dbo].[acm_md_versions] WHERE [module_name]=$(module_name) and version_major=$(version_major) and version_minor=$(version_minor) and version_sub=$(version_sub) and version_build=$(version_build))

INSERT INTO [dbo].[acm_md_versions] (module_name,version_major,version_minor,version_sub,version_build,comments,upgrade_at,module_type)

VALUES ($(module_name), $(version_major), $(version_minor), $(version_sub),$(version_build),'',convert(varchar, getdate(), 25),'');

The output with the error:

Update RCM Database

Sqlcmd: 'module_name=Studio" "version_major=3" "version_minor=5" "version_sub=0" "version_build=0': Invalid argument.

Thanks for any help!

Chanab