private void AppendFullTemplate(StringBuilder sb)
{
try
{
string storedProcedureName = "gen_" + Globals.GetPascalName(_model, _currentTable) + "SelectByModifiedDateRange";
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + storedProcedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [dbo].[" + storedProcedureName + "]");
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("SET QUOTED_IDENTIFIER ON ");
sb.AppendLine("GO");
sb.AppendLine("SET ANSI_NULLS ON ");
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("CREATE PROCEDURE [dbo].[" + storedProcedureName + "]");
sb.AppendLine("(");
sb.AppendLine(" @start_date datetime,");
sb.AppendLine(" @end_date datetime");
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine("SET NOCOUNT ON;");
sb.AppendLine();
string fieldName = "[" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + _model.Database.ModifiedDateColumnName + "]";
//SELECT CLAUSE
sb.AppendLine("SELECT");
sb.AppendLine(" " + Globals.BuildSelectList(_currentTable, _model, true));
sb.AppendLine("FROM");
sb.AppendLine(_currentTable.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
sb.AppendLine("(((" + fieldName + " IS NULL) AND (@start_date IS NULL)) OR (@start_date <= " + fieldName + ")) AND ");
sb.AppendLine("(((" + fieldName + " IS NULL) AND (@end_date IS NULL)) OR (@end_date >= " + fieldName + "))");
sb.AppendLine("GO");
sb.AppendLine();
}
catch (Exception ex)
{
throw;
}
}