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); }