3

I am trying to call a stored procedure from delphi7. The procedure also contains parameters with default values. As far as I understand I can call this procedure without explicitly passing values for parameters, having defaults. That's what I did in my delpho code.

But..

The result of appeared to be confusing. The parameter I skipped did get some value. That was the value for the next parameter. And it appeared the last parameter that didn't get its value instead in the end.

I tried to search and found this article. So, it turns out to be, that delphi ignored the names I gave to the parameteres of the TADOStoredProc and just passed them in the order I added them to the parameters' collection, i.e. the last parameter (happily it was also optional and no exception was thrown) wasn't passed to the procedure.

I see the answer by @crefird, suggesting to use TADOQuery instead of TADOStoredProc. But I don't like the approach. I'd better give certain values to all the parameters and don't use the optional parameters feature at all.

So, is there a better solution to make delphi specify parameters to a stored procedure by their names, when using TADOStoredProc?

Community
  • 1
  • 1
horgh
  • 17,918
  • 22
  • 68
  • 123
  • I use the TAdoCommand for storeprocedure – Ravaut123 Sep 13 '12 at 13:58
  • @Ravaut123 In other words you manually specify names of stored procedure parameters and corresponding adocmd.parameters in the `CommandText` property? Smth like this: `AdoCmd.CommandText:='execute MyProc @p1=:p1 , @p2=:p2'`? – horgh Sep 14 '12 at 00:27
  • No CommandType:= cmdStoredProc and then select the storeprocedure in commandtext. And use the parameters collection. – Ravaut123 Sep 14 '12 at 05:58
  • @Ravaut123 I've tested it, and the result is the same as using TADOStoredProc, i.e. the omitted parameter has been given a value...so I failed to find any difference... – horgh Sep 14 '12 at 06:17
  • @Ravaut123 Am I missing smth? – horgh Sep 14 '12 at 06:20
  • Do you want to use an other name for your parametersvalues? – Ravaut123 Sep 14 '12 at 07:12
  • @Ravaut123 I want to omit optional parameters – horgh Sep 14 '12 at 07:20
  • 1
    Before to set the values, reset all your values with null then just add your values for the parameters that you want to execute. Just suggestion. – Ravaut123 Sep 14 '12 at 08:00
  • @Ravaut123 Agree that it is better than writing them namely into sql text. But I wondered, if there's an opportunity to not even mention those parameters owing to they're optional and have the defaults. – horgh Sep 14 '12 at 14:29
  • Probably, that's not possible using Delphi7... – horgh Sep 14 '12 at 14:29
  • Btw th same result would be achieved by TADOStoredProc, i.e. reseting all the parameters. – horgh Sep 14 '12 at 14:30

1 Answers1

0

You could make a TADOStoredProc at design time and have it populate its parameters collection. If you don't have a form to put it on you could use a DataModule.

Years ago I learnt the same lesson as you - the parameter names are ignored. Maintaining and growing the codebase that uses Delphi & SQL Server over the past ten years has been made easier by putting any new SP parameters at the END of the parameter list and, if desired, making them optional.

I have seem people make the TADOStoredProc object and then call its refreshParams (or refreshParameters - not sure of the name) method. That would involve a whole extra trip to the database which is probably to be avoided.

Ian Yates
  • 1,324
  • 13
  • 24