asked on

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

Jim Horn

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

Alyanto

ASKER

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.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alyanto

ASKER

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

Alyanto

ASKER

I will look into "EXEC sp_refreshsqlmodule @spname" from this point on to see if I can coax the meta data in.