CODEWITHSUNDEEP
sql-serverdelphistored-procedures
Y. Adam
Y. Adam

Reputation: 13

How to get the SQL content of the stored procedure in Delphi code?

I'm debugging Delphi code with Delphi 7 IDE, the code is not mine. My goal is to understand how this code works. This will help me to write my application which will interface with this code.

I never used Delphi in my life. I'm a C, C++, C# programmer.

I'm able to see the output (result) of the stored procedure but I need to see the content of the stored procedure (I mean the SQL statement). I looked in SQL Server side but I didn't find anything.

The stored procedure is called in this function :

function TDBDM.getID( prKeyName : string ) : integer;
begin
  try
    spGetSN.Parameters.ParamValues['@prKeyname'] := prKeyName;
    spGetSN.open;
    result := spGetSN.Fields[0].AsInteger;
  finally
    spGetSN.close;
  end;
end;

and the stored procedure 'spGetSN' is declared here :

unit dmDB;
interface
uses
   SysUtils, Classes, Forms, ADODB, DB, TypInfo, Dialogs, StdCtrls,
   Controls,Registry, Windows;
type
   eConnectionType = (ctSN);

TDBDM = class(TDataModule)
    ADOConnectionSN: TADOConnection;
    spGetSN: TADOStoredProc;
    ADOQueryGetPartNoFromRMA: TADOQuery;
    ADOQueryGetPartNoFromOrder: TADOQuery;
    procedure ADOConnectionSNBeforeConnect(Sender: TObject);
    procedure DataModuleCreate(Sender: TObject);
 private
    { Private declarations }
    function GetConnection( prConnectionType : eConnectionType ) :   
    TADOConnection;
 public
    { Public declarations }

    function getID( prKeyName : string ) : integer;
    function getSN( prKeyName : string ) : string;
    function getCommaValues( prSelect : string; prConnection :  
        eConnectionType = ctSN ) : string;

    property pConnection[prType : eConnectionType] : TADOConnection read 
       GetConnection; default;
 end;

 var
    DBDM: TDBDM;

 implementation
 uses dmRegistryClasses;
 {$R *.dfm}

The text of the object TADOSTOREDPROC 'spGetSN' as defined in the IDE is :

  object spGetSN: TADOStoredProc
    Connection = ADOConnectionSN
    ProcedureName = 'spGetSN;1'
    Parameters = <
      item
        Name = '@RETURN_VALUE'
        DataType = ftInteger
        Direction = pdReturnValue
        Precision = 10
        Value = Null
      end
      item
        Name = '@prKeyname'
        Attributes = [paNullable]
        DataType = ftWideString
        Size = 30
        Value = Null
      end>
    Left = 268
    Top = 80
  end

How can I print or watch the SQL code of this stored procedure in Delphi debugger? Thanks

Upvotes: 1

Views: 3385

Answers (2)

kobik
kobik

Reputation: 21242

You could query the sys.sql_modules table e.g.:

function MSSQL_GetModuleText(conn: TADOConnection; const ModuleName: WideString): WideString;
var
  SQL: WideString;
  rs: Variant;
begin
  SQL := Format('SELECT [definition] FROM sys.sql_modules WHERE [object_id]=OBJECT_ID(N''%s'')', [ModuleName]);
  rs := conn.Execute(SQL);
  Result := VarToWideStr(rs.Fields[0]); 
end;

Usage:

S := MSSQL_GetModuleText(ADOConnectionSN, 'dbo.spGetSN');

Upvotes: 3

MartynA
MartynA

Reputation: 30715

The following should give you an idea how to do it:

  • Add a TAdoQuery called AdoQuerySP to your form and set its connection to whatever AdoConnection component you're using at the moment.

  • Add a TMemo to the form.

  • Add a TButton to the form and set its OnClick handler to execute the code below (after changing checklibuse to the name of your SP).

Code:

procedure TForm1.GetSPDefinition;
var
  S : String;
begin
  S := '';
  AdoQuerySP.Sql.Text := 'exec sp_helptext checklibuse';  //  sp_helptext
  //  is a Sql Server system stored proc to retrieve the definition of 
  // a stored proc.  The definition is returned as a series of rows which need to be 
  //  concatenated together as in the `while ...` loop below.
  AdoQuerySP.Open;
  while not AdoQuerySP.Eof do begin
    if S <> '' then
      S := #13#10 + S;
    S := S + AdoQuerySP.FieldByName('Text').AsString;
    AdoQuerySP.Next;
  end;
  //  The retrieved definition is typically preceded by a number of blank lines
  //  which the call to Trim() remove.
  Memo1.Lines.Text := Trim(S);
end;

Upvotes: 4

Related Questions