8wDlpd.png
8wDFp9.png
8wDEOx.png
8wDMfH.png
8wDKte.png

将数组作为参数从 PowerShell 传递给 SQL 脚本

PruSafiiX 1月前

14 0

我正在创建一个相当复杂的 SQL 脚本,用于从数据库中删除一堆记录。因为我必须执行大量操作,所以我想将要删除的记录的 ID 保存在临时文件中...

我正在创建一个相当复杂的 SQL 脚本,用于从数据库中删除一堆记录。因为我必须执行很多操作,所以我想将要删除的记录的 ID 保存在临时表中

DECLARE @RunIDsToDelete TABLE (RunID INT);

INSERT INTO @RunIDsToDelete (RunID)
VALUES
       N'$(runIDs)'

要删除的 ID 列表(要保存在表中的别名)必须是来自 PowerShell 脚本的参数。SQL 脚本必须将我想要读取的确认信息发送回 PowerShell 脚本。

$runIds = @(1, 2)
$paramList = $runIds -join ","

$sqlCommand = "sqlcmd -S sql1 -d mydb -i $($sqlScriptPath) -v IDs=$paramList"

我的第一次尝试是准备列表并将结果字符串传递给 SQL 脚本,但我收到此错误

sqlcmd:Sqlcmd:“,(2),(4)”:参数无效。输入“-?”获取帮助。

+ CategoryInfo          : NotSpecified: (Sqlcmd: ',(2),(... '-?' for help.:String) [], 
                          RemoteException
+ FullyQualifiedErrorId : NativeCommandError

然后,我尝试传递这样的字符串

$sqlCommand = "sqlcmd -S sql1 -d myDb -i $($sqlScriptPath) -v IDs=""(1), (2)"""

但我又遇到了同样的错误。那么我该如何传递 ID 列表呢?

帖子版权声明 1、本帖标题:将数组作为参数从 PowerShell 传递给 SQL 脚本
    本站网址:http://xjnalaquan.com/
2、本网站的资源部分来源于网络,如有侵权,请联系站长进行删除处理。
3、会员发帖仅代表会员个人观点,并不代表本站赞同其观点和对其真实性负责。
4、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报
5、站长邮箱:yeweds@126.com 除非注明,本帖由PruSafiiX在本站《powershell》版块原创发布, 转载请注明出处!
最新回复 (0)
  • 这里你需要的是表类型(\'structured\')参数。如果你使用 DbaTools 之类的模块,并且它

  • 为此,您最好使用 ADO.Net 对象而不是 sqlcmd 程序,因为它对表值参数有很好的支持。

  • 您只需使用内部转义字符来处理该字符串。PS 中的转义字符是反斜杠 (`)。在按钮 1 之前。

  • 正如我在评论中提到的,使用 DbaTools 模块之类的东西会容易得多。然后,您可以使用 New-DbaSqlParameter 创建添加表类型参数(使用构建) ConvertTo-DbaDataTable ,并将其传递给 Invoke-DbaQuery .

    请看以下过于简化的表格:

    CREATE TABLE dbo.StructuredTest (ID int IDENTITY,
                                     SomeString varchar(10),
                                     SomeInt int);
    GO
    
    INSERT INTO dbo.StructuredTest (SomeString,
                                    SomeInt)
    VALUES('abc',1),
          ('def',2),
          ('xyz',1);
    

    现在我们要传递一个 ID 列表 int 。我们需要一个表类型,例如:

    CREATE TYPE dbo.Ints AS table(IntValue int);
    

    然后我们可以使用 PowerShell 中的以下脚本将表类型作为类型传递 dbo.Ints

    $Query = 'SELECT * FROM dbo.StructuredTest ST WHERE EXISTS (SELECT 1 FROM @TableVariable TV WHERE TV.Intvalue = ST.ID);'
    
    $IDs = @()
    $IDs += [PSCustomObject]@{Intvalue = 1}
    $IDs += [PSCustomObject]@{Intvalue = 2}
    $DT = ConvertTo-DbaDataTable -InputObject $IDs
    
    $TableVariable = New-DbaSqlParameter -ParameterName TableVariable -SqlDbType Structured -Value $DT -TypeName 'dbo.Ints'
    
    Invoke-DbaQuery -SqlInstance 'SandboxServer' -Database Sandbox -Query $Query -SqlParameter $TableVariable
    
  • THVV 1月前 0 只看Ta
    引用 6

    我从根本上同意 这个答案 - 使用 DbaTools 可以让你的生活更轻松 - 但是你可以从 PowerShell 以编程方式执行此操作而无需调用任何外部工具:

    $runIDs = @(1, 2, 3)
    
    # establish connection
    $connection = [System.Data.SqlClient.SqlConnection]::new("connection string goes here")
    $connection.Open()
    
    # define the first part of the command text
    $cmdTextPreamble = @'
    DECLARE @RunIDsToDelete TABLE (RunID INT);
    
    INSERT INTO @RunIDsToDelete (RunID) 
    VALUES 
    '@
    
    # create a command object
    $cmd = [System.Data.SqlClient.SqlCommand]::new()
    $cmd.Connection = $connection
    
    # now construct the final command text, one insertion at a time
    $cmdText = $cmdTextPreamble
    for ($i = 0; $i -lt $runIDs.Count; $i++) {
      # calculate the next parameter name
      $paramName = "@runID${i}"
      # add the parameterized value set to the command text
      $cmdText += "(${paramName}),"
      # and bind the corresponding value to the parameter name
      $cmd.Parameters.AddWithValue($paramName, $runIDs[$i]) |Out-Null
    }
    
    # remove trailing `,` from the command text and update the $cmd object
    $cmd.CommandText = $cmdText.TrimEnd(',')
    
    # execute insert command
    $rowCount = $cmd.ExecuteNonQuery()
    
    Write-Host "Inserted ${rowCount} rows into table"
    
    • p0

      • 【【p1】】

      • 【【p2】】

    • p3

      • While the broken behavior is fixed in PowerShell (Core) 7 v7.3+ , where getting the old, broken behavior back now requires opt-in via $PSNativeCommandArgumentPassing = 'Legacy' , this opt-in isn't actually necessary for sqlcmd.exe , because it is on an exception list of known executables to which the old behavior continues to be applied automatically in the default $PSNativeCommandArgumentPassing mode, 'Windows' - see the docs for the complete list.

    以下显示的解决方案是 直接调用 命令, sqlcmd.exe 而不是创建它的字符串表示形式:

    $runIds = @(1, 2)
    $paramList = $runIds.ForEach({ '(' + $_ + ')' }) -join ',' # -> "(1),(2)"
    
    # Note the embedded, escaped " chars. around $paramList
    sqlcmd -S sql1 -d mydb -i $sqlScriptPath -v IDs=`"$paramList`"
    

    要将命令作为 字符串 表示形式获取,使用 exp和able (interpolating), double-quoted string ( "..." ) ,如您自己的尝试一样,嵌入的 " and ` 它们本身都需要 ` 转义:

    $sqlCommand =
      "sqlcmd -S sql1 -d mydb -i `"$sqlScriptPath`" -v IDs=```"$paramList```""
    

    但是,请注意,通常没有理由将完整的命令行存储在字符串中(通常不建议与 Invoke-Expression ),因为 PowerShell 可以轻松地以编程方式构造可以 直接 - 有关详细信息, 此答案 有关如何传递存储在 数组 此答案 .

返回
作者最近主题: