Stored Procedure Default Parameter Identification

Question

I have a proc that is servicing a web service call. This is a SQL 2012 environment. The proc takes a stored procedure name and a arbitrary set of procedure parameters (separated with a string separator). Inside the proc I build the statement such as follows: -

    INSERT INTO @Params
    SELECT p.parameter_id, p.name AS ParameterName, t.name AS ParameterType, p.max_length AS ParameterLength, ''
    FROM sys.parameters AS p
    JOIN sys.types AS t ON t.user_type_id = p.user_type_id
    WHERE object_id = OBJECT_ID(RTRIM(LTRIM(@ProcName)))

    UPDATE P
    SET pValue = s.data 
    FROM dbo.split(@ParamList,'ç') as S
    INNER JOIN @Params as P ON P.id = S.ID 


    SELECT @ParamSplit = ISNULL(Stuff((SELECT ',' + 
        pName + '=' +
        CASE 
            WHEN pValue = 'ÆNULL' THEN 'NULL' 
            WHEN pType = 'varchar' THEN char(39) + replace(pValue,char(39),char(39) + char(39)) + char(39) 
            WHEN pType = 'date' THEN char(39) + replace(pValue,char(39),char(39) + char(39)) + char(39) 
            WHEN pType = 'datetime' or pType='TIME' THEN char(39) + replace(pValue,char(39),char(39) + char(39)) + char(39)
            else pValue
            END
    FROM @Params
    FOR XML PATH ('')),1,1,''),'')

This works fine. I appreciate that not all data types are being checked, but for my purposes (this is a closed system) it covers all of the required bases. But I have a problem as the calling service has to pass ALL parameters, even params that may be defaulted within the proc.

There is a field in the sys.parameters table which is

has_default_value

and another field

default_value

, but checking the Microsoft documentation they say: -

Default values are recorded in the sys.parameters.default column only for CLR procedures. That column will be NULL for Transact-SQL procedure parameters.

What I would like to do is identify if the proc I am calling has any default params, if they do (and the calling service has not passed them) I can use the defaults, otherwise these can be replaced by whatever the calling service has passed.

So... Question - does anyone know how to identify if the proc has any default parameters assigned?

The answers to [this question](http://stackoverflow.com/q/5873731/1048425) contain a way of parsing the procedure definition to retrieve the default values. I am not sure that there is a cleaner way of doing it. — GarethD, May 05 '16 at 11:53
You could: 1) Create DDL trigger that will intercept `CREATE/ALTER procedure` 2) Parse definition to retrieve default values 3) Put in extended properties 4) In your code list all parameters from `sys.parameters` and fetch default values from extended events. Something like **[demo](http://rextester.com/GGXWTY28224)**. It is completely possible to achieve it in automated manner. Parsing could be even simpler if you have some sort of documentation in `JSON/XML format` as comment. — Lukasz Szozda, May 05 '16 at 12:08
This is an existing, production DB so not really practical open the hood up. I can query what is there - can't really modify any procs. I think parsing at runtime would be inefficient (these procs are called a lot, potential many times a second). I thought what I could is assume that if the web service has not passed ALL the parameters, then any missed must by the defaut - so the proc can be called without these params. I think that will work... — Matt Luckham, May 05 '16 at 12:19

score 0 · Answer 1 · answered May 05 '16 at 13:16

You can try this but may need to check format of your stored procedure defintions, you may be able to call this from your application and cache on the client side to prevent performance hits:

Declare @ProcedureName VARCHAR(50) = 'MySP'
DECLARE @text VARCHAR(MAX)
DECLARE @startPos int = 0
DECLARE @endPos int = 0

SELECT @text=text FROM syscomments
WHERE id = object_id(@ProcedureName) and colid=1 AND text LIKE '%(%'

IF @text IS NULL 
BEGIN
    -- No Parameters in stored procedure
    PRINT 'No Parameters'
END
ELSE
BEGIN

    -- Find Start of parameter declaration
    SELECT @startPos = PATINDEX( '%(%' ,@text)
    -- Find end of parameter declaration
    SELECT @endPos = PATINDEX( '%AS' + CHAR(13) + '%' ,@text)

    -- Set Text to our parameters string
    SELECT @text = SUBSTRING(@text, @startPos + 1, @endpos - @startpos -4)

    -- Split parameters and insert into table 
    -- Alternatively use other string splitting techniques such as numbers table
    DECLARE @t1 TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@text,',','</t><t>') + '</t>'
    INSERT INTO @t1(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)


    SELECT  SUBSTRING(val, PATINDEX('%@%', val), PATINDEX('% %', val) - PATINDEX('%@%', val)) as Parm, 
            SUBSTRING(val, PATINDEX('%=%',val) + 1, DATALENGTH(val) - PATINDEX('%=%',val)) As DefaultValue
    FROM @t1
    WHERE val like '%=%' -- Only get parameters with default value

END

Stored Procedure Default Parameter Identification

1 Answers1