Calling SQL Server stored procs from Delphi with arguments?

Question

I'm trying to call SP_SPACEUSED from Delphi 2010 using ADO. I can call it without arguments by using TSQLStoredProc, and setting the StoredProcName. This gives me the database size. I now need a specific table size, and SP_SPACEUSED takes a single arugument, @objname. How do I pass this as an argument. I have tried passing this as a parameter, but this doesn't work. Is it a parameter? Can I do this from Delphi?

Answer 1

Quick and dirty example (in D6 but it should work without any change in 2010):

var
  I: Integer;
  adStoredProc : TADOStoredProc;
begin
  adStoredProc := TADOStoredProc.Create(nil);
  try
    adStoredProc.Connection := ADOConnection1;
    adStoredProc.ProcedureName := 'SP_SPACEUSED';
    adStoredProc.Parameters.Refresh;
    for I := 0 to adStoredProc.Parameters.Count - 1 do    // Iterate
    begin
      if Sametext(adStoredProc.Parameters[i].Name,'@objname') then
        adStoredProc.Parameters[i].Value := 't_config';
    end;    // for
    adStoredProc.Open;
    for I := 0 to adStoredProc.FieldCount - 1 do    // Iterate
    begin
      memo1.Lines.Append(format('%s : %s', [adStoredProc.Fields[i].Fieldname, adStoredProc.Fields[i].AsString]));
    end;    // for
  finally
    FreeAndNil(adStoredProc);
  end;
end;