Home | Feedback | Login

Stored Procedure

Stored procedures are precompiles and thereforethey are better in performance compared to inline SQL statements. It is a better idea to use stored procedure instead of SQL statment in DAL data access layer.  

A simple Stored Procedure has the following:

//  begins with CREATE PROCEDURE and the procedure name
create procedure dbo.MyStoredProcedure 
// begins with open bracket for variables
(
	// each variable begins with @ sign and ends 
	// with datatype and size. 
	// Put a comma if you have more.
	@name varchar(20), 
	@age int,
	@status bit
// end with closing bracket 
)
// after AS the SQL statment begins
as
	Insert into tblStudents(name, age, status)
			 values(@name, @age, @status)
// ends with RETURN
return

Methods using Stored Procedure:

This article explains how to consume a stored procedure using DataReader, Execute Scalar and ExecuteNonQuery  methods. 

Hint:

Do not forget to use Commandtype as Stored Procedure if you call a stored procedure vs the inline SQL statment.  

e.g. testCMD.CommandType = CommandType.StoredProcedure;