1

Possible Duplicate:
How can I retrieve a list of parameters from a stored procedure in SQL Server

I'm using Massive to create a generic stored procedure executor. Basically, I have a function that takes the name of a procedure, and a variable number of parameters. Then, I blindly attempt to execute that stored procedure with the passed parameters and either return the result, or handle errors.

My question is: is there a way for me to determine what parameters the stored procedure expects, before calling it?

Community
  • 1
  • 1
xdumaine
  • 9,362
  • 6
  • 57
  • 101

1 Answers1

5

Try this:

SELECT 
    ProcedureName = pr.Name,
    ParameterName = p.Name,
    Typename = ty.name 
FROM 
    sys.parameters p
INNER JOIN 
    sys.procedures pr ON p.object_id = pr.object_id
INNER JOIN 
    sys.types ty ON p.user_type_id = ty.user_type_id
WHERE   
    pr.Name = '.....'   -- put your stored procedure name here
ORDER BY 
    p.Name, p.parameter_id

This will inspect the system catalog views and show you the parameters and their type for a given procedure.

The system catalog view contains additional info (like whether the parameter has a default value and if so, what it is; whether it's a XML parameter and so forth) - check out the MSDN documentation for all details!

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425