Test if Dynamic SQL Statement Requires a Parameter 1

[ToddHayden]

I'm pulling statements from a centralized location and need to test if the statement takes parameters. My issue is users are using linked tables and fields may be missing. My thinking is, if the statement takes parameters, then I am missing a field, and can report the parameter fields as missing fields to the user.

 

[lameid]

This is very vague... you are connection to what Database Server and want to know the parameters for what object, which office application?

That aside, the only thought I have for connection to an RDBMS...
The ADO Command object which you might use to call a T-Sql stored procedure, I believe has a parameters collection... I'm trying to remember if it is enumerable... I want to say no, you just have to know the parameters... I may be mistaken, worth a shot if you are going down that path...

Then again maybe your doing something completely different. Specifics will be helpful.

 

[ToddHayden]

Thanks lameid

I'm using Access 2010

All the SQL is in tables, I use ADO to pull and execute the SQL. The SQL is not built on the fly, it is just pulled as needed.

I don't use ADODB.Command very much, I'll look into it.

If QueryDefs.Parameters is enumerable, I could add the SQL to the collection and then delete it when done. Just thinking it out.

 

[lameid]

My apologies... I thought I was in the Office VBA forum... I'm glad I figured out my Excel VBA question rather than posting here.

Access, Querydefs, DAO. Always use DAO for performance in Access when working with native Access objects. Use ADO for other databases.

I suspect you might have to have a parameters clause to enumerate the collection if possible. - I'm curious about the results.

You could also parse out the where and having clauses and look for things in Square Brackets. If you make certain all fields use Fully Qualified Names, you could assume anything that is not preceded by something dot on the right side, is a parameter.


Or... You could just put query names in a table and parameters in a related table, and make a form to drive the whole thing. It is not that difficult to write code to add controls to a new form to use as criteria.

 

[madonnac]

With this code to run a stored procedure:

Set cmdl = New ADODB.Command

With cmdl
    .ActiveConnection = StrCon ' Connection to the Database
    'set COMMAND timeout property - query can time out on either the connection OR the command
    .CommandTimeout = 0
    .CommandText = "spCrossTabRun"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh

after the refresh, .parameters.count gives me the number of parameters.
note that the return value is parameter 0, the subsequent values are the names of the parameters passed to the procedure.

In my case, the value of my parameters are:

[pre]?cmdl.parameters(0).Name
@RETURN_VALUE
?cmdl.parameters(1).Name
@QueryTemplate
?cmdl.parameters(2).Name
@AggregateFunction
?cmdl.parameters(3).Name
@PivotColumn[/pre]

 

[ToddHayden]

That looks like it will work perfect, thanks!!!!!