SQL Server list all parameters including default_value

Hi Pawan thanks for your response.
I am writing a query retrieve list of parameters including 'default value' and if it's optional.

Can you please suggest how I can best use the above procedure with the query like below

select * from 
(select 
r.name as [Report Name],
--o.name as [Procedure Name],
--o.object_id as [Procedure ID],
p.name as [Paremeter Name],
t.name as [Data Type],
ep.name as [Property Name],
ep.value as [Property Value]
from sys.objects o 
inner join sys.parameters p on o.object_id = p.object_id
inner join sys.extended_properties ep on ep.major_id = o.object_id and ep.minor_id = p.parameter_id
inner join sys.types t on t.system_type_id = p.system_type_id
inner join t_RPT_Report r on o.name = r.StoredProcedure
where o.type = 'P') as P
Pivot
(
	MAX([Property Value]) for [Property Name] in ([Name], [Description], [Is Optional?])
) as pvt1

Select allOpen in new window

Is the only way to this is by converting the above procedure to a function?

Are you able to provide a procedure or function that would confirm if the parameter is optional or not?

Do you want it to be converted it to be a function?

I have converted it like below but I looking for the one to check if the parameter is optional
Just battling with try to use this function. For some reason I keep getting error that the database object doesn't exist. However, it's there..:/

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter FUNCTION GetParameterDefaultValue
(
	@Procname varchar(50),
	@ProcParamName varchar(50)
)
RETURNS varchar(100)
AS
BEGIN
	Declare @DefaultValue varchar(100)
	declare @sqlstr nvarchar(4000),
	@obj_id int,
	@version int,
	@text varchar(8000),
	@startPos int,
	@endPos int,
	@ParmDefinition NVARCHAR(500)
	select @procName = rtrim(ltrim(@procname))
	set @startPos= charindex(';',@Procname)

	if @startPos<>0
	begin
	set @version = substring(@procname,@startPos +1,1)
	set @procname = left(@procname,len(@procname)-2)
	end
	else
	set @version = 1

	SET @sqlstr =N'SELECT @text_OUT = (SELECT text FROM syscomments
	WHERE id = object_id(@p_name) and colid=1 and number = @vers)'
	SET @ParmDefinition = N'@p_name varchar(50),
	@ParamName varchar (50),
	@vers int,
	@text_OUT varchar(4000) OUTPUT'

	EXEC sp_executesql
	@SQLStr,
	@ParmDefinition,
	@p_name = @procname,
	@ParamName = @ProcParamName,
	@vers = @version,
	@text_OUT =@text OUTPUT

	--select @TEXT
	select @startPos = PATINDEX( '%' + @ProcParamName +'%',@text)
	if @startPos<>0
	begin
	select @text = RIGHT ( @text, len(@text)-(@startPos +1))
	select @endPos= CHARINDEX(char(10),@text) -- find the end of a line
	select @text = LEFT(@text,@endPos-1)
	-- check if there is a default assigned and 
	-- parse the value to theoutput
	select @startPos= PATINDEX('%=%',@text)
	if @startPos <>0
	begin
	select @DefaultValue = 
		 ltrim(rtrim(right(@text,len(@text)-(@startPos))))
	select @endPos= CHARINDEX('--',@DefaultValue)
	if @endPos <> 0
	select @DefaultValue = rtrim(left(@DefaultValue,@endPos-1))

	select @endPos= CHARINDEX(',',@DefaultValue)
	if @endPos <> 0
	select @DefaultValue = rtrim(left(@DefaultValue,@endPos-1))
	end
	ELSE
	select @DefaultValue = 'NO DEFAULT SPECIFIED'
	end
	else
	SET @DefaultValue = 'INVALID PARAM NAME'
	
	-- Return the result of the function
	RETURN @DefaultValue
	
END
GO

Select allOpen in new window

Do like below

Alter FUNCTION GetParameterDefaultValue
(
      @Procname varchar(50) NULL ,
      @ProcParamName varchar(50) NULL
)

and check for NULL

I have created this as scalar function
but for some reason I can't run it
the object exist in the database
and when I do

select * from GetParameterDefaultValue('p_report, '@DTD')

It gives me error 'Invalid object name'..
Very strange

Running it like below,

select dbo.GetParameterDefaultValue('p_WRAP_GetWrapperOutputMTDYTD', '@DTD') as tes

I get following error,
Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures can be executed from within a function.

Any ideas?

I think the reason I am getting this error message is this sp EXEC call within the function,

EXEC sp_executesql
      @SQLStr,
      @ParmDefinition,
      @p_name = @procname,
      @ParamName = @ProcParamName,
      @vers = @version,
      @text_OUT =@text OUTPUT

any suggestions please..

thanks Pawan

Welcome !!