How to get stored procedure parameters details?

select  
   'Parameter_name' = name,  
   'Type'   = type_name(user_type_id),  
   'Length'   = max_length,  
   'Prec'   = case when type_name(system_type_id) = 'uniqueidentifier' 
              then precision  
              else OdbcPrec(system_type_id, max_length, precision) end,  
   'Scale'   = OdbcScale(system_type_id, scale),  
   'Param_order'  = parameter_id,  
   'Collation'   = convert(sysname, 
                   case when system_type_id in (35, 99, 167, 175, 231, 239)  
                   then ServerProperty('collation') end)  

  from sys.parameters where object_id = object_id('MySchema.MyProcedure')

select * from sys.parameters 
inner join sys.procedures on parameters.object_id = procedures.object_id 
inner join sys.types on parameters.system_type_id = types.system_type_id AND parameters.user_type_id = types.user_type_id
where procedures.name = 'sp_name'

For a supplied procedure name, the following query lists all of its parameters and their order along with their type and the type's length (for use with VARCHAR, etc.)

Replace procedure_name with the name of your procedure.

DECLARE @ProcedureName VARCHAR(MAX) = 'procedure_name'

SELECT
    pa.parameter_id AS [order]
    , pa.name AS [name]
    , UPPER(t.name) AS [type]
    , t.max_length AS [length] 
FROM sys.parameters AS pa 
INNER JOIN sys.procedures AS p on pa.object_id = p.object_id 
INNER JOIN sys.types AS t on pa.system_type_id = t.system_type_id AND pa.user_type_id = t.user_type_id
WHERE p.name = @ProcedureName

There are the system tables, like sys.objects or sys.sysobjects.

Or you could also look at INFORMATION_SCHEMA, specifically INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.ROUTINE_COLUMNS.

Because it is in the ANSI-standard INFORMATION_SCHEMA, there are less SQL Server specific quirks. IMHO it is easier to understand for most things.

The following Query worked for me:

SELECT * FROM sys.parameters sp1, sys.procedures sp2 WHERE sp1.object_id = sp2.object_id

For more specific result with parameter order:

SELECT * FROM sys.parameters sp1, sys.procedures sp2, sys.types st WHERE sp1.object_id = sp2.object_id AND sp2.name = 'usp_nameofstoredprocedure' AND sp1.user_type_id = st.user_type_id ORDER BY sp1.parameter_id asc;