How to create Stored Procedure in Visual Studio and C#

A stored procedure is code that is written in SQL and saved as part of a database. It is a

method stored in the database itself, and not in your program code; hence the term stored

procedure. table structure as:

Figure 1

To create a stored procedure, right-click the Stored Procedure folder for the database

in Server Explorer and select Add New Stored Procedure. You'll see an editor appear with

skeleton code for a stored procedure. Modify the code so that it retrieves all of the data

from the Customer table, as shown in Listing. After modifying the template code,

click Save and you'll see the stored procedure appear in the Stored Procedures folder of

the database in Server Explorer.

Listing  Stored procedure example

ALTER PROCEDURE dbo.GetCustomers

    /*

    (

    @parameter1 int = 5,

    @parameter2 datatype OUTPUT

    )

    */

AS

declare @cust_count int

select @cust_count=count(*) from customer

if @cust_count >0

begin                                               /* begin of if statement */

select [Name] from customer

end  /* End of If Statement */

    /* SET NOCOUNT ON */

    RETURN

Listing  declares a variable named @cust_count and runs a select statement to

assign the number of customers, count(*), to @cust_count. If @cust_count is larger than 0,

there are customers and the stored procedure queries for customer names.

To execute this stored procedure, right-click the stored procedure in the database in

Server Explorer and click Execute. You'll see output similar to the following if there are

records in the customer table:

Running [dbo].[GetCustomers].

Name                                              

--------------------------------------------------

Niranjan                                          

Hari Om                                           

Jai                                               

Rinku                                             

Harmeet                                           

Sunny                                             

Vivek                                             

Shyam                                             

No rows affected.

(8 row(s) returned)

@RETURN_VALUE = 0

Finished running [dbo].[GetCustomers].

In addition to execution, you can debug the stored procedure in VS. To debug, set a

breakpoint on any line in the stored procedure, right-click the stored procedure in Server

Explorer, and select Step Into Stored Procedure or click ALT-F5.