How To Create A Cte

SQL - Apresentando Common Table Expression (CTE)

J� ouviu falar de Common Table Expression (CTE). Aposto que n�o.

A Common Table Expression (CTE) n�o � um recurso novo e foi introduzido no SQL Server 2005 e continua presente at� hoje mas pouco desenvolvedores conhe�o o recurso.

Mas o que vem a ser mesmo Common Table Expression (CTE) ?

Uma Common Table Expression (CTE) pode ser vista como um result set tempor�rio que � definido no escopo de execu��o de uma �nica instru��o SELECT, INSERT, UPDATE, DELETE, ou CREATE VIEW. Uma CTE � muito parecida com uma tabela derivada que n�o � armazenada como um objeto e que existe apenas durante a execu��o da consulta. Diferente de uma tabela derivada uma CTE pode ser auto-referenciada e pode ser referenciada v�rias vezes na mesma consulta.

Podemos usar uma CTE para:

Criar uma consulta recursiva;
Substituir uma view quando o uso geral de uma viewo n�o � necess�ria, isto �, voc� n�o tem que armazenar a defini��o em metadados;
Permitir o agrupamento de uma coluna que � derivada de uma subsele��o escalar ou uma fun��o que � ou n�o determinista ou tem acesso externo;
Referenciar a tabela resultante v�rias vezes na mesma declara��o;

Usando uma CTE temos as vantagens de melhorar a legibilidade e facilidade de manuten��o de consultas complexas. A consulta pode ser dividida em blocos l�gicos simples e separados. Estes blocos simples podem ent�o ser utilizados para construir blocos mais complexos at� que a consulta final seja montada.

As CTEs podem ser definidas em rotinas definidas pelo usu�rio, tais como fun��es, procedimentos armazenados, triggers, ou views.

Uma CTE � composta de:

um nome de express�o representando a CTE,
uma lista de colunas opcionais,
e uma consulta definindo a CTE .

Depois que uma CTE � definida, ela pode ser referenciada como uma tabela ou view em uma instru��o SELECT, INSERT, UPDATE, ou DELETE.

Uma CTE tamb�m pode ser usada em uma instru��o CREATE VIEW, como parte de sua declara��o SELECT que a define.

A estrutura b�sica da sintaxe de uma CTE �:

WITH expression_name [ ( column_name [,...n] ) ]

( CTE_query_definition )

A lista de nomes de colunas � opcional apenas se nomes distintos para todas as colunas resultantes forem fornecidos na defini��o da consulta.

A declara��o para executar o CTE �:

SELECT <column_list>

FROM expression_name;

Abaixo um exemplo mais simples poss�vel de defini��o e execu��o de uma CTE executada no SQL Server 2008 Express Edition:

A seguir um exemplo onde referenciarmos mais de uma vez uma CTE definida(ExemploCTE) executada no SQL Server 2008 Express Editon:

Agora um exemplo de uma consulta CTE (NumerosPares) recursiva onde usamos o operador UNION ALL para calcular os n�meros pares at� o n�mero 10:

Vejamos agora um exemplo pr�tico bem b�sico usando uma aplica��o Windows Forms.

Abra o Visual C# 2010 Express Edition e crie um novo projeto do tipo Windows Forms Application com o nome Usando_Common Table Expression;

Inclua no formul�rio padr�o um controle DataGridView (gdvResultado) um controle Button (btnExecutar) e uma Label e um controle TextBox(txtValor), conforme o leiaute abaixo:

Defina os seguintes namespaces no formul�rio:

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

No evento Click do bot�o - Executar Common Table Expression - inclua o c�digo abaixo:

private void btnExecutar_Click(object sender, EventArgs e)
{
//define a string de conex�o usando uma inst�ncia anexada do banco de dados Northwind
string sqlConnectString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\dados\Northwind.MDF;Integrated
Security=True;Connect Timeout=30;User Instance=True";

double valor = Convert.ToDouble(txtValor.Text);

     //monta a Common Table Expression(CTE) usando o banco de dados Northwind e a tabela Products
   string sqlSelect = "WITH ProdutosCategoriasFiltroPorValor (ProductName, CategoryName, UnitPrice) AS " +
"( SELECT p.ProductName, c.CategoryName, p.UnitPrice " +
"FROM Products p " +
"INNER JOIN Categories c ON c.CategoryID = p.CategoryID " +
"WHERE p.UnitPrice > " + valor + ") " +
" SELECT * " +
"FROM ProdutosCategoriasFiltroPorValor " +
"ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC ";
try
{
   // preenche o datatable
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
da.Fill(dt);
//preenche o DataGridView
gdvResultado.DataSource = dt.DefaultView;
}
catch (Exception ex)
{
MessageBox.Show("Erro : " + ex.Message);
}
}

Agora execute o projeto, defina um valor e veja o resultado: