Modifying a Stored Procedure

Modifying a Stored Procedure

If you need to modify an existing stored procedure, simply replace the CREATE with ALTER. Let's add a space in between "Latest" and "Tasks" (i.e. make it "Latest Tasks") and add the Description field:

ALTER PROCEDURE LatestTasks @Count int AS

SET ROWCOUNT @Count

SELECT TaskName AS "Latest Tasks", Description, DateCreated

FROM Tasks

ORDER BY DateCreated DESC

Execute a Stored Procedure

Executing a Stored Procedure

Now that if you've created your stored procedure, any time you want to execute it, you need to call it using either EXECUTE or EXEC. If the stored procedure requires parameters you provide those after the procedure name. Like this:

EXECUTE LatestTasks

EXEC LatestTasks

EXEC LatestTasks @Count = 5

Example : In the following example, we execute the stored procedure twice at the same time. The first time we call it, we pass in a @Count of 3. The second time we pass in a value of 5.

The screenshot shows that by passing the paramater (and a value), the stored procedure returns results based on the value that we provide. The top result set returns 3 rows because we passed in a value of 3. The second result set returns 5 rows because we provided a value of 5:

Create a Stored Procedure

Create a Stored Procedure

To create a stored procedure, you need to use the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure. If your stored procedure is going to accept parameters, they need to be included after the name.

CREATE PROCEDURE myStoredProcedure AS

...

OR

CREATE PROCEDURE myStoredProcedure @{Parameter Name} {data type} AS

...

Example : The following code creates a stored procedure called "LatestTasks". It accepts a parameter called @Count. Whenever you call this stored procedure, you pass the @Count parameter along with a number, which determines how many rows you want returned. Here's the code:

CREATE PROCEDURE LatestTasks @Count int AS

SET ROWCOUNT @Count

SELECT TaskName AS LatestTasks, DateCreated

FROM Tasks

ORDER BY DateCreated DESC

Run this code in the SQL Server Management Studio and you'll see that it gets created under the Stored Procedures node as "LatestTasks".

Benefits of Stored Procedures

Benefits of Stored Procedures

1. Modular programming : You can write a stored procedure once, then call it again and again, from different parts of an application (and even from multiple applications).
2. Performance :Stored procedures provide faster code execution and reduce network traffic.

• Faster execution: Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimze your SQL code every time it runs.
• Reduced network traffic: If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. This is especially true if you have hundreds of lines of SQL code and/or you have lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.

3. Security : Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way.

Adding a Column to a Table in SQL Server

Adding a Column to a Table in SQL Server

To add a new column to a table, follow this formula:

ALTER TABLE TableName

ADD ColumnName Properties

Here is an example:

ALTER TABLE StaffMembers

ADD Address varchar(100) NULL

When this code is executed, a new column named Address, of type varchar, with a limit of 100 characters, and that allows empty entries, would be added to the table named StaffMembers.