Sunday, December 25, 2005

Get default value of stored procedure parameter

Get the default value of a stored procedure parameter.
Well, SQL Server, does not store the default values of a stored procedure parameters in its system tables, they are merely evaluated by the text of the procedure at runtime. Boo- Hoo-.

So, taking an SP (_GetParamDefault) created by Eva Zadoyen back in 2002, adding another one of my own and some C# code and "Walla".

Some small information on the process:
* _GetAllProcedures - get all parameters of all stored procedures.
The resultset which returns contains:SP Name, SP Column

* _GetParamDefault - get default values for a stored procedure parameter.
The SP gets:SP Name, SP Column and an OUTPUT parameter for the default value.

* Now, lets wrap it all with some C# glue:


// open two connections
string ConnectionString = "Data Source=localhost;" +
"Initial Catalog=Northwind;user id=sa";
SqlConnection conSP = new SqlConnection(ConnectionString);
SqlConnection conSPParamValue = new SqlConnection(ConnectionString);
conSP.Open();
conSPParamValue.Open();

// get all stored procedures and parameters
SqlCommand cmdSP = new SqlCommand("_GetAllProcedures", conSP);
cmdSP.CommandType = CommandType.StoredProcedure;

// loop on all stored procecdures and parameters
SqlDataReader myReader = cmdSP.ExecuteReader();
while (myReader.Read())
{
if (myReader.GetString(0).ToLower().StartsWith("dt_") != true)
{

string ParamDefaultValue = "";

SqlCommand cmdSPParamValue =
new SqlCommand("_GetParamDefault", conSPParamValue);
cmdSPParamValue.CommandType = CommandType.StoredProcedure;
cmdSPParamValue.Parameters.Add("@Procname",myReader.GetString(0));
cmdSPParamValue.Parameters.Add("@ProcParamName",myReader.GetString(1));
SqlParameter param = cmdSPParamValue.Parameters.Add
("@DefaultValue",SqlDbType.VarChar,100);
param.Direction = ParameterDirection.Output;

cmdSPParamValue.ExecuteNonQuery();

ParamDefaultValue = param.Value.ToString();
if (ParamDefaultValue != "NO DEFAULT SPECIFIED")
{
Console.WriteLine("{0}, {1}, {2}",
myReader.GetString(0), myReader.GetString(1), ParamDefaultValue);
}
}
}
myReader.Close();
conSP.Close();
conSPParamValue.Close();


Create the following 2 stored procedures:


CREATE PROCEDURE _GetAllProcedures
AS

SET NOCOUNT ON

select sysobjects.name,syscolumns.name from sysobjects, syscolumns
where
sysobjects.xtype='P' and
sysobjects.id = syscolumns.id

RETURN
GO


and...



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:
declare @Value varchar(30)
exec _GetParamDefault 'random_password','@password_type',@value OUTPUT

SELECT @VALUE

*****************************************************
Created by Eva Zadoyen
*/

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))))
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
GO