Is it a good practice of creating and using stored procedures for a database?

[Sentential]

Hi,

Is it a good practice of creating and using stored procedures for a database?
Can you share you views on this topic?
Do you find it better to use stored procedures?

Thanks,

[The_D]

Creating and using stored procedures

The main reasons for using stored procedures to execute queries on a database, is the improved performance.

CREATE PROCEDURE [dbo].[InsertLink]

(

@Title char(64),

@URL char(128)

)

AS

Insert MyLinks(Title, URL)

Values (@Title, @URL)

@Title and @URL are variables of type char with the length of 64 characters and 128 characters, respectively.

Executing stored procedures

// Create new SQL command named sqlInsert

System.Data.SqlClient.SqlCommand sqlInsert = new System.Data.SqlClient.SqlCommand("InsertLink", sqlConnection1);

// Set the command type property to StoredProcedure

sqlInsert.CommandType = CommandType.StoredProcedure;

// @Title should have the value inside txtTitle

sqlInsert.Parameters.Add("@Title", txtTitle.Text);

// @URL should have the value inside txtURL

sqlInsert.Parameters.Add("@URL", txtURL.Text);

// Open the connection

sqlConnection1.Open();

// Execute the query

sqlInsert.ExecuteNonQuery();

// Close the connection

sqlConnection1.Close();

Hope this helps.

[sligh]

Creating and using stored procedures

The syntax for creating a simple stored procedure, without special features such as parameters, is:

create procedure procedure_name 
	as SQL_statements

Stored procedures are database objects, and their names must follow the rules for identifiers.
Any number and kind of SQL statements can be included except for create statements.
A procedure can be as simple as a single statement that lists the names of all the users in a database:

create procedure namelist 
as select name from sysusers

To execute a stored procedure, use the keyword execute and the name of the stored procedure, or just use the procedure’s name, as long as it is submitted to Adaptive Server by itself or is the first statement in a batch. You can execute namelist in any of these ways:

namelist
execute namelist
exec namelist

To execute a stored procedure on a remote Adaptive Server, you must give the server name. The syntax for a remote procedure call is:

execute server_name.[database_name].[owner].procedure_name

A procedure can include more than one statement.

create procedure showall as 
select count(*) from sysusers 
select count(*) from sysobjects 
select count(*) from syscolumns

[Lineman]

Stored Procedures

Advantages

1. Performance - All the SQL statements, that you send to your database server passes through a series of actions, called execution. These are the steps that your SQL statement passes through before the data is returned to the client.
2. Security - By using a stored procedure, you can grant permissions to certain users to access data, reducing the immense coding that you need to do in your client applications.
3. Modifications/Maintenance - If you use stored procedures for database access, any change in the database can be reflected on to the client application without much effort. This is because you know exactly where the data is accessed from, and you also know exactly where you need to alter.
4. Minimal processing at the client - Stored procedures help you write batch of SQL statements, which helps you manage the transactions, constraints etc. A little data aware code has to be written in to the client application, making it a thin-client application. These applications will be concerned more about displaying data in the way the user needs them and they know little about the database.
5. Network traffic - Client applications always have to request/send data from the database server. These data are sent as packets, and travel through the network to the server.