Stored procedures are nothing but a set of sql statements which are precompiled in the database. It has many advantages over executing sql queries directly from the application. As few are here:

• Security:
• Actual table names are not revealed via application scripts.
• Database user created for the application dont need to have any execution permission apart from executing these stored procedures, these two features provide extreme security to databases. Must be followed in projects where very sensible data are handled in database.
• Speed and Efficiency:
• Procedures are precompiled in the database server, so you can save the compile time needed to compile the new query into the database from the application.
• For bigger queries, you don't need to form them from the application, again and again. Instead, Just pass the variables.
• For queries to be executed in loops, use cursors within stored procedures, that will help reducing execution time (request time and response time).

A stored procedure can have:

• Any number of input parameters.
• Any number of output parameters.
• Return value to calling function (other than output params).
• Fetch recordsets as we do with "select * from", we can collect it in a dataset.

A sample of stored procedures is here:

	 CREATE PROC dbo.testProcedure
	 {
	 	@inputVar1 int,
		@outputVar1 varchar(30) OUTPUT
	 )
	 AS
	 	 DECLARE @tempVar int
		 SELECT @tempVar = count(*) from tbl_accounts where userid=@inputVar1
	 	 select @outputVar1 = userName from tbl_user where id=inputVar1
		 INSERT INTO tbl_log (userid, logindate) values (@inputVar1, getdate())
		 return @tempVar
	 GO
	 

The above example has a input var, a output var and a return value. It also inserts a value into tbl_Log table. To learn about cursors in procedures click here.

Likewise, if need to call this sample from C#(ADO.NET), click here.