Click here to Skip to main content
15,562,414 members
Articles / Database Development / SQL Server
Posted 29 Aug 2006


54 bookmarked

Calling Stored procedures in ADO.NET

Rate me:
Please Sign up or sign in to vote.
2.56/5 (21 votes)
29 Aug 20062 min read
An introduction to executing SQL Server stored procedures and how to retrieve the return parameters


Stored Procedures are a set of sql commands which are compiled and are stored inside the database. Every time you execute a sql command, the command is parsed, optimization is done and then the command is executed. Parsing and optimization the command each time you run the query is very expensive. To solve this we have a set of commands collectively called as stored procedure, which are already parsed and optimized and are executed when ever we call them. This article describes about how to call the stored procedures through and how to handle the output parameters of the called stored procedures.

Initially create a object of SqlConnection class which is available in System.Data.SqlClient namespace. You has to provide the connection string as a parameter which includes the Data Source name, the database name and the authentication credentials. Open the connection using the Open() method.

            SqlConnection con = new SqlConnection("Data Source=  ; initial                catalog= Northwind ; User Id=  ; Password=  '");


Create the following stored procedure on the Region table in the Northwind database which accepts two parameters and does not have any output parameters.

            CREATE PROCEDURE RegionUpdate (@RegionID INTEGER,
            @RegionDescription NCHAR(50)) AS
            SET NOCOUNT OFF
            UPDATE Region
            SET RegionDescription = @RegionDescription

Create a SqlCommand object with the parameters as the name of the stored procedure that is to be executed and the connection object con to which the command is to be sent for execution.

            SqlCommand command = new SqlCommand("RegionUpdate",con);

Change the command objects CommandType property to stored procedure.        

 	    command.CommandType = CommandType.StoredProcedure;

Add the parameters to the command object using the Parameters collection and the SqlParameter class.

            command.Parameters.Add(new SqlParameter("@RegionID",SqlDbType.Int,0,"RegionID"));

            command.Parameters.Add(new SqlParameter("@RegionDescription",SqlDbType.NChar,50,"RegionDescription"));

Specify the values of the parameters using the Value property of the parameters



Excecute the stored procedure using the ExecuteNonQuery method which returns the number of rows effected by the stored procedure.

             int i=command.ExecuteNonQuery();

Now let us see how to execute stored procedures which has output parameters and how to access the results using the output parameters.

Create the following stored procedure which has one output parameter.

            ALTER PROCEDURE RegionFind(@RegionDescription NCHAR(50) OUTPUT,
            @RegionID INTEGER )AS

            SELECT @RegionDescription =RegionDescription from Region where <A href="mailto:RegionID=@RegionID">RegionID=@RegionID</A>

The above stored procedure accepts regionID as input parameter and finds the RegionDescription for the RegionID input and results it as the output parameter.

            SqlCommand command1 = new SqlCommand("RegionFind",con);
            command1.CommandType = CommandType.StoredProcedure;

Add the paremeters to the command1

            command1.Parameters.Add(new SqlParameter     ("@RegionDescription",SqlDbType.NChar ,50,ParameterDirection.Output,false,0,50,"RegionDescription",DataRowVersion.Default,null));
            command1.Parameters.Add(new SqlParameter("@RegionID" ,
            0 ,
            "RegionID" ));

Observe that the parameter RegionDescription is added with the ParameterDirection as Ouput.

specify the value for the input parameter RegionID.

            command1.Parameters["@RegionID"].Value = 4;

Assign the UpdatedRowSource property of the SqlCommand object to UpdateRowSource.OutputParameters to indicate that data will be returned from this stored procedure via output parameters.

            command1.UpdatedRowSource = UpdateRowSource.OutputParameters;

Call the stored procedure and access the RegionDescription for the RegionID 4 using the value property of the parameter.

           string newRegionDescription =(string) command1.Parameters["@RegionDescription"].Value;

Close the sql connection.


In the same way you can call the stored procedure that returns a set of rows by defining the parameters as appropriate and executing the command using ExecuteReader() that is used to traverse the records returned by the command.



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

Written By
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionReg:stored procedure Pin
Member 103179565-Oct-13 20:17
Member 103179565-Oct-13 20:17 
AnswerRe: Reg:stored procedure Pin
balmerhevi13-Oct-14 20:48
balmerhevi13-Oct-14 20:48 
QuestionStored procedure input and output parameters Pin
Member 91711286-Jan-13 19:22
Member 91711286-Jan-13 19:22 
QuestionThanks Pin
Vo Thanh Bac24-Jun-12 19:45
Vo Thanh Bac24-Jun-12 19:45 
GeneralMy vote of 5 Pin
joaoedusantosquinzao12-Jan-11 1:53
joaoedusantosquinzao12-Jan-11 1:53 
GeneralGood reminder for how to use stored procedures in ADO.NET Pin
Member 408375317-Mar-10 2:02
Member 408375317-Mar-10 2:02 
GeneralUmmm... no Pin
PIEBALDconsult23-Nov-09 6:19
professionalPIEBALDconsult23-Nov-09 6:19 
JokePoor Pin
Coder_200714-Mar-07 21:05
Coder_200714-Mar-07 21:05 
GeneralPoor Formatting Pin
ByteGhost30-Aug-06 2:58
ByteGhost30-Aug-06 2:58 
QuestionAnd ??? Pin
fwsouthern29-Aug-06 22:14
fwsouthern29-Aug-06 22:14 

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.