/// <summary>
/// Renders the select statement, including the with clause.
/// </summary>
/// <param name="sb">The SQL text formatter.</param>
public void RenderSqlStatement(SqlBuilderContext sb)
{
if (WithClause != null)
{
WithClause.RenderSql(sb);
}
RootQuery.RenderSql(sb);
}
/// <summary>
/// Renders a temp table or table variable, and inserts the query into it.
/// </summary>
/// <param name="sb">The SQL text formatter.</param>
public void RenderTempTable(SqlBuilderContext sb)
{
if (string.IsNullOrEmpty(Name))
{
throw new Exception("SqlSelectStatement.Name must be set when rendering as a temp table.");
}
bool tableVariable = Name.StartsWith("@");
bool tempTable = Name.StartsWith("#");
if (!tableVariable && !tempTable)
{
throw new Exception("Table name must start with @ or # for a table variable, or temp table, respectively.");
}
// Table declaration
if (tableVariable)
{
sb.AppendOnNewLine("declare " + Name + " as table");
}
else
{
sb.AppendOnNewLine("create table " + Name);
}
// Column declarations
sb.AppendOnNewLine("(");
sb.Indent( );
var first = new First( );
int col = 0;
foreach (SqlSelectItem item in RootQuery.SelectClause.Items)
{
string alias = item.Alias;
if (string.IsNullOrEmpty(item.Alias))
{
alias = "col" + (col++).ToString(CultureInfo.InvariantCulture);
}
// throw new Exception("Queries rendered in temp tables must have an alias for every column.");
if (!first)
{
sb.Append(",");
}
const string colType = "bigint"; // for now
const string options = " primary key"; // for now
sb.AppendOnNewLine(alias + " " + colType + options);
}
sb.EndIndent( );
sb.AppendOnNewLine(")");
// Insert query contents into temp table
if (WithClause != null)
{
WithClause.RenderSql(sb);
}
sb.AppendOnNewLine("insert into " + Name);
sb.Indent( );
RootQuery.RenderSql(sb);
sb.EndIndent( );
}