how to get parameterless procedure ?

[Lloyd Dupont]

I already try something like that:
SELECT INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME,
COUNT(INFORMATION_SCHEMA.PARAMETERS.PARAMETER_NAME) AS Expr1
FROM INFORMATION_SCHEMA.ROUTINES INNER JOIN
INFORMATION_SCHEMA.PARAMETERS ON
INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME =
INFORMATION_SCHEMA.PARAMETERS.SPECIFIC_NAME
GROUP BY INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME


unfortunately this gave me all the procedure with parameter (and the number
of parameters), but none of the procedure WITHOUT parameters do appear.
any clues on how to get procedure without parameters ?

 

[Lloyd Dupont]

well I found that, which gives me also the one with 0 parameter, then I have
to cull out the other while reading the result set.
any idea how to cull them out in the select ?
///-----------------------
SELECT INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME AS Script,
COUNT(INFORMATION_SCHEMA.PARAMETERS.PARAMETER_NAME)
AS NumParam
FROM INFORMATION_SCHEMA.ROUTINES LEFT OUTER JOIN
INFORMATION_SCHEMA.PARAMETERS ON
INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME =
INFORMATION_SCHEMA.PARAMETERS.SPECIFIC_NAME
GROUP BY INFORMATION_SCHEMA.ROUTINES.SPECIFIC_NAME

 

[Miha Markic]

Hi Lloyd,

Add HAVING clause at the end:
HAVING COUNT(INFORMATION_SCHEMA.PARAMETERS.PARAMETER_NAME) = 0
(if you need only parameterless)

 

[Lloyd Dupont]

Thanks Miha, works great !

Hi Lloyd,

Add HAVING clause at the end:
HAVING COUNT(INFORMATION_SCHEMA.PARAMETERS.PARAMETER_NAME) = 0
(if you need only parameterless)