Say I've got some throw-away sample procedure that looks like this:
CREATE procedure simpletestproc_prc
(
@companyId int,
@objectid float = 5.678,
@personname varchar(255) = 'hello world'
) as
select @companyId + 10, @objectid, @personname
I can use the below query to get the types and names of all the parameters:
SELECT
*
FROM sys.procedures sp
INNER JOIN sys.parameters parm
ON sp.object_id = parm.object_id
INNER JOIN sys.types typ
ON parm.system_type_id = typ.system_type_id
WHERE sp.name = 'simpletestproc_prc'
order by parameter_id
But none of those columns (even parm.default_value) has 5.678 or 'hello world'. How can I retrieve those values? If it matters, you can assume either SQL Server 2005 or 2008.