Click here to Skip to main content
12,290,514 members (64,495 online)
Click here to Skip to main content
Add your own
alternative version


24 bookmarked

Figure Out the Default Values of Stored Procedure Parameters

, , 2 Feb 2006
Rate this:
Please Sign up or sign in to vote.
When working with 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 the stored procedure parameters into the SqlCommand object, as the SQL JDBC doesn't allow you to 'ignore' parameters that have default values. In this article, you will see how you can derive all the 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 values.

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:

select, from sysobjects, syscolumns 
sysobjects.xtype='P' and = 

The C# code that will use this stored procedure to get all your stored procedures and evaluate them for their parameters is pretty straightforward. First, use two 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); 

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 stored procedure and ignore all of the system stored procedures that begin with the prefix 'dt_'. Trivia – 'dt' stands for 'DaVinci Tools' and the early code name for the technology used here to auto generate code when using the 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
This procedure will return DEFAULT value for 
the parameter in thestored procedure.
declare @Value varchar(30)
exec _GetParamDefault 'random_password','@password_type',
                                            @value OUTPUT

Author: Eva Zadoyen
Edited: Rafael Mizrahi

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
set @version = substring(@procname,@startPos +1,1)
set @procname = left(@procname,len(@procname)-2)
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
@p_name = @procname,
@ParamName = @ProcParamName,
@vers = @version,
@text_OUT =@text OUTPUT

--select @TEXT
select @startPos = PATINDEX( '%' + @ProcParamName +'%',@text)
if @startPos<>0
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 theoutput
select @startPos= PATINDEX('%=%',@text)
if @startPos <>0
select @DefaultValue = 
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))
select @DefaultValue = 'NO DEFAULT SPECIFIED'

set nocount off

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:


Finally, you specify the output type and execute the query:

SqlParameter param = cmdSPParamValue.Parameters.Add(
                    "@DefaultValue", SqlDbType.VarChar,100);
param.Direction = ParameterDirection.Output;
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 shown 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); 

// 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; 
             "@ProcParamName", myReader.GetString(1)); 

    SqlParameter param = cmdSPParamValue.Parameters.Add(
                         "@DefaultValue", SqlDbType.VarChar,100); 

    param.Direction = ParameterDirection.Output; 


    ParamDefaultValue = param.Value.ToString(); 

    if (ParamDefaultValue != "NO DEFAULT SPECIFIED") 
      Console.WriteLine("{0}, {1}, {2}", myReader.GetString(0),    
                         myReader.GetString(1), ParamDefaultValue); 

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.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Authors

Rafael Mizrahi
Chief Technology Officer Feng-GUI
United States United States
Rafael Mizrahi is a CTO at Feng-GUI

He is also a hacker contributor at the mono project, and a GarageGeeks member.

Rafael Mizrahi personal blog -

Laurence Moroney is the Director of Product Evangelism for Mainsoft. He joined Mainsoft from Reuters, where he was a Senior Architect in their CTO Office, specializing in Enterprise Architecture and Web Services Interoperability. Laurence has also worked extensively in the financial services and security fields. He is the author of several computer books, including ‘Expert Web Services Security in the .NET Platform’ and ‘ASP.NET 1.1 with VB.NET’ as well as several dozen technology articles.

You can find his blog at:

You may also be interested in...

Comments and Discussions

GeneralMore caveats Pin
timschultze2-Apr-07 2:34
membertimschultze2-Apr-07 2:34 
The stored procedure for parsing the parameters assumes (I think) a certain standard format of the stored procedure "declaration." Each parameter needs to be listed on a separate line, as is done for the procedure itself in the article.

I happen to think that this standardized approach to declaring a stored procedure is good and, in fact, is slowly being adopted at one of my clients. There are still those old style formats out there, though, and this trips up the parsing logic, I believe.

There are perhaps other issues associated with parsing. For example, if I had a two parameters, one named SubDirectory followed by one named Directory, I'm not so sure the second one would ever be parsed. Granted, it seems a little unlikely that you would use one parameter whose name is a substring of the other, but it can happen in situations where you least expect it.

Perhaps an improvement would be to move the parsing logic into C# where there seems to be a larger text processing toolkit available to you.
GeneralRe: More caveats - Params on Single Line work for me Pin
The Corner Analyst27-Jun-07 11:25
memberThe Corner Analyst27-Jun-07 11:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160518.1 | Last Updated 3 Feb 2006
Article Copyright 2006 by Rafael Mizrahi, Laurence Moroney
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid