you create a stored procedure, you need to have an idea of what your query will be doing.  For the purpose of this simple example, we will select everything from the Client table

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Stored Procedures and Triggers

The objective of this lab is to give students experience with developing T-SQL stored procedures. 

Some Examples to get You Started

In this example, we will use the purchasing database from the Advanced SQL Query database.  If you don’t have this database, the script to create it is in the Lab 5 folder on Blackboard. 

Once the database is created, open a new query window and run the following SQL command:

USE Purchasing;

GO

A Simple Stored Procedure

Before you create a stored procedure, you need to have an idea of what your query will be doing.  For the purpose of this simple example, we will select everything from the Client table as follows:

SELECT * FROM client

To create a stored procedure to run this query, we would use the CREATE PROCEDURE statement.  This can also be abbreviated to CREATE PROC.   Here is the code to create a stored procedure out of the above query:--Drop procedure if it exists

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetClientList')

DROP PROCEDURE uspGetClientList

GO

--Create stored procedure

CREATE PROCEDURE uspGetClientList

AS

SELECT * FROM client

GO

Notice that we named the procedure uspGeClientList.  The usp is a naming convention that stands for user stored procedure.  This name is used to call the stored procedure as follows:

EXEC uspGetClientList

Using Input Parameters

When using stored procedures, you will often want to supply parameters to the stored procedure.  For example, let’s say that we have a query with a WHERE clause such as this:

SELECT * FROM client

WHERE full_name = 'Kieran Keller'

Hard coding the client name in the stored procedure defeats the purpose of the stored procedure because you will have to change the stored procedure every time you want to  query a new client.  Because of this, you will want to include client as a parameter in the stored procedure as follows.  To do this, you use the @ symbol to signify the parameter and you give the parameter a name and a data type such as this:

Drop procedure if it exists

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetClientInfo')

DROP PROCEDURE uspGetClientInfo

GO

Create stored procedure

CREATE PROCEDURE uspGetClientInfo @Name nvarchar(50)

AS

SELECT *

FROM Client

WHERE full_name = @Name

GO

Here we have created the parameter @City with a data type of nvarchar(30).  When you want to query a city, you simply run the stored procedure and include the parameter in the EXEC statement as follows:

EXEC uspGetClientInfo @Name = 'Kieran Keller'

--Drop procedure if it exists

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetClientInfo')

DROP PROCEDURE uspGetClientInfo

GO

--Create stored procedure

CREATE PROCEDURE uspGetClientInfo @Name nvarchar(50)

AS

SELECT *

FROM client

WHERE full_name LIKE @Name + '%'

GO

In the above procedures, a parameter must be supplied or the query will not run.  You can get around this by using the NULL option when creating the parameter and the ISNULL term within the query.  For example, in the following stored procedure, if @Name is not supplied, it runs the WHERE clause as Name=Name:

--Drop procedure if it exists

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetClientInfo')

DROP PROCEDURE uspGetClientInfo

GO

--Create stored procedure

CREATE PROCEDURE uspGetClientInfo @Name nvarchar(50) = NULL

AS

SELECT *

FROM client

WHERE full_name = ISNULL(@Name,full_name)

GO

You can also supply multiple parameters to a stored procedure.  Let’s say that we want to select the client with the client name and email.  To do this, we could write the following procedure:

--Drop procedure if it exists

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetClientInfo')

DROP PROCEDURE uspGetClientInfo

GO

Using Output Parameters

You can also set output parameters in stored procedures.  You would do this when you would want to pass a value back from a stored procedure.  For example, we might want to count how many orders that a client has placed.  

SELECT count(*)

FROM client AS c

INNER JOIN purchase as p ON p.client_id = c.client_id

WHERE c.full_name = 'Kieran Keller'

If we want to return this value from the stored procedure as a parameter, we would use the OUTPUT option when declaring the parameter:

--Drop procedure if it exists

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetPurchaseCount')

DROP PROCEDURE uspGetPurchaseCount

GO

 

--Create stored procedure

CREATE PROCEDURE uspGetPurchaseCount @Name nvarchar(50), @PurchaseCount int OUTPUT

AS

SELECT @PurchaseCount = count(*)

FROM client AS c

INNER JOIN purchase as p ON p.client_id = c.client_id

WHERE full_name LIKE '%' ISNULL(@Name,full_name)

 

Notice here that we have added the OUTPUT option to the @AddressCount parameter.  To call this stored procedure, we would call it using the DECLARE term to first declare a parameter @AddressCount.  Then in the EXEC command, we set the @AddressCount parameter equal to the @AddressCount OUTPUT.  We then would SELECT @AddressCount to return the parameter:

 

DECLARE @PurchaseCount int

EXEC uspGetPurchaseCount @Name = 'Kieran Keller', @PurchaseCount = @PurchaseCount OUTPUT

SELECT @PurchaseCount

 

Trigger Example

 

In this example, we will add a new table called purchase_audit.  This table will keep track of all purchase information in a denormalized table that will act as a log for any purchases made for the purpose of tracking potential purchase errors.  We will then create a trigger that will add data to this table when a new purchase is made. 

 

First, let’s create our new table:

CREATE TABLE dbo.purchase_audit

                (client_id int NOT NULL,

                purchase_id int NOT NULL,

                purchase_item_id int NOT NULL,

                product_id int NOT NULL,

                full_name varchar(255) NOT NULL,

                amount int NOT NULL,

                sku varchar(12) NOT NULL,

                name varchar(255) NOT NULL,

                price decimal NOT NULL

CONSTRAINT PK_product_audit PRIMARY KEY CLUSTERED (client_id,purchase_id,purchase_item_id, product_id));

GO

Exercise

The exercise associated with this lab will require you to develop a number of stored procedures for the employees database.  Some of the questions will build on the queries that you created in Lab 4.  For each question, you will have to develop a simple stored procedure based on the supplied requirements. 

 

1. Create a stored procedure to produce a list the current manager for a single department that is supplied by the user. The procedure should be named uspDeptManager and should include the department name as an input parameter.

CREATE PROCEDURE uspDeptManager @Dept nvarchar(40) = NULL

AS

SELECT *

FROM departments

WHERE dept_name LIKE ISNULL(@Dept, dept_name)

AND to_date = ‘9999-01-01’

GO

2. Create a stored procedure to calculate the current average salary for a given position title and return a user defined variable @avgSalary that can be used in subsequent SQL statements. The procedure should be named uspAvgSalaryByPosition and should include the position title as an input parameter.  (hint: this requires two parameters - one input (position title) and one output (average salary)).
3. Create a stored procedure to raise the salary for a user-supplied employee name by a given percentage. The procedure should be named uspEmpRaiseSalary and include two input parameters including a string parameter for the employee name and a double precision parameter for the percentage.  The percentage parameter should be a decimal number between 0 and 1 where for example 20% would be represented as 0.2. 

4. Create a stored procedure to add a new employee to the database based on the answer to question 9 in Lab 4. This stored procedure should be named uspAddNewEmp and should include input parameters to populate the database for all required tables. 

5. Create a trigger that automatically changes the from_date in the dept_manager table such that when a new manager is inserted into the table, the from_date of the old manager is changed to the current date.

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme