Click here to Skip to main content
13,596,321 members
Click here to Skip to main content
Add your own
alternative version


35 bookmarked
Posted 3 Jan 2006

Using C# to Enumerate Through Stored Procedures in MS SQL Server 2000

, 3 Jan 2006
Rate this:
Please Sign up or sign in to vote.
This guide will show you how to enumerate through the stored procedures in MSSQL 2000, as well as retrieve parameter information for a stored procedure.


This article will demonstrate how to enumerate through a stored procedure's parameters using C#, ASP.NET, and MS SQL Server 2000.


After looking around 'net for some time trying to locate a simple way to enumerate through the parameters of a stored procedure, I decided to post the solution I have here. Why is it useful? Well, if you want to create a solution that can dynamically pass parameters to a stored procedure, it's quite useful. I used the solution to create a reporting application that will return data to a user (through a web application) by executing stored procedures on a SQL Server. But it was important that I allowed the user to provide parameters, such as report start date, or report end date, and I needed to be sure I could validate that the data from the input was the right type for the stored procedure's parameter. I'll show you how to do that in this article.

Using the Code

This code can be used in any C# application, web or otherwise, but the example will demonstrate usage in a web application. If you download the sample project, you will of course need VS.NET 2003, an instance of MS SQL Server 2000, and IIS running. You'll want to change the connection string in the Web.config to point to your local instance of MS SQL Server.

List the Stored Procedures

Since MS SQL Server doesn't provide an extended stored procedure for enumerating through stored procedures, we just do it directly using a SELECT command:

select name from dbo.sysobjects where type ='P' order by name asc

Our C#/ASP.NET function:

//Enumerate and load all stored procedures from the database 
private void loadStoredProcs()
    //Clear out the dropdownlist    
    SqlConnection cn = new SqlConnection(
    //We'll use a SQL command here. 
    //We use an adapter below. 
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "select name from sysobjects" + 
                      " where type='P' order by name asc";
    cmd.CommandType = CommandType.Text;
    cmd.Connection = cn;
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
     catch (Exception exc)
         //Send the exception to our exception label 
         this.lblException.Text = exc.ToString();

List the Parameters for a Stored Procedure

Now that we can list all of the stored procedures, we'll use the following SQL to get a table listing the important columns for the parameters of a selected procedure. We can use the ID from sysobjects and the rest of the data from syscolumns to obtain all of the parameter and type information for each parameter of our selected stored procedure:

select ,, as [type], t.length
from  syscolumns s
inner join systypes t
on s.xtype = t.xtype
where id = (select id from sysobjects where name =

Our C#/ASP.NET function:

 //Get all parameters for a specified stored procedure 
 private void bindParameters( string strName)
    SqlConnection cn = new SqlConnection(
    //Use a string builder to hold our SQL command 
    StringBuilder sb = new StringBuilder();
    sb.Append("select,, as [type], t.length ");
    sb.Append("from syscolumns s ");
    sb.Append("inner join systypes t ");
    sb.Append("on s.xtype = t.xtype ");
    sb.Append("where id = (select id from" + 
              " sysobjects where name='" + strName + "')");
    //Use a SqlDataAdapter to fill a datatable, 
    //using the above command 
    SqlDataAdapter adapter = new SqlDataAdapter(sb.ToString(), cn);
    DataTable dt = new DataTable();
        //Bind the resulting table to the grid 
     catch (Exception exc)
         //Send the exception to our exception label 
         this.lblException.Text = exc.ToString();
        //Clean up the connection

Points of Interest

There has been a lot of concern over the past few years about SQL injection attacks. As a web programmer, you leave yourself wide open to this when you utilize raw SQL and query strings. If you decide to use the above SQL or code, I'd recommend compiling the SQL into parameterized stored procedures, and executing them that way. I left them as raw SQL here for the purpose of illustration.


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 Author

Product Manager iMobile3
United States United States
I have worked professionally in IT since 2004, and as a software architect since 2008, specializing in user interface design and experience, something I am still extremely passionate about. In 2013 I moved into management, and since then I've held positions as Director of Product Development, Director of Engineering, and Practice Director.

You may also be interested in...


Comments and Discussions

QuestionC# access to SQL Server Stored Procedure Pin
LeifDalkarChr12-Jan-10 4:03
memberLeifDalkarChr12-Jan-10 4:03 
GeneralThanks Pin
Ahmed R El Bohoty17-Nov-08 7:42
memberAhmed R El Bohoty17-Nov-08 7:42 
GeneralFix for NVARCHAR Pin
TallBear11-Dec-07 6:50
memberTallBear11-Dec-07 6:50 
GeneralLong Line Pin
Jay Giganti25-Jan-07 11:42
memberJay Giganti25-Jan-07 11:42 
GeneralRe: Long Line Pin
UsualDosage26-Jan-07 4:07
memberUsualDosage26-Jan-07 4:07 
GeneralRe: Long Line Pin
Jay Giganti26-Jan-07 5:28
memberJay Giganti26-Jan-07 5:28 
GeneralShould use standard SQL sp for this Pin
JanvdK10-Jan-06 8:33
memberJanvdK10-Jan-06 8:33 
GeneralRe: Should use standard SQL sp for this Pin
HyperX2-Feb-06 5:17
memberHyperX2-Feb-06 5:17 
GeneralRe: Should use standard SQL sp for this [modified] Pin
Leblanc Meneses19-Jul-06 22:03
memberLeblanc Meneses19-Jul-06 22:03 
NewsBad Idea Pin
Grimolfr4-Jan-06 3:49
memberGrimolfr4-Jan-06 3:49 
GeneralRe: Bad Idea Pin
UsualDosage4-Jan-06 4:47
memberUsualDosage4-Jan-06 4:47 
GeneralRe: Bad Idea Pin
Grimolfr4-Jan-06 8:10
memberGrimolfr4-Jan-06 8:10 
GeneralRe: Bad Idea Pin
UsualDosage4-Jan-06 8:33
memberUsualDosage4-Jan-06 8:33 
GeneralRe: Bad Idea Pin
yarex00723-Mar-06 6:48
memberyarex00723-Mar-06 6:48 
GeneralGood Article - Alternate Approach Pin
Michael McKechney3-Jan-06 16:42
memberMichael McKechney3-Jan-06 16:42 
Answerobject id Pin
Grimolfr4-Jan-06 3:51
memberGrimolfr4-Jan-06 3:51 
GeneralVery nice. Pin
Marc Leger3-Jan-06 13:51
memberMarc Leger3-Jan-06 13:51 
GeneralRe: Very nice. Pin
UsualDosage3-Jan-06 15:42
memberUsualDosage3-Jan-06 15:42 

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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.180621.3 | Last Updated 3 Jan 2006
Article Copyright 2006 by DreamInHex
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid