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
  • 10,096
  • 6
  • 62
  • 103

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
  • 732,580
  • 175
  • 1,330
  • 1,459
  • So you mean I should execute this with massive, and expand the result into a list to use, right? – xdumaine Nov 13 '12 at 17:34
  • @roviuser: this SQL query will get you the info you need - how you fetch that, is totally up to you ... – marc_s Nov 13 '12 at 17:35
  • +1 You might also include things like whether the param `is_output` and optional (`has_default_value` = 1). `is_readonly` might also be useful in 2008+ if TVPs are in use. – Aaron Bertrand Nov 13 '12 at 17:52
  • 1
    Thanks. Your WHERE clause should be `pr.Name`. – xdumaine Nov 13 '12 at 17:54