Variable names must be unique within a query batch or stored procedure?

Question

How do we run multiple sql scripts in one transaction, where each script may have the same variable defined?

file1:

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

file2:

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

I am executing the files like this in 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()
}

I'm getting the following exception:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near the keyword 'declare'. The variable name '@tableName' has already been declared. Variable names must be unique within a query batch or stored procedure. The variable name '@tableName' has already been declared. Variable names must be unique within a query batch or stored procedure. Incorrect syntax near the keyword 'SET'."

How do we run multiple SQL scripts in one transaction, where each script may have the same variable defined?

Here's the complete script it's trying to run:

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

You're building on answers to two of your previous questions, https://stackoverflow.com/a/69756763/45375 and https://stackoverflow.com/a/69763633/45375. These answers provide solutions to those question _as asked_ - that they didn't also solve _later_ problems you ran into is no shortcoming of theirs, and to guide future readers you should accept answers based on whether they solve the problem as asked. — mklement0, Nov 01 '21 at 19:35
Does this answer your question? [The variable name '@' has already been declared. Variable names must be unique within a query batch or stored procedure](https://stackoverflow.com/questions/19535486/the-variable-name-has-already-been-declared-variable-names-must-be-unique-w) — TylerH, Jan 18 '23 at 16:53

Charlieface · Accepted Answer · 2021-11-01T19:51:59.863

You cannot have the same variable twice in a single batch

So execute them as separate commands, by looping the array

$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()
}

Note the way Dispose is used, rather than Rollback, because Dispose will never throw an exception. It's also in the finally rather than the catch

Variable names must be unique within a query batch or stored procedure?

1 Answers1