Stored procedures

Stored procedures

A stored procedure is a set of SQL statements used to perform specific tasks. A stored procedure resides on the SQL server and can be executed by any user who has the appropriate permissions. Because the stored procedures reside on the SQL server, you do not need to transfer SQL statements to the server each time you want to perform a task on the server. This reduces the network traffic. When you want to execute a procedure, you only need to transfer the name of the procedure. However, if the procedure takes any parameters, you also need to transfer the parameters along with the procedure name.

You can create a stored procedure by using the Create Procedure statement as follows:

Create Procedure ProcName

As

SQL statements

Return

In this statement:

• ProcName: Represents the name of the stored procedure.
• SQL statements: Represents the set of SQL statements in the stored procedure.
• Return: Represents the end of the procedure. Each stored procedure must end with a Return statement.

After the stored procedure is created, the SQL server scrutinizes it for any errors. The procedure can be executed by using the Execute or Exec keyword, as follows:

Execute ProcName

You can also pass parameters or arguments to a stored procedure to perform a specific task based on the parameter. For example, consider the following procedure that displays the price of a product whose ID is passed as a parameter:

Create Procedure ProductPrice (@id char (4))

As

Select UnitPrice

From Products Where ProductID=@id

Return

This procedure takes a parameter, @id, at the time of execution. To display the price of the product whose ID is "P001", execute this procedure using the following code:

Execute ProductPrice "P001"

Using stored procedures

As mentioned earlier, stored procedures perform database operations more efficiently than the ad hoc SQL queries, because stored procedures are stored on the SQL Server. You simply need to write the procedure's name and the procedure parameters, if any, to execute the stored procedure. When using stored procedure, the traffic is less as compared to passing the complete set of SQL queries to the server. Therefore, the performance is greatly improved. A stored procedure already exists on a SQL Server. Stored procedures can also take parameters that need to be passed while executing them. Parameters make the stored procedures more flexible because they return results based on user input. For example, you can create a stored procedure that takes a product name as a parameter and displays the product details for the specified product.

You can use a stored procedure in your Web application; create a procedure named "DisplayCustomer." The code for the same is given as follows:

Create Procedure DisplayCustomer (@CustID Varchar(4))

As

Select * from Customers

Where CustomerID=@CustID

Return

When you run the application, you can test the code for its functionality. To do so, enter a customer ID in the Customer ID text box and click the Query button. The DataGrid control now displays only one record with the specified customer ID.

https://www.youtube.com/channel/UCKLRUr6U5OFeu7FLOpQ-FSw/videos