1

In an interactive batch script, using sqlcmd, I need to query a table for values from a single column that I know will return multiple records. The return values are just integers, but I need to display them so the user can specify which value should be used later on in the batch script.

Here's what I have right now:

@SETLOCAL ENABLEDELAYEDEXPANSION

@ECHO OFF

SET _INSTANCE=<someinstance<
SET _DATABASE=<somedb>

:getcompanyids
REM Query glb_Companies for available IDs
ECHO.
ECHO Getting Company IDs ...
ECHO.
ECHO.

FOR /F %%i IN ( 'sqlcmd -b -S %_INSTANCE% -d %_DATABASE% -E -l 2 -Q "SET NOCOUNT ON; SELECT CompanyID FROM glb_Companies"'
) DO (
    SET IDs=%%i
)

ECHO Company IDs: %IDs%
ECHO.
ECHO.
ECHO After If Not Defined
@pause

As is, only one line is displayed - for the first record found:

Company IDs: 1

In the particular instance and db I am testing this on, I know I should get the values 1, 2, and 13. How do I display those? They can be on the same line like such:

1,2,13

or on separate lines

1
2
13

It doesn't really matter, so long as I can display in a readable fashion all of the values returned from the query.

My searching has only turned up PowerShell scripts, so I'm not finding much help that way.

I'm guessing I either have to get the returned values into an array that I can iterate through (if that can be done in sqlcmd) or somehow iterate through the result set directly.

marky
  • 4,878
  • 17
  • 59
  • 103
  • 2
    Why not `ECHO` them from the `FOR` command instead of assigning them to a variable? – Squashman Jan 29 '20 at 17:09
  • Why do you want to use a batch file, rather than Powershell? – Thom A Jan 29 '20 at 17:09
  • @Squashman, I added `ECHO Company ID: %IDs%` after the `SET` line and got 5 lines of just `Company ID:` and then the `ECHO Company IDs: 1` result as before. Unless I didn't implement that the way that you intended... – marky Jan 29 '20 at 17:13
  • @Larnu, Mainly because I already created an extensive script in a .bat file... (Assuming there would be some rewriting involved and I'm not aware that there would be any benefit to make it worth it... – marky Jan 29 '20 at 17:17
  • Echo the FOR variable!!!! `echo Company ID: %%i` Do not assign them to an environmental variable. – Squashman Jan 29 '20 at 17:19
  • `SET IDs=%%i` will set the value of a variable named `IDs` to each returned line/item from the parenthesized `COMMAND`. Upon completion `%IDs%` will have the value of `13`, which I doubt you're looking to do. Technically as @Squashman, stated you would within the `DO` portion, `ECHO(%%i` to display each line/item returned. I would suggest that you read through the code and answers in [this question](https://stackoverflow.com/q/10166386), for what I think you're intending to do. – Compo Jan 29 '20 at 17:23
  • 1
    @Squashman & Compo, Yep - putting `ECHO i%%` in the DO clause did the trick. Thanks! – marky Jan 29 '20 at 17:37
  • marky, please take more care when you post code, it should be `ECHO %%i`, not `ECHO i%%`. – Compo Jan 29 '20 at 17:44
  • @Compo, Just a simple typo. Thanks, Jacob H – marky Jan 29 '20 at 18:22

0 Answers0