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_idwhere 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_idWHERE p.name = @ProcedureName