Stored Procedure Question

[GameboyHippo]

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?

[vijaya.vpl]

me too getting the same kind of error.
can anyone pls help out on this

[LizR]

I would have expected

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

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

[vijaya.vpl]

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.

[vijaya.vpl]

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.

[LizR]

then please check the casing of your variable.

[vijaya.vpl]

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

[LizR]

Can you post the stored proc contents, and the delphi query

[sknake]

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()