Retrieve default value for parameter in procedure
This procedure will return DEFAULT value for the parameter in the stored procedure.
Usage:
Use pubs
go
declare @Value varchar(30)
exec _GetParamDefault 'random_password','@password_type',@value OUTPUT
SELECT @VALUE
Also accepts different versions, by default, if not specified, first version info retrieved.
exec _GetParamDefault 'random_password;2','@password_type',@value
if exists (select name from sysobjects
where name = '_GetParamDefault' and type = 'P')
drop procedure _GetParamDefault
GO
create proc _GetParamDefault
@Procname varchar(50),
@ProcParamName varchar(50),
@DefaultValue varchar(100) OUTPUT
as
/*
This procedure will return DEFAULT value for the parameter in the stored procedure.
Usage:
Use pubs
go
declare @Value varchar(30)
exec _GetParamDefault 'random_password','@password_type',@value OUTPUT
SELECT @VALUE
*****************************************************
Created by Eva Zadoyen
05/10/2002
*/
set nocount on
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 the output
select @startPos= PATINDEX('%=%',@text)
if @startPos <>0
begin
select @DefaultValue = ltrim(rtrim(right(@text,len(@text)-(@startPos+1))))
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'
set nocount off
return