SQL Server Stored Procedure and How to Call it in C#

In the upcoming project it is likely i will be using a lot of stored procedure as the business process. Since i have never worked on real project using stored procedure but i did learn it before, i guess write a blog post about Sql Server stored procedure and how to call stored procedure in C# will be useful.

Sql Server

We can define stored procedure as a group of Sql statements that are group to perform specific task.

For this tutorial we need following table structure

Stored Procedure Structure

To create procedure we will follow below structure

In stored procedure parameter is optional. There are two types of parameter in stored procedure. One is input parameter and another one is output parameter.

Stored Procedure in action

Suppose we want to create stored procedure that return student name based on its id.

We can also assign return value to output parameter like below

As for now, the examples of stored procedure i provide above are all SELECT statement. Of course you can place INSERT, UPDATE and DELETE also, moreover you can write more than one statement on stored procedure.

Call Stored Procedure in Sql Server

To call stored procedure that has no output parameter below is the example

Whereas to call stored procedure that has output parameter we need to declare variable first.

Call Stored Procedure in C#

Will be useless if stored procedure can’t be invoked via programming language. In C# to call stored procedure we use ADO.NET.

In line 8 you can see that we have to specify the name of stored procedure we want to invoke and online 10 we have to set command type to StoredProcedure.

Facebook Comments

Agung Setiawan

Agung Setiawan adalah software engineer di BukaLapak.com, penulis sekaligus pecinta sastra, dan pembaca buku

Halo, perkenalkan saya Agung Setiawan.
Seorang Software Engineer di BukaLapak, penulis buku, blogger, gunpla builder, tech educator.
Simak pemikiran saya soal dunia Software Engineering via Twitter di @agungsetiawanmu dan facebook
Mau belajar Vim bareng saya?
Belajar ngoding dari nol menggunakan PHP

One thought on “SQL Server Stored Procedure and How to Call it in C#

Leave a Reply

Your email address will not be published. Required fields are marked *