Pass 2 parameters in powershell

Sachin Rathore 21 Reputation points
2021-01-10T14:10:06.947+00:00

I have been trying to run a .sql file through powershell. For the script need to pass 2 parameters through powershell. I have written like below:

$DBScriptFile = "C:\powershell\Power.sql"
$sqlParameters =@("id=1,2", "val1=2,3")
foreach($i in $sqlParameters)
{
Invoke-Sqlcmd -ServerInstance ServerName -Database test -Username "test" -Password "test" -InputFile $DBScriptFile -Variable $sqlParameters
}

T-sql SCRIPT: select * from t1 where id in($(id)) and val1 in($(val1))

I am getting below output right now:

id val1


1 2
2 2
1 2
1 3
2 3
1 2
2 2
1 2
1 3
2 3

What i want is for each run in the loop the parameters should be passed in conjuction like for first run value should be passed 1 & 2, for second run value should be passed 2 & 3. The expected output should be like this.

id val1


1 2
1 2
2 3
1 2
1 2
2 3

From the script i have created i am getting all the combinations of both arguments. I am pretty new to powershell and could not find much information about this over internet. Could you please help me figure this out?

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,549 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,652 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 112.7K Reputation points MVP
    2021-01-10T18:41:33.17+00:00

    Change the assignment of $sqlParameters to

    $sqlParameters=(("id=1", "val1=2"), ("id=2", "val1=3"))
    

    You need a nested array so that you can pick one pair at a time.

    And in the call to InvokeSqlcmd change $sqlParameters to $i, the looping variable.

    And while not required, you could change IN in the SQL script to a plain =.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Viorel 118K Reputation points
    2021-01-10T17:51:21.223+00:00

    I think that one of the possible kind of queries is, for example:

    select t.* from t1 t
    cross apply (values (4, 3), (7, 2), (9, 5)) a(id, val1)
    where t.id = a.id and t.val1 = a.val1
    

    where 4, 7, 9 are id; 3, 2, 5 are val1. It will find the corresponding pairs of id and val1. Probably the parameterized query will look like this:

    select t.* from t1 t
    cross apply (values $(pairs)) a(id, val1)
    where t.id = a.id and t.val1 = a.val1
    

    where $(pairs) is a variable that contains “(4, 3), (7, 2), (9, 5)”.

    In my opinion, a query that deals with your $(id) and $(val1) will be more complicated. Is it imperious to use these form of parameters?

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.