PowerShell Function multiple SQL queries output as CSV

I'm not sure if SQL processes multiple queries separately so while you might be passing two different queries the SQL server might be interpreting them as one query (Not 100% sure this is happening, just a guess really)

You've put your queries in an array so we can easily loop through the array, run each query by itself and put the results into a CSV.

Here's how i'd modify your code to start with:

Function Run-Query
{
    param([string[]]$queries,[string[]]$sheetnames)
    Begin
    {
        $SQLServer = 'ServerName'
        $Database = 'DataBase'
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True"
    }#End Begin
    Process
    {
        # Loop through each query
        For($i = 0; $i -lt $queries.count; $i++)
        {
            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

            # Use the current index ($i) to get the query
            $SqlCmd.CommandText = $queries[$i]

            $SqlCmd.Connection = $SqlConnection
            $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
            $SqlAdapter.SelectCommand = $SqlCmd
            $DataSet = New-Object System.Data.DataSet
            $SqlAdapter.Fill($DataSet)

            # Use the current index ($i) to get the sheetname for the CSV
            $DataSet.Tables[0] | Export-Csv -NoTypeInformation -Path "C:\Scripts\$($sheetnames[$i]).csv"
        }
    }#End Process
    End
    {
        $SqlConnection.Close()
    }
}#End function run-query.

$queries = @()

$queries += @'
Select * from something
'@
$queries += @'
Select * from something2
'@

$sheetnames = @()
$sheetnames += 'Cert'
$sheetnames += 'Prod'

Run-Query -queries $queries -sheetnames $sheetnames