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