Search This Blog

Friday, June 11, 2010

Get Default Values of Parameters of Stored Procedures


If you are dealing and quering on the
schema of your database, you probably need this script;


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--
=============================================
-- Author:        Atif
-- Create date:24-May-2010
-- Description:    Get Default Value of a Parameter of SP or Function
--
=============================================


ALTER FUNCTION [dbo].[fnGetParameterDefaultValue]
(    
    @pSPName varchar(1000)='',
    @pParameterName varchar(100),
    @pDataType varchar(100),
    @pType bit=0 --0 for Stored Procedure and 1 for Function
)
RETURNS varchar(1000)
AS
BEGIN    
    Declare @pOutPut varchar(1000)
    Declare @vStartPosition int


    Declare @vStartPosition2 int    
    Declare @vStartPosition3 int
    Declare @vStartPosition4 int
    Declare @vStartPosition5 int
    Declare @vSPText varchar(max)
    Declare @vSPText2 varchar(max)
-- Get the text form syscomments (first 4000 characters if length is > 4000)



    if @pType = 0
    begin
        SELECT @vSPtext = (SELECT text FROM syscomments
         WHERE id = object_id(@pSPName) and colid=1 and number = 1)


        Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE PROCEDURE',@vSPtext),4000)
    end
    else
        SELECT @vSPtext = (SELECT text FROM syscomments
         WHERE id = object_id(@pSPName) and colid=1 and number = 0)


      Set @vSPtext = SubString(@vSPtext,CharIndex('CREATE FUNCTION',@vSPtext),4000)


    if IsNull(@vSPtext,'') = ''
    begin
        -- Exit if SP Name Not found in syscomments....
        Select @pOutPut = ''
        RETURN @pOutPut
    end
    Set @pOutPut = ''
    While 1=1
    Begin
        -- Get the position of the parameter definition.
        Select @vStartPosition = PatIndex('%' + @pParameterName + '%',@vSPText)
        -- Check if parameter exists
        if @vStartPosition > 0
        begin
            -- Get the Definition String
            select @vSPText=RIGHT(@vSPText, DataLength(@vSPText)-(@vStartPosition -1))

            -- Get the string breaker
            if (CharIndex(',',@vSPText) > 0) or (CharIndex('-',@vSPText) > 0)
                or (CharIndex(Char(10),@vSPText) > 0) or (CharIndex('AS',@vSPText)
      > 0)
            begin                
                Set @vStartPosition = CharIndex(',',@vSPText,Len(@pParameterName))-1
                Set @vStartPosition2 = CharIndex('-',@vSPText,Len(@pParameterName))-1
                Set @vStartPosition3 = CharIndex(Char(10),@vSPText,Len(@pParameterName))-1
                Set @vStartPosition4 = CharIndex('AS',@vSPText,Len(@pParameterName))-1
                Set @vStartPosition5 = CharIndex('OUT',@vSPText,Len(@pParameterName)) -1


                if @vStartPosition <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)


                    Set @vStartPosition = 10000000

                if @vStartPosition2 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)


                    Set @vStartPosition2 = 10000000


                if @vStartPosition3 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)


                    Set @vStartPosition3 = 10000000
                if @vStartPosition4 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)


                    Set @vStartPosition4 = 10000000





                if @vStartPosition5 <= (Len(@pParameterName) + Len(@pDataType) + case when CharIndex('AS',@vSPText) between CharIndex(@pParameterName,@vSPText) And CharIndex(@pDataType,@vSPText) then 2 else 0 end)


                    Set @vStartPosition5 = 10000000


                Select Top 1 @vStartPosition = [value]
                from dbo.fnSplit(Cast(@vStartPosition as varchar(10)) + ',' + Cast(@vStartPosition2 as varchar(10))
                                + ',' + Cast(@vStartPosition3 as varchar(10))
                                + ',' + Cast(@vStartPosition4 as varchar(10))
                                + ',' + Cast(@vStartPosition5 as varchar(10)) ,',')    
                order by Cast([value] as int)
            end
            else
            begin
                -- SP text must atleast have AS to break the parameter definition string
                Set @vStartPosition = CharIndex('AS',@vSPText) - 1
            end             

            -- Get the specific Definition String
            Set @vSPText2 = Left(@vSPText,@vStartPosition)

            -- Check if you got the right one by data type...            
            if CharIndex(@pDataType,@vSPText2) > 0
            begin
                --Select 'IN'
                --Select @text2
                if CharIndex('=',@vSPText2) > 0
                begin
                    -- check the default value
                    Select @pOutPut = Right(@vSPText2,DataLength(@vSPText2) - CharIndex('=',@vSPText2))
                    -- We have default value assigned here

                if Right(@pOutPut,1) = ','
                        Set @pOutPut = Left(@pOutPut,DataLength(@pOutPut)-1)
                end
                else
                begin
                    --Set @pOutPut = 'No Default Value Defined...'
                    -- We DO NOT have default value assigned here
                    Set @pOutPut = ''
                end


                --No need to work further with this parameter
                BREAK
            end
            else
            begin   
                --Set @vSPText = SubString(@vSPText,@vStartPosition + Len(@vSPText2),4000)
                -- Cut the SP text short and loop again
                Set @vSPText = SubString(@vSPText,@vStartPosition,4000)
            end
            -- This should never be the case. Just a check....
            if Datalength(@vSPText) < Datalength(@pParameterName)
                Break
        end
        else
        begin
            --Set @pOutPut = 'Parameter Not Found...'
            -- Wrong parameter search...
            Set @pOutPut = ''
            Break
        end
    End


    Select @pOutPut = rtrim(ltrim(@pOutPut))
    RETURN @pOutPut
END








To use this function, here is the proper way;



Select a.[name],b.[name] ,dbo.fnGetParameterDefaultValue('[dbo].[YourSPName]',a.[name],b.[name],0) as defaultVal
from sys.parameters a
inner join sys.types b on b.system_type_id = a.system_type_id
where Object_id = object_id('[dbo].[YourSPName]')




No comments:

Post a Comment