How to Derive Parameters in Ad Hoc SELECT statements

Hi,

If I have ad hoc SQL statements created by users, which could be parameterized, how could I derive the parmeters at runtime. I cannot use CommandBuilder.DeriveParameters() as that is for StoredProcedures only.

Just use Split on the SQL string? Or is there a better way, such as a third-party .Net Component?

Thanks

John

We need more input on your problem. What does it mean that the users are creating the SQL Strings on their own, how does one look like ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--|||

Hi,

The User's created SQL could be anything (they are writing a report!), but here is a trivial example

SELECT CustomerID, CustomerName FROM Customers WHERE CustomerID = @.CustomerID

Clearly, I have to Pop up a Window to the User for them to supply the actual run time value for @.CustomerID. Just like MS Access or the VS2005's Dataset Designer's Query Builder. Once I have the values I can populate Parameters Collection.

I was hoping someone would have some advise over Parsing SQL strings.

Thanks

John

|||

Best thing would be to regex the string and search for the matches within the string.

Jens K.

|||

Hi,

I've been looking at the General SQL Parser component, and it looks like I can simply get to the Field, Parameter pairs using this. Product is easily found, just do a Web search.

But, out of interests, Jens. Do you have contacts within the Microsoft Dev teams to find out how they do it in VS2005's Dataset Designer's Query Builder and Management Studio's Query Designer?

Rgds

John