private void CreateFunction(UserFunction userFunction)
{
StringBuilder sb = new StringBuilder();
sb.Append("CREATE FUNCTION ");
sb.AppendFormat("[{0}]", userFunction.Name);
if (userFunction.Parameters != null)
{
sb.Append("(");
foreach (var param in userFunction.Parameters)
{
if (param == userFunction.Parameters[0])
sb.AppendLine();
else
sb.AppendLine(",");
sb.AppendFormat(" {0} {1}{2}{3}",
param.Name,
GetTypeDescriptor(param),
!string.IsNullOrEmpty(param.DefaultValue) ? " = " + param.DefaultValue : "",
param.Direction == ProcedureParameterDirection.Out ? " OUT " : "");
}
sb.Append(")");
}
sb.AppendFormat("RETURNS {0} TABLE ", userFunction.ReturnTable.Name);
if (userFunction.ReturnTable.Columns != null)
{
sb.Append("(");
foreach (var column in userFunction.ReturnTable.Columns)
{
if (column == userFunction.ReturnTable.Columns[0])
sb.AppendLine();
else
sb.AppendLine(",");
sb.AppendFormat(" {0} {1}",
column.Name,
GetTypeDescriptor(column));
}
sb.Append(")");
}
sb.AppendLine(" AS ");
sb.AppendLine(" BEGIN ");
sb.AppendLine(ReplaceMacros(userFunction.Body));
sb.AppendLine(" RETURN END ");
string oldCreateStatement = (string)ExecuteScalar("SELECT OBJECT_DEFINITION(OBJECT_ID(@p1));", userFunction.Name);
string createStatement = sb.ToString();
if (!string.IsNullOrEmpty(oldCreateStatement))
{
if (oldCreateStatement == createStatement)
{
return;
}
ExecuteNonQuery("DROP FUNCTION @p1", userFunction.Name);
}
log("Creating Procedure: " + userFunction.Name);
ExecuteNonQuery(createStatement);
ExecuteNonQuery("GRANT SELECT ON [" + userFunction.Name + "] TO mine_users");
RunCallback(userFunction, CallbackAction.After);
}
private void CreateFunction(UserFunction userFunction)
{
StringBuilder sb = new StringBuilder();
sb.Append("CREATE FUNCTION ");
sb.AppendFormat("[{0}]", userFunction.Name);
if (userFunction.Parameters != null)
{
sb.Append("(");
foreach (var param in userFunction.Parameters)
{
if (param == userFunction.Parameters[0])
{
sb.AppendLine();
}
else
{
sb.AppendLine(",");
}
sb.AppendFormat(" {0} {1}{2}{3}",
param.Name,
GetTypeDescriptor(param),
!string.IsNullOrEmpty(param.DefaultValue) ? " = " + param.DefaultValue : "",
param.Direction == ProcedureParameterDirection.Out ? " OUT " : "");
}
sb.Append(")");
}
sb.AppendFormat("RETURNS {0} TABLE ", userFunction.ReturnTable.Name);
if (userFunction.ReturnTable.Columns != null)
{
sb.Append("(");
foreach (var column in userFunction.ReturnTable.Columns)
{
if (column == userFunction.ReturnTable.Columns[0])
{
sb.AppendLine();
}
else
{
sb.AppendLine(",");
}
sb.AppendFormat(" {0} {1}",
column.Name,
GetTypeDescriptor(column));
}
sb.Append(")");
}
sb.AppendLine(" AS ");
sb.AppendLine(" BEGIN ");
sb.AppendLine(ReplaceMacros(userFunction.Body));
sb.AppendLine(" RETURN END ");
string oldCreateStatement = (string)ExecuteScalar("SELECT OBJECT_DEFINITION(OBJECT_ID(@p1));", userFunction.Name);
string createStatement = sb.ToString();
if (!string.IsNullOrEmpty(oldCreateStatement))
{
if (oldCreateStatement == createStatement)
{
return;
}
ExecuteNonQuery("DROP FUNCTION @p1", userFunction.Name);
}
log("Creating Procedure: " + userFunction.Name);
ExecuteNonQuery(createStatement);
ExecuteNonQuery("GRANT SELECT ON [" + userFunction.Name + "] TO mine_users");
RunCallback(userFunction, CallbackAction.After);
}
