Web Design that taps into the haromny and vision of your dreams.

Calling SQL Server Stored Procedures from C#

By on in Coding

340 words, estimated reading time 2 minutes.

SQL Server Stored Procedures are sub routines which are exposed to applications accessing relational databases. The subroutine is stored within the database data dictionary and is often used to encapsulate data by providing validation or returning results of a query.

In this short example we will look at a procedure stored in a database of authors, can call that stored procedure from C# passing in parameters and returning a value.

This tutorial is based on an example from the Microsoft Help and Support.

The Authors database has a stored procedure called GetAuthorsByLastName which accepts one parameter of the author's last name. The procedure returns the number of records found.

In C# (ASP.Net, WinForms or any other project type) we need to establish a connection to the database in the usual way.

string authorName = "Bob";
  SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;UID=myUser;PWD=myPassword;");
  SqlDataAdapter MyDataAdapter = new SqlDataAdapter("GetAuthorsByLastName", MyConnection);
  MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;  
  MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@au_lname", SqlDbType.VarChar, 40));
  MyDataAdapter.SelectCommand.Parameters["@au_lname"].Value = authorName.Trim();
  MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));
  MyDataAdapter.SelectCommand.Parameters["@RowCount"].Direction = ParameterDirection.Output;
  DataSet DS = new DataSet();
  MyDataAdapter.Fill(DS, "AuthorsByLastName");
  Console.WriteLine(MyDataAdapter.SelectCommand.Parameters[1].Value + " Rows Found.");
catch (Exception ex)

In this code example, we create a connection to the pub's sample database, create a SqlDataAdaptor specifying the name of the stored procedure and the connection object. We set up the select command object to be a stored procedure, configure some parameters and add them to the command object. This example has one input parameter (default) and one output parameter using the ParameterDirection.Output.

You will be able to access the data of the rows returned using DS.Tables["AuthorsByLastName"].DefaultView as the data source for DataGridView or list views.

For reference, here is the SQL Server Stored Procedure:

Use Pubs
Create Procedure GetAuthorsByLastName (@au_lname varchar(40), @RowCount int output)  
select * from authors where au_lname like @au_lname; 
/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT

Last updated on: Thursday 22nd June 2017



Have a question or suggestion? Please leave a comment to start the discussion.



Nice. Would like to see an example where the stored procedure returns a cursor.

Reply to Dave


Thanks a lot.... this helped me ! to achieve what I wanted

Reply to Mahantesh
Dominic Palacio

Dominic Palacio

Hey Its Great!!! good job

Reply to Dominic Palacio


Leave a Reply

Please keep in mind that all comments are moderated according to our privacy policy, and all links are nofollow. Do NOT use keywords in the name field. Let's have a personal and meaningful conversation.

Your email address will not be published.