// https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql
public static IReadOnlyDictionary <string, SqlParamInfo> ParseProcedure(string sql)
{
if (string.IsNullOrWhiteSpace(sql))
{
return(_empty);
}
// Tokenize
var tokens = SqlTokenizer.Tokenize(sql, true);
using (var tokenizer = tokens.GetEnumerator())
{
var more = tokenizer.MoveNext();
if (!more)
{
return(_empty);
}
// CREATE
if (!ParseLiteral(tokenizer, "CREATE"))
{
return(_empty);
}
// PROC | PROCEDURE
if (!ParseLiteral(tokenizer, "PROC", "PROCEDURE"))
{
return(_empty);
}
// [Name] or "Name"
if (!ParseModuleName(tokenizer, out var schema, out var name))
{
return(_empty);
}
// (
var parenthesized = ParseSymbol(tokenizer, '(');
var parms = new Dictionary <string, SqlParamInfo>();
while (true)
{
// Exit loop if end of parameter block
// )
if (parenthesized &&
ParseSymbol(tokenizer, ')'))
{
break;
}
// AS, WITH or FOR
if (ParseLiteral(tokenizer, "AS", "WITH", "FOR"))
{
break;
}
// Read next parameter
// @param
if (!ParseParamName(tokenizer, out var pname))
{
return(_empty);
}
// AS
ParseLiteral(tokenizer, "AS");
// Foo.DECIMAL(1,2)
if (!ParseTypeName(tokenizer, out var tschema, out var tname))
{
return(_empty);
}
// VARYING
ParseLiteral(tokenizer, "VARYING");
// = <default>
var hasDefault = ParseDefault(tokenizer, out var isNullable);
// OUT | OUTPUT
var dir = ParameterDirection.Input;
if (ParseLiteral(tokenizer, "OUT", "OUTPUT"))
{
dir = ParameterDirection.InputOutput;
}
// READONLY
var isReadOnly = ParseLiteral(tokenizer, "READONLY");
// Done
var parm = new SqlParamInfo(isNullable, hasDefault, isReadOnly, dir);
parms.Add(pname, parm);
// ,
ParseSymbol(tokenizer, ',');
}
return(parms);
}
}
// https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
public static IReadOnlyDictionary <string, SqlParamInfo> ParseFunction(string sql)
{
if (string.IsNullOrWhiteSpace(sql))
{
return(_empty);
}
// Tokenize
var tokens = SqlTokenizer.Tokenize(sql, true);
using (var tokenizer = tokens.GetEnumerator())
{
var more = tokenizer.MoveNext();
if (!more)
{
return(_empty);
}
// CREATE
if (!ParseLiteral(tokenizer, "CREATE"))
{
return(_empty);
}
// FUNCTION
if (!ParseLiteral(tokenizer, "FUNCTION"))
{
return(_empty);
}
// [Name] or "Name"
if (!ParseModuleName(tokenizer, out var schema, out var name))
{
return(_empty);
}
// (
if (!ParseSymbol(tokenizer, '('))
{
return(_empty);
}
var parms = new Dictionary <string, SqlParamInfo>(StringComparer.Ordinal);
while (true)
{
// Exit loop if end of parameter block
// )
if (ParseSymbol(tokenizer, ')'))
{
break;
}
// RETURNS
if (ParseLiteral(tokenizer, "RETURNS"))
{
break;
}
// Read next parameter
// @param
if (!ParseParamName(tokenizer, out var pname))
{
return(_empty);
}
// AS
ParseLiteral(tokenizer, "AS");
// Foo.DECIMAL(1,2)
if (!ParseTypeName(tokenizer, out var tschema, out var tname))
{
return(_empty);
}
// = default
var hasDefault = ParseDefault(tokenizer, out var isNullable);
// READONLY
var isReadOnly = ParseLiteral(tokenizer, "READONLY");
// Done
var parm = new SqlParamInfo(isNullable, hasDefault, isReadOnly, ParameterDirection.Input);
parms.Add(pname, parm);
// ,
ParseSymbol(tokenizer, ',');
}
return(parms);
}
}