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?

   

   

   

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  September 25, 2019 at 4:52 pm

  #3683369

  Have you tried adding double quotes around the second param?

  "200,210"

  ---

• Weegee2017

  SSC Eights!

  Points: 994

  More actions

  September 27, 2019 at 8:58 am

  #3683895

  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

  December 20, 2022 at 2:24 pm

  #4127947

  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