Find which parameters of stored procedures are nullable (optional)

Question

I know I can use the following query to find all stored procedures and their parameters :

SELECT 
    r.*, p.*
FROM
    INFORMATION_SCHEMA.ROUTINES AS r INNER JOIN
    INFORMATION_SCHEMA.PARAMETERS AS p 
    ON r.SPECIFIC_SCHEMA = p.SPECIFIC_SCHEMA AND r.SPECIFIC_NAME = p.SPECIFIC_NAME
WHERE     (r.ROUTINE_TYPE = N'PROCEDURE')

From this I can see (almost) all info about parameters, but I don't know how to find which of them are nullable or have default values.

Is there any way to find this?

Thank you

Martin Smith · Accepted Answer · 2013-02-16T19:00:13.600

9

All parameters are nullable. There is no syntax to specify that NULL should not be passed.

To find out those that have defaults you can inspect sys.parameters for CLR stored procedures

SELECT has_default_value,name
FROM sys.parameters
where object_id=object_id('YourProc')

Unfortunately this column is not currently populated correctly for TSQL stored procedures and the only ways involve parsing the object definition.

edited Feb 16 '13 at 19:00

answered Oct 14 '11 at 20:17

Martin Smith

438,706
87
741
845

Actually parameters are not nullable unless you declare them the be when creating the procedure e.g. parametername = null (assuming SQL Server) – Grixxly Jun 20 '12 at 20:56
@Tony that just gives them a default value of null if no value is supllied. You can pass an explicit value of null for **all** parameters. Example `EXEC('CREATE PROC usp_Foo @P1 INT AS SELECT @P1'); EXEC usp_Foo NULL` – Martin Smith Jun 21 '12 at 06:08
Does that query work for determining whether a parameter has a default value? As far as I can tell it will only work for CLR objects [ http://msdn.microsoft.com/en-us/library/ms176074.aspx , http://stackoverflow.com/questions/14652361/determine-whether-sp-parameter-has-a-default-value-in-t-sql ] – Sanjay Uttam Feb 01 '13 at 19:31
@SanjayU - Yes you're correct. I just assumed `has_default_value` would do what it said on the tin. – Martin Smith Feb 16 '13 at 18:53
Any clue where that issue originally in the "connect" link now resides? This really seems like a trivial thing for MS to address, and I'd like to upvote the issue! :/ – Jonathan B. Jul 21 '18 at 20:15
1

@JonathanB. archived copy https://web.archive.org/web/20160702213604/http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=234143 and migrated without the votes https://feedback.azure.com/forums/908035-sql-server/suggestions/32891455-populate-has-default-value-in-sys-parameters – Martin Smith Jul 21 '18 at 20:47

score 1 · Answer 2 · answered Apr 05 '13 at 06:08

Try this query to get all defaul values for specific stored procedure -

SELECT  
      data3.[object_name]
    , data3.name
    , [default_value] = REVERSE(RTRIM(SUBSTRING(
            data3.rtoken
        , CASE 
            WHEN CHARINDEX(N',', data3.rtoken) > 0 
                THEN CHARINDEX(N',', data3.rtoken) + 1
            WHEN CHARINDEX(N')', data3.rtoken) > 0 
                THEN CHARINDEX(N')', data3.rtoken) + 1
            ELSE 1 
            END
        , LEN(data3.rtoken)
        )))
FROM (
    SELECT  
          data2.name
        , data2.[object_name]
        , rtoken = CASE WHEN data2.ptoken LIKE '%=%' 
            THEN REVERSE(SUBSTRING(ptoken
                        , CHARINDEX('=', ptoken, 1) + 1
                        , LEN(data2.ptoken))
                    )
            END
    FROM (
        SELECT  
              data.name
            , data.[object_name]
            , ptoken = SUBSTRING(
                    data.tokens
                , token_pos + name_length + 1
                , ISNULL(ABS(next_token_pos - token_pos - name_length - 1), LEN(data.tokens))
            )
        FROM (
            SELECT  
                  sm3.tokens
                , sm3.[object_name]
                , p.name
                , name_length = LEN(p.name)
                , token_pos = CHARINDEX(p.name, sm3.tokens)
                , next_token_pos = CHARINDEX(p2.name, sm3.tokens)
            FROM (
                SELECT 
                      sm2.[object_id]
                    , sm2.[object_name]
                    , tokens = REVERSE(SUBSTRING(sm2.tokens, ISNULL(CHARINDEX('SA', sm2.tokens) + 2, 0), LEN(sm2.tokens))) 
                FROM (
                    SELECT 
                          sm.[object_id]
                        , [object_name] = s.name + '.' + o.name
                        , tokens = REVERSE(SUBSTRING(
                                        sm.[definition]
                                    , CHARINDEX(o.name, sm.[definition]) + LEN(o.name) + 1
                                    , ABS(CHARINDEX(N'AS', sm.[definition]))
                                    )  
                        ) 
                    FROM sys.sql_modules sm WITH (NOLOCK)
                    JOIN sys.objects o WITH (NOLOCK) ON sm.[object_id] = o.[object_id]
                    JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id] 
                    WHERE o.[type] = 'P '
                ) sm2
                WHERE sm2.tokens LIKE '%=%'
            ) sm3
            JOIN sys.parameters p WITH (NOLOCK) ON sm3.[object_id] = p.[object_id]
            OUTER APPLY (
                SELECT p2.name
                FROM sys.parameters p2 WITH (NOLOCK) 
                WHERE p2.is_output = 0
                    AND sm3.[object_id] = p2.[object_id] 
                    AND p.parameter_id + 1 = p2.parameter_id
            ) p2
            WHERE p.is_output = 0
        ) data
    ) data2
) data3

Could you describe in English, at a general level, what this is doing? — Joshua Frank, Jul 15 '14 at 16:18
@JoshuaFrank He is parsing the stored procedure text and trying to pick out the defaults. If i run this on my database, it returns a lot of incorrect information, so it's not quite ready for prime time. — Simon Hughes, Jan 28 '15 at 10:48
@SimonHughes: I figured as much, but it's hard to make sense of the specifics. — Joshua Frank, Jan 28 '15 at 18:09

Find which parameters of stored procedures are nullable (optional)

2 Answers2