KB: Figure Out the Default Value of Stored Procedure Parameters

Figure Out the Default Value of Stored Procedure Parameters.

When working with the Microsoft SQL JDBC driver, you cannot invoke a stored procedure without specifying all the parameters, including the optional parameters that have default values. Learn how to fix that problem here.

When you are migrating a .NET application that uses ADO.NET to a Java-based one that uses JDBC, you will generally have to add code to add all stored procedure parameters into the SqlCommand object, as SQL JDBC doesn't allow you to 'ignore' parameters that have default values. In this article you will see how you can derive all parameters from a Stored Procedure using some T-SQL and C# code so that you can call your stored procedures and automatically have the default parameters assigned with their default value.

As SQL Server does not store the default values of stored procedure parameters in its system tables, and instead evaluates the text of the procedure at runtime, you lose some flexibility when writing applications that use T-SQL stored procedures.

This problem can be worked around with a little bit of C# and some T-SQL. Get started using this T-SQL Stored Procedure called _GetAllProcedures :

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

The C# code that will use this stored proc to get all your stored procedures and evaluate them for their parameters is pretty straightforward:

First, use 2 connections to the database – I will call these the 'main' connection, and the 'parameter' connection:

    SqlConnection conSP = new SqlConnection(ConnectionString);
    SqlConnection conSPParamValue = new SqlConnection(ConnectionString);
    conSP.Open();
    conSPParamValue.Open();

Next, set up a SqlCommand on the main connection and initialize it with the stored procedure that you are interested in using.

    SqlCommand cmdSP = new SqlCommand("_GetAllProcedures", conSP);
    cmdSP.CommandType = CommandType.StoredProcedure;

Next the code will loop through the results of this SP and ignore all of the system stored procedures that begin with the prefix 'dt_'.

Trivia – 'dt' stands for 'DaVinci Tools' and early code name for the technology used here to auto generate code when using Enteprise Manager or design tools, so you could say that you've truly found the 'DaVinci Code'.

When the procedure isn't a 'dt_' one, you then create a new command on the 'params' connection to the '_GetParamDefault' stored procedure, which as its name suggests will get the default parameter for you.

    string ParamDefaultValue = "";
    SqlCommand cmdSPParamValue = new SqlCommand(
        "_GetParamDefault", conSPParamValue);
    cmdSPParamValue.CommandType = CommandType.StoredProcedure;

The code for this stored procedure is here:

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

You then add parameters to this command, which are derived from the 'main' connection. These are the name of the procedure that you are currently inspecting and its parameter.

    cmdSPParamValue.Parameters.Add("@Procname",myReader.GetString(0));
    cmdSPParamValue.Parameters.Add("@ProcParamName", myReader.GetString(1));

Finally you specify the output type and execute the query.

    SqlParameter param = cmdSPParamValue.Parameters.Add(
            "@DefaultValue", SqlDbType.VarChar,100);
    param.Direction = ParameterDirection.Output;
    cmdSPParamValue.ExecuteNonQuery();
    ParamDefaultValue = param.Value.ToString();

You now have the default value for the Parameter, and you can use this in future calls to the stored procedure quite happily!

The full source code for this routine, in C# is below.

    // 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();

In this KB you have gained a new tool for your development toolbox: an automatic way of iterating through all the stored procedures in your database and extracting a list of their parameters that are declared with default values. With this list, you can then go through your code and find out where the stored procedures are used, and make sure that the SqlCommand is filled with all the parameters, including the optional ones.