1

I have this SQL file with the CREATE statements for 697 stored procedures in it, but unfortunately it won't execute due to a syntax problem.

The script requires a GO statement between every CREATE PROCEDURE call.

The question is: how do I add GO before every CREATE PROCEDURE statement?

I am looking to achieve this through the use of PowerShell code.

The Select-String might guide us in the right direction as it is able to find the 697 stored procedures in the SQL file. The below returned a count of 697. But not sure how to use this to add text in front of every finding.

(Select-String -Path $sqlFile -Pattern "CREATE PROCEDURE" -AllMatches).Matches.Count

I also tried to replace text in the file with the below command

(Get-Content $sqlFile).replace('CREATE PROCEDURE', ' GO CREATE PROCEDURE') | Set-Content $sqlFile

This however resulted in an error when executing the SQL script:

A fatal scripting error occurred. Incorrect syntax was encountered while parsing GO

Thank you for the help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dieter
  • 401
  • 1
  • 9
  • 31
  • 2
    `GO` ***IS NOT** a SQL statement! It's a **delimiter** used by SQL Server Management Studio and some Microsoft libraries in the SQL Server space - but it's SQL Server specific and **not** a standardized SQL element at all – marc_s Dec 07 '20 at 20:46

1 Answers1

1

The solution is found. Use the `ncharacter.

(Get-Content $sqlFile).replace('CREATE PROCEDURE', " GO `n CREATE PROCEDURE") | Set-Content $sqlFile
Dieter
  • 401
  • 1
  • 9
  • 31
  • Nice, though note that `\`n` isn't a _character_; it's an _escape sequence_ that expands to a LF character in an [expandable (interpolating) string](https://stackoverflow.com/a/40445998/45375) in PowerShell. A slightly slower, but less repetitive solution based on the [`-replace` operator](https://stackoverflow.com/a/40683667/45375) is: `(Get-Content $sqlFile) -replace 'CREATE PROCEDURE', " GO \`n \`$&" | Set-Content $sqlFile` – mklement0 Dec 07 '20 at 23:25