Tuesday, February 15, 2011

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.

No comments :

Post a Comment