Query to find Number of Parameters in a Stored Procedure or Function in Sql Server?

Question

Well, if i want to find parameter count of any stored procedure or function inside SQL SERVER, what is the correct way to do it.

Your help would be appreciated. thanks.

Solution 1

Try the following query to get a list of all parameters for a stored procedure. Change the select to a COUNT(*) if you just want the number of parameters.

SELECT 
    p.name AS Parameter,        
    t.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters p 
    ON sp.object_id = p.object_id
JOIN sys.types t
    ON p.system_type_id = t.system_type_id
WHERE sp.name = '<name>'

Solution 2

INFORMATION_SCHEMA.PARAMETERS should be all you need...

SELECT  *
FROM    INFORMATION_SCHEMA.PARAMETERS

Comments

[Syscall]

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, this reduces the readability of both the code and the explanations!