Technical Article

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating