I'm pretty green at Delphi. I have a program that must call a MS SQL stored procedure before executing the rest of its logic. Here's the code I've written (changes have been made here and there to protect confidentiality):

theProcedure                :=  TStoredProc.Create( NIL ) ;
  theProcedure.DatabaseName   :=  theTDatabase.DatabaseName ;
  theProcedure.SessionName    :=  theTDatabase.SessionName ;
  theProcedure.StoredProcName :=  'dbo.my_stored_proc' ;
  theProcedure.Prepare;
  theProcedure.ExecProc;

I get an error back that says: No parameter type for parameter ‘@Foo’

So I tried this

theProcedure                :=  TStoredProc.Create( NIL ) ;
  theProcedure.DatabaseName   :=  theTDatabase.DatabaseName ;
  theProcedure.SessionName    :=  theTDatabase.SessionName ;
  theProcedure.StoredProcName :=  'dbo.my_stored_proc' ;
  theProcedure.ParamByName('@Foo').AsString := 'bar' ;
  theProcedure.Prepare;
  theProcedure.ExecProc;

I then get an error that says "Parameter ‘@Foo’ not found"

I tried leaving off the @. Same error as above (sans the @ sign).

Google doesn't seem to help either. It's as frustrating as frustration can get. What am I doing wrong?

I would have expected

theProcedure.ParamByName('Foo').AsString := 'bar' ;

to work, however, only other thing is potentail case sensitivity - anything visible SQL end?

Hi ,
I tried ur answer. but am getting 'no parameter for parameter type foo'. am using oracle as back end. Please help out on this.
Thanks in advance.

pls ignore my previous post.
I tried ur answer but i come up with an error.
The error says 'No parameter type for parameter <variable name>' .
I use oracle as back end. I use the varaiable name in my delphi code as like in oracle stored procedure.

Hi ,
I cheked the case too.. variable name in delphi and stored procedure has the same value.

Since you're creating this stored procedure at run time then you need to create the parameters as well. As far as I know at design time if you specify the sproc name with a valid connection and edit the parameters it fetches them from the procedure and autopopulates the list. In this case the "query" you have is just the name of the stored procedure, and when you call prepare there are no variables to prepare.

If your query was "Select * From Table Where Number = :Identifier" you could call ParamByName('Identifier'). Try to manually add the parameters to the stored procedure with TParam.Create()

This article has been dead for over six months. Start a new discussion instead.