chrisdoubleu13 chrisdoubleu13 - 6 months ago 24
SQL Question

PowerShell: How to determine if SQL query returns 0 rows

I have a script run a select statement and then go through a reader, but when there are no rows returned in the statement, it skips the reader and causes an error later on in the script.

I want to be able have my script do either:

(a) if the select statement returns rows, go through the reader and finish the script, or

(b) if no rows returned, do something else and write to the host that no rows were returned.

Here is what I have currently at that part of the script:

$SQLQuery = $("SELECT [Column1] FROM [MyDatabase].[dbo].[MyTable] WHERE Column1 > 0")

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$Reader = $Command.ExecuteReader()
while ($Reader.Read()) {
## do some stuff
}

$Connection.Close()

## The rest of the script.....


I want to have my script do this:

IF (SQL output returns rows) {
do stuff
}
ELSEIF (SQL output returns 0 rows) {
tell me 0 rows were returned
}


Thanks.

Answer

Check for the HasRows property on the SqlDataReader You might want to do the if check first and see whether there are rows returned and then if it has, then go ahead and read them.

If ($Reader.HasRows) {
       //read your rows.
}

else
{
   no rows returned...
}