代码之家 › 专栏 › 技术社区 › Alex Gordon

变量名在查询批处理或存储过程中必须是唯一的?

tsql powershell sql-server sql

1

Alex Gordon · 技术社区 · 5 年前

我们如何在一个事务中运行多个sql脚本,其中每个脚本可能定义了相同的变量?

文件1:

declare @tableName varchar(28), @query nvarchar(1000)
set @tableName = '1apples'+convert(varchar(28), getdate(),121)
select @query = 'select name into ' + quotename(@tableName) + ' from sys.objects'
print @query
exec (@query)
go

文件2:

declare @tableName varchar(28), @query nvarchar(1000)
set @tableName = '2apples'+convert(varchar(28), getdate(),121)
select @query = 'select name into ' + quotename(@tableName) + ' from sys.objects'
print @query
exec (@query)
 go

我正在PowerShell中执行如下文件:

try {

  $files = Get-ChildItem -Path $SqlFilesDirectory -File -Filter *.sql
  $filesSorted = $files |Sort-Object -Property Name
  $filesconcatenated = @(
      $files |Get-Content -Raw
  ) -join [Environment]::NewLine
  $filesconcatenated = 'SET XACT_ABORT ON ' + $filesconcatenated + ' SET XACT_ABORT OFF ' + [Environment]::NewLine
    $cmd = New-Object System.Data.SqlClient.SqlCommand ($filesconcatenated), $conn
    $cmd.Transaction = $tran
    [void]$cmd.ExecuteNonQuery()
   
  $tran.Commit()
}
catch {
  $tran.Rollback()
  Write-Host "Record not Inserted ->"
  $_.exception.message
}
finally {
  $conn.Close()
}

我得到以下异常:

使用“0”参数调用“ExecuteNonQuery”时发生异常:“不正确关键字“declare”附近的语法。变量名“@tableName”具有已声明。变量名在查询中必须唯一批处理或存储过程。变量名“@tableName”已经已宣布。变量名在查询批处理中必须是唯一的,或者存储过程。关键字“SET”附近的语法不正确。"

如何在一个事务中运行多个SQL脚本,其中每个脚本可能定义了相同的变量?

以下是它试图运行的完整脚本:

SET XACT_ABORT ON 
declare @tableName varchar(28), @query nvarchar(1000)
set @tableName = '1apples'+convert(varchar(28), getdate(),121)
select @query = 'select name into ' + quotename(@tableName) + ' from sys.objects'
print @query
exec (@query)
go
declare @tableName varchar(28), @query nvarchar(1000)
set @tableName = '2apples'+convert(varchar(28), getdate(),121)
select @query = 'select name into ' + quotename(@tableName) + ' from sys.objects'
print @query
exec (@query)
go
declare @tableName varchar(28), @query nvarchar(1000)
set @tableName = '3apples'+convert(varchar(28), getdate(),121)
select @query = 'select name into ' + quotename(@tableName) + ' from sys.objects'
print @query
exec (@query)
go
 SET XACT_ABORT OFF

1 回复 | 直到 5 年前

1

4

Charlieface 5 年前

同一个变量不能在一个批次中出现两次

因此,通过循环数组,将它们作为单独的命令执行

$connString = "Server=tcp:$ServerName.database.windows.net,1433;Database=$DBName;User Id=$SvcAdminAccount@$ServerName;Password=$SvcAdminPassword;MultipleActiveResultSets=true;Persist Security Info=true;";
$conn = New-Object System.Data.SqlClient.SqlConnection $connString try
{
  $conn.Open()
  $files = Get-ChildItem -Path $SqlFilesDirectory -File -Filter *.sql
  $filesSorted = $files |Sort-Object -Property Name
  $tran = $conn.BeginTransaction()
  foreach ($file in $filesSorted)
  {
    $query = "SET XACT_ABORT ON; " + (Get-Content -Raw $file)
    $cmd = New-Object System.Data.SqlClient.SqlCommand ($query, $conn, $tran)
    [void]$cmd.ExecuteNonQuery()
  }
  $tran.Commit();
}
catch {
  Write-Host "Record not Inserted ->"
  $_.exception.message
}
finally {
  $tran.Dispose()
  $conn.Dispose()
}

注意方式 Dispose 使用,而不是 Rollback 因为 处置 永远不会抛出异常。它也在 finally 而不是 catch