How to use CTE in SQL Server

Hi,

Below is the code with CTE example and there is no change in creating asp.net core application, only thing is query change.

using(var connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=true;"))
{
connection.Open();
//"WITH CTE AS (SELECT CustomerID, OrderID FROM dbo.Orders)\r\n"
//+ "SELECT COUNT(*) FROM CTE WHERE CustomerID = @CustomerID;",
var countCommand = new SqlCommand(
"WITH CTE1 AS (SELECT CustomerID FROM dbo.Customers),\r\n"
+ " CTE2 AS (SELECT o.CustomerID, o.OrderID FROM dbo.Orders AS o\r\n"
+ " INNER JOIN CTE1 ON CTE1.CustomerID = o.CustomerID)\r\n"
+ "SELECT COUNT(*) FROM CTE2 WHERE CustomerID = @CustomerID "
+ "GROUP BY CustomerID;", // this GROUP BY is superfluous, returns no rows
connection);
countCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5).Value = "NONE"; // does not exists
int? count = (int?)countCommand.ExecuteScalar();
Console.WriteLine("Count: {0}", count ?? 0);
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.