STORED PROCEDURES IN MS SQL SERVER AND EXECUTING IN ASP.NET

A stored procedure is a routine or a group of SQL statements which can be created and stored in a database. Directly accessing and manipulating data in a database from a Web Form may create security risks and also affects efficiency. One way of improving the efficiency and security of database access is to create stored procedures on the database server, and then call these stored procedures from our Web Form. Stored procedures allow variables, parameters and looping constructs.

APPLICATIONS

There are three types of stored procedures, 1) Return records stored procedure Return records stored procedures are used to find specific records, sort and filter those records, and then return the result to a DataSet object. These stored procedures are based on SQL Select statements. 2) Return value stored procedures, also known as scalar stored procedures Stored procedures which are used to execute a database command that returns a single value. 3) Action stored procedures Action stored procedures are used to perform some function in the database, but not return any value or record. These database functions may include updating, editing, or modifying the data.

ADVANTAGES OF PROCEDURES

The stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure. Stored Procedures are used to organise the SQL logic apart from the application logic, and also act as a security measure ie, protection against SQL Injection attacks. 1) Modular programming Stored procedures are classic examples of modular programming. Once the stored procedure is created and tested, store it on the database server, and then call it any number of times from multiple applications. They are easier to maintain and troubleshoot. 2) Distribution of work Allows each developer to focus on their own speciality, and meet their own deadlines. 3) Increase database security Provides increased security for a database by limiting direct access. The risk of accidental damage to the structure or to the content of the database is minimum. 4) Reduce network traffic An operation requiring hundreds of lines of Transact-SQL code can sometimes be performed through a single statement that calls a stored procedure. 5) Provides flexibility and reusability The database developer can change the structure of the database without breaking the Web applications that use it. And it can be reused.

PASSING PARAMETERS TO STORED PROCEDURES

Declaring Parameters includes,

1. The name
2. The data type
3. The default value
4. The direction

The syntax is:

@parameter_name [AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT]

Input and Output parameters Parameters can be input or output. The stored procedure takes input and output both type of parameters. For output parameters, only difference is that we use the OUTPUT clause after the parameter name to specify that it should return a value.  The output clause can be specified by either using the keyword "OUTPUT" or just "OUT". Parameter Types Input- Used by our Web application to send specific data values to a stored procedure. Output - Used by a stored procedure to send specific values back to the calling Web application. InputOutput- Used by a stored procedure to both retrieve information that was sent by our Web application and to send specific values back to the Web application. ReturnValue - Used by a stored procedure to send a return value back to the calling application.

CREATE PROCEDURE  usp_GetStudentAddressByID  @StudentId int,        //Input parameter @Address varchar(255) OUTPUT   //Output parameter AS BEGIN SELECT @Address=Address FROM Student WHERE StudentId=@StudentId; END

CALLING STORED PROCEDURES

A connection and connection string, a command, stored procedure name, and parameters are needed to call a stored procedure. The SqlCommand type can be used to execute stored procedures. First, let the SqlCommand object know which stored procedure to execute and tell the SqlCommand object that it is executing a stored procedure. Parameters can be passed to the stored procedure.

• Create a Command object, set the CommandText property to the name of the stored procedure, and set the CommandType property to CommandType.StoredProcedure.
• Add any needed parameters to the Parameters collection.
• Run the stored procedure by either calling Fill on the DataAdapter or ExecuteNonQuery on the Command object.

cmd.CommandType = CommandType.StoredProcedure; //Specify that executing a stored procedure cmd.CommandText = "userin";    // Specifying stored procedure name cmd.Parameters.AddWithValue("@name", obj.dname);  //Passing parameters to stored procedure. Stored Procedure is a replication of the database, with this replication performance and security issues are resolved. Parameters are the key components of stored procedures.

About Author:

Rithu Marium Thomas is a B.Tech Scholar in Computer Science from College of Engineering, Kidangoor. She hails from South Pampady a small town in the east of Kottayam District, Kerala. Rintu has a passion towards software and is active in updating the latest developments in this domain. She loves to share the information and her views on latest technology in media. Currently, she is doing her Internship program in ASP.NET at IPSR Solutions Ltd, Kottayam.