USE Util
GO
DROP FUNCTION dbo.SPParamDefault
GO
CREATE FUNCTION dbo.SPParamDefault(@Procname SYSNAME, @ProcParamName SYSNAME, @is_output BIT, @ProcDefinition VARCHAR(8000))
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @DefaultValue VARCHAR(500), @startPos INT, @endPos INT, @ParmDefinition NVARCHAR(500)
SET @ProcDefinition = REPLACE(REPLACE(@ProcDefinition, ' ', ' '), ' ', ' ')
SELECT @procName = RTRIM(LTRIM(@procname))
SET @startPos = CHARINDEX(';',@Procname)
IF @startPos<>0
BEGIN
SET @procname = LEFT(@procname,LEN(@procname)-2)
END
SELECT @startPos = PATINDEX( '%' + @ProcParamName + ' %',@ProcDefinition)
IF @startPos<>0
BEGIN
SELECT @ProcDefinition = RIGHT( @ProcDefinition, LEN(@ProcDefinition)-(@startPos +1))
SELECT @endPos= CHARINDEX(CHAR(10),@ProcDefinition) -- find the end of a line
SELECT @ProcDefinition = LEFT(@ProcDefinition,@endPos-1)
-- check if there is a default assigned and
-- parse the value to theoutput
SELECT @startPos= PATINDEX('%=%',@ProcDefinition)
IF @startPos <>0
BEGIN
SELECT @DefaultValue = LTRIM(RTRIM(RIGHT(@ProcDefinition,LEN(@ProcDefinition)-(@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 = NULL
END
ELSE
BEGIN
SET @DefaultValue = 'INVALID PARAM NAME'
END
IF RIGHT(@DefaultValue, 1) = CHAR(13) SET @DefaultValue = RTRIM(SUBSTRING(@DefaultValue, 1, LEN(@DefaultValue) - 1))
IF @is_output = 1 AND RIGHT(@DefaultValue, 6) = 'OUTPUT' SET @DefaultValue = SUBSTRING(@DefaultValue, 1, LEN(@DefaultValue) - 6)
RETURN @DefaultValue
END
GO