How can I determine the parameters required by an

问题:

Basically, I'm looking for an equivalent to SqlCommandBuilder.DeriveParameters that will work for arbitrary T-SQL.

For example, this query requires one parameter:

SELECT @Foo [Foo], '@Bar' [Bar], @Baz [Baz]

I basically need to extract:

new[] { "Foo", "Baz" }

From above. I could build a SQL parser, but I have an open connection to SQL server, so I'd prefer to use an existing option if possible.

---

Edit:

There has to be a way to do this, because SQL Server's Business Intelligence Development Studio is able to do this very successfully.

---

Edit 2:

SQL BIDS is executing this command in order to describe the results:

exec sp_executesql N'SET FMTONLY OFF;SET FMTONLY ON;SELECT @Foo [Foo], ''@Bar'' [Bar], @Baz [Baz]',
    N'@Foo sql_variant,@Baz sql_variant',
    @Foo=NULL,@Baz=NULL

Which explains how it can determine the columns, but it may be just string parsing to get the parameters...

回答1:

You can use Microsoft.Data.Schema.ScriptDom for this. I'm not familiar with it myself but I just tried parsing your statement and could see that the variables were accessible in the ScriptTokenStream collection (not sure if there is an easier way of getting hold of them or not)

Edit: Posting the OP's own code from the comments!

    var sql = "SELECT @Foo [Foo], '@Bar' [Bar], @Baz [Baz]";
    var p = new TSql100Parser(true);
    var errors = new List<ParseError>();
    var tokens = p.GetTokenStream(new StringReader(sql), errors);
    if (errors.Count == 0)
    {
        var variables = (from t in tokens where t.TokenType == 
                       TSqlTokenType.Variable select t.Text).ToArray();
    }

SQL Server 2012 also introduces sp_describe_undeclared_parameters which is of relevance but fails with this example as it needs to be able to deduce datatypes.

回答2:

Use RegEx and parse the parameters, I quickly tested this, so not sure if will work, might need modiciation.

[^']@([^[,]+)

If you need to modify the regular expression string, I find this site very helpful: http://regexlib.com/RETester.aspx

public Form1()
    {
        InitializeComponent();

        string query = "SELECT @Foo [Foo], '@Bar' [Bar], @Baz [Baz] ";
        Regex r = new Regex(@"[^']@([^\[,]+)");
        MatchCollection collection = r.Matches(query);
        string[] array = new string[collection.Count];

        for (int i = 0; i < collection.Count; i++)
        {
            array[i] = collection[i].Value.Trim();

            // If you want the string[] populated without the "@"
            // array[i] = collection[i].Groups[1].Value.Trim();
        }
    }

回答3:

I think SqlCommandBuilder.DeriveParameters works by asking SQL Server what the parameters are, which is not an option in this case. Some sort of string processing like a RegEx is probably your best bet.

This Regex might be able to do it

@([_a-zA-Z]+) 

Haven't tried it myself, but its from this similar question

It will also find parameters in comments and quoted strings but its a start.