Passing multiple values into a SQLCmd script

  • 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?

     

     

     

  • Have you tried adding double quotes around the second param?

    "200,210"

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

     

     

     

  • 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

     

     

     

     

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

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