Finding the default value of a stored procedures arguments

I have come to a point in my script that I seem unable to overcome.  I want to know what the default value of a procs argument is.   sys.parameters seems not to reflect this, I can see in a scripted proc some of its arguments have default values and some do not but when I use the default_value = Null and Has_default_value = 0 for all arguments.  Can anyone point me in the direction of a solution for this?

SQL SERVER 2012 is being used.

SELECT	SCHEMA_NAME(SCHEMA_ID) AS [Schema],
		SO.name AS [ObjectName],
		SO.Type_Desc AS [ObjectType (UDF/SP)],
		P.parameter_id AS [ParameterID],
		P.name AS [ParameterName],
		TYPE_NAME(P.user_type_id) AS [ParameterDataType],
		P.max_length AS [ParameterMaxBytes],
		P.is_output AS [IsOutPutParameter]
FROM sys.objects AS SO
	INNER JOIN sys.parameters AS P
		ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID
						FROM sys.objects
						WHERE TYPE IN ('P','FN'))
						AND SO.name = 'ProcsNameHere'
ORDER BY [Schema], SO.name, P.parameter_id

Open in new window

LVL 1
AlyantoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Char / Varchar's are NULLs, numeric data types are zeros.  You should be able to google 'SQL Server' and any data type and the msdn page describing it will have the default.

If you need this nailed down then you can define a default value in your procs, see the '= value' in the below code.   Using this, if a NULL value is passed the SP will use the default value instead

CREATE PROC foo (
   number int = 0, 
   name varchar(100) = 'McPaddy O''Flavo', 
   active bit = True,
   clue varchar(10) = NULL) 
AS
...

Open in new window

AlyantoAuthor Commented:
The procs we already have do have default values against a large number of the arguments, and often the default is null but sometimes it is mandatory and no default is given.

what I want to achieve is identifying which arguments are mandatory by thier lack of a default.  I am looking for a system table to give either the information or clues to a logical argument to determine whether it is or it is not.
Jim HornMicrosoft SQL Server Data DudeCommented:
>which arguments are mandatory by thier lack of a default.
Not sure you can assigned mandatory = lack of a default in all cases.

According to msdn the sys.procedures SP has columns has_default_value bit and default_value sql_variant that are queryable, so you can add WHERE has_default_value = 0 in your query above.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlyantoAuthor Commented:
I think the issue is that has_default_value in sys.procedures is unclear in its origins.  Where as is_output corrolates clearly to its purpose a parameter with Param INT = NULL I would expect as having is_output = 0 but has_default_value = 1 is what I would also expect.  Perhaps the table needs some sort of refresh to bring it upto date.  

However what you have provided has given me a new avenue to explore and may thanks for the excellent support given
AlyantoAuthor Commented:
I will look into "EXEC sp_refreshsqlmodule @spname" from this point on to see if I can coax the meta data in.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.