-2

I suffer the correct calling data to one of my databases (Parameter of stored procedure not found)

For further debugging it might be helpful to extract a list of available stored procedures inside my database and the params to call each procedure.

How to get this information from the database using Delphi code?

  • DELPHI XE 2
  • Database SQL Server 2008
  • ADO
Community
  • 1
  • 1
user1769184
  • 1,571
  • 1
  • 19
  • 44
  • 1
    What server back-end are you using, and which Delphi components are you you using to access the database? You should add the same info as in your earlier q regarding these ... – MartynA Aug 09 '15 at 20:35
  • 1
    The syntax to query stored procedures depends entirely on the database. For example, with MSSQL you might use `SELECT [Routine_Name] FROM [INFORMATION_SCHEMA].[ROUTINES] WHERE [ROUTINE_TYPE] = 'PROCEDURE'` – paulsm4 Aug 09 '15 at 20:37
  • 1
    This question has absolutely nothing to do with Delphi. It's strictly a MS SQL Server SQL question. The same query against system databases would work in Delphi or any other language capable of using ADO. – Ken White Aug 10 '15 at 02:35

1 Answers1

2

The following query will list all user defined stored procs (including their parameters and parameter types) in your default database:

SELECT
  sp.name,
  p.name AS Parameter,
  t.name AS [Type]
FROM
 sys.procedures sp
  LEFT JOIN sys.parameters p
    ON sp.object_id = p.object_id
  LEFT JOIN sys.types t
    ON p.system_type_id = t.system_type_id
WHERE 
  is_ms_shipped = 0 
ORDER BY
  sp.name

Put that into an ADOQuery object and set it to Active. (Updated answer with LEFT JOINS so that it includes SPs without parameters).

JohnS
  • 1,942
  • 1
  • 13
  • 16