1. Launch SSMS, create a new query and enter the comment /* Task 1 */
2. We will need to use the AP database, the Vendors table and the Invoices table. We will start with the select statement we want to use:
USE AP;
GO
SELECT VendorName, InvoiceNumber, InvoiceDate,
InvoiceTotal-CreditTotal-PaymentTotal AS InvoiceAmtDue
FROM Invoices
JOIN Vendors
ON Invoices.VendorID = Vendors.VendorID
WHERE
InvoiceTotal-CreditTotal-PaymentTotal > 0
ORDER BY VendorName;
3. Our next step is to add the CREATE PROC statement before the select
4. Execute the CREATE PROC command and comment out the code
5. To see the results, we need to execute the procedure
EXEC spInvoiceReport;
6. Add the code below and try running both commands (you should notice it doesn't work)
spInvoiceReport;
7. Adjust the code by entering GO followed by spInvoiceReport and EXEC spInvoiceReport. When you run the code, it should work because spInvoiceReport is the first command in the batch
GO
spInvoiceReport
EXEC spInvoiceReport
8. Save the script as chapter15-textbook-assignment.sql
9. Comment out the code.
10. To verify the stored procedure is saved with the database, expand AP, expand Programmability, Expand Store dProcedures and you will see the spInvoiceReport procedure listed.
1. To change the stored procedure requires using the ALTER PROC command
2. Comment out the code and save your changes.
1. To create the stored procedure, we will use the Northwind database, customer table and an input parameter that allows pattern match queries.
2. In the code below, we create the procedure using an input variable set to '%' In the SELECT, we add a WHERE statement with a LIKE clause so anything they type with a % after it will be used in a search.
3. Comment out everything but the EXEC statement and change 'Berg%' to 'Eastern%', then run it again.
4. Comment out the code and save your changes.
1. We are going to use the AP database, Vendors table. We will only have 1 variable, state. We will give the state variable a default value of NULL. We will use an IF... ELSE statement to check the state variable to see if it is NULL or not. If it is null, we will display all states, if it is NOT null, we will add a WHERE clause and limit the results to the selected state
Type:
USE AP;
GO
Then enter the code shown below:
2. Comment out the code and save your changes.
1. To meet their request, we need 2 input variables and 1 return variable.
The input variables will be vendor name and invoice date.
The return variable will be a count of all the invoices for a particular vendor that have a date greater than the date stored in the date variable
To provide an option that lets them enter the date or skip it and use the smallest invoice date, we need to set a default value of NULL for the date. We also need to check the date to see if it is NULL and if it is, we need to set the date variable to the minimum invoice date in the table.
We will be using the Vendors and Invoice tables in the AP database. We will be joining the tables on VendorID. We will be filtering records based on InvoiceDate>=@DateVar AND VendorName Like @VendorVar
We will be counting the number of invoice IDs for a particular vendor and returning that to the execute statement
Enter the stored procedure shown below:
NOTE: The RETURN statement can ONLY return integer values. If you want to return any other type of output, you MUST use an OUTPUT parameter!
2. Execute the code and comment it out.
3. Test the stored procedure using the following EXEC statements:
DECLARE @InvCount int;
EXEC @InvCount = spVendorInvoices
'2010-02-01', 'P%';
SELECT @InvCount AS 'Number of Invoices';
GO
DECLARE @InvCount int;
EXEC @InvCount =
spVendorInvoices '','A%';
PRINT 'Invoice count: ' + CONVERT(varchar,
@InvCount);
GO
4. Comment out the code. The results from the first EXEC will display in the results tab because we used SELECT to display them. The results from the second EXEC will display in the Messages tab, because we used PRINT to display them.
1. We decided to just delete the stored procedure and recreate it since the modification involved additional parameters. Just in case our boss wants another change, we decide to add an if statement above the stored procedure to test if the object exists or not and if it does exist, we will delete it.
Make the modifications shown below in bold and highlighted:
USE AP;
GO
-- Delete the procedure
IF OBJECT_ID('spVendorInvoices') IS NOT
NULL
DROP PROC spVendorInvoices;
GO
-- Create the procedure
CREATE PROC spVendorInvoices
@DateVar smalldatetime = NULL, --optional input parameter
@VendorVar varchar(40) = '%', -- required input parameter
@VendorName varchar(40) OUTPUT --
output variable
AS
IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
DECLARE @InvCount int;
SELECT @InvCount = COUNT(InvoiceID)
,@VendorName=VendorName
FROM Invoices
JOIN
Vendors ON Invoices.VendorID = Vendors.VendorID
WHERE (InvoiceDate
>= @DateVar) AND (VendorName LIKE @VendorVar)
GROUP BY VendorName
RETURN
@InvCount;
GO
-- Run the procedure
DECLARE
@VendorName varchar(40);
DECLARE @invCount int;
EXEC @invCount=spVendorInvoices '','A%',
@VendorName OUTPUT
SELECT @VendorName AS 'Vendor', @invCount AS
'Number of Invoices'
2. After executing the code, comment it out.
1. We decide to add another output parameter to the stored procedure. We copy/paste all the code from the previous query and make the changes highlighted and boldfaced below:
USE AP;
GO
-- delete the old stored procedure
IF
OBJECT_ID('spVendorInvoices') IS NOT NULL
DROP PROC spVendorInvoices;
GO
-- create the new stored procedure
CREATE PROC spVendorInvoices
@DateVar smalldatetime = NULL, --optional input
parameter
@VendorVar varchar(40) = '%', --
required input parameter
@VendorName varchar(40)
OUTPUT, -- output variable
@VendorInvoiceDate smalldatetime OUTPUT --
output variable
AS
IF @DateVar IS NULL
SELECT @DateVar =
MIN(InvoiceDate) FROM Invoices;
DECLARE @InvCount int;
SELECT @InvCount = COUNT(InvoiceID),
@VendorName=VendorName,
@VendorInvoiceDate = @DateVar
FROM Invoices
JOIN Vendors ON
Invoices.VendorID = Vendors.VendorID
WHERE (InvoiceDate >= @DateVar)
AND (VendorName LIKE @VendorVar)
GROUP BY VendorName,
InvoiceDate
RETURN @InvCount;
GO
DECLARE
@VendorName varchar(40);
DECLARE @invCount int;
DECLARE @VendorInvoiceDate
smalldatetime;
EXEC @invCount=spVendorInvoices
'2010-01-01','M%', @VendorName OUTPUT,
@VendorInvoiceDate OUTPUT
SELECT @VendorName AS
'Vendor', @invCount AS 'Number of Invoices',
@VendorInvoiceDate AS 'Minimum Invoice Date';
2. Execute the query, comment out the code and save your changes.
1. Modify the previous query and make the modifications shown below:
USE AP;
GO
IF OBJECT_ID('spVendorInvoices') IS NOT NULL
DROP
PROC spVendorInvoices;
GO
CREATE PROC spVendorInvoices
@DateVar smalldatetime = NULL, --optional input parameter
@VendorVar varchar(40) = '%', -- required input parameter
@VendorName varchar(40) OUTPUT, -- output variable
@VendorInvoiceDate smalldatetime OUTPUT -- output variable
AS
BEGIN TRY
IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate) FROM Invoices;
DECLARE @InvCount int;
SELECT @InvCount =
COUNT(InvoiceID), @VendorName=VendorName, @VendorInvoiceDate = @DateVar
FROM Invoices
JOIN Vendors ON Invoices.VendorID =
Vendors.VendorID
WHERE (InvoiceDate >= @DateVar)
AND (VendorName LIKE @VendorVar)
GROUP BY
VendorName, InvoiceDate
RETURN @InvCount;
END TRY
BEGIN CATCH
PRINT 'An
error occurred.';
PRINT
'Message: ' + CONVERT(varchar, ERROR_MESSAGE());
END CATCH
GO
DECLARE @VendorName
varchar(40);
DECLARE @invCount int;
DECLARE @VendorInvoiceDate
smalldatetime;
EXEC @invCount=spVendorInvoices '2010-01-01','M%',
@VendorName OUTPUT, @VendorInvoiceDate OUTPUT
SELECT @VendorName AS
'Vendor', @invCount AS 'Number of Invoices', @VendorInvoiceDate AS
'Minimum Invoice Date';
2. Save your changes and comment out the script
1. To do that, we will take a look at Northwinds Programmability folder and we will look under Stored Procedures. You should see quite a few listed. Select two and execute them with sp_HelpText
Example:
NOTE: You may have different stored procedures and that is OK.
Just pick 2 and look at the code.
2. After executing the code, comment out the code and save your
changes. Now you know why it is a good idea to add
encryption to your stored procedures :)
1. Enter the heading /* Task #2 */
2. We are going to call the function fnVendorID. It will have one argument, @VendorName and it will return an integer value for the Vendor's ID
USE AP;
GO
CREATE FUNCTION fnVendorID
(@VendorName varchar(50))
RETURNS int
BEGIN
RETURN (SELECT VendorID FROM Vendors WHERE VendorName = @VendorName);
END;
3. After you create the function, comment out the code
4. We are going to run the function in a SELECT statement to retrieve the ID for a Vendor using their name.
5. Change 'IBM' to 'United Parcel Service' and execute the select statement again
6. Comment out the code and save your changes.
1. We decide to create a function which we are naming fnProducts
2. The function will accept 1 argument, ProductID and it will return all columns for that product (because it is returning more than 1 value, it is returning a table). We are not setting a default value because we need the productID to perform the select
3. Create the function shown below to meet our bosses request:
Q: What would you do if you got an error that the function
already exists?
A: Either use ALTER FUNCTION to change it or use DROP FUNCTION followed by CREATE FUNCTION
4. Comment out the code used to create the function and run the SELECT with 3 different product ID's (ProductIDs range from 1 through 77)
Example:
5. Comment out your code and save your changes.
1. Enter the heading /* Task #3 */
2. We decide to create a table for testing purposes from the Northwind customers table
USE Northwind;
SELECT CustomerID,CompanyName,Country INTO
TestCustomers
FROM Customers;
3. Comment out the code. We also need to create a CustomerLog table that will record which customer IDs are added to the TestCustomers table (it will serve as an audit of the table)
CREATE TABLE CustomerLogs(
CustomerId nchar(5),
status varchar(30)
);
4. Comment out the code. With the TestCustomer and CustomerLog tables created, we can create the trigger that will write to the CustomerLog after a new customer is added to the TestCustomer table
CREATE TRIGGER TestCustomers_INSERT ON TestCustomers
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerID nchar(5)
SELECT @CustomerID
=INSERTED.CustomerId
FROM INSERTED
INSERT INTO CustomerLogs
VALUES(@CustomerId,
'Inserted')
END
5. Comment out the code for the Trigger and test it by adding rows to the TestCustomers table
INSERT INTO TestCustomers(CustomerID,CompanyName,Country) VALUES
('ABCWA','ABC Warehouse','USA');
INSERT INTO
TestCustomers(CustomerID,CompanyName,Country) VALUES ('RAALO','Rainbows
and Lollipops','USA');
6. Comment out the insert commands and view the customer log entries
SELECT * FROM CustomerLogs;
You should see 2 entries in the log.
1. The trigger for deletes will be similar to inserts. We can copy/paste the insert trigger and modify it for deletions (Change INSERT to DELETE)
CREATE TRIGGER TestCustomers_DELETE
ON TestCustomers
AFTER
DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerID nchar(5)
SELECT @CustomerID =DELETED.CustomerId
FROM DELETED
INSERT INTO CustomerLogs
VALUES(@CustomerId, 'Deleted')
END
2. After you execute the trigger, comment it out.
3. To test the trigger, we will delete a couple rows from the TestCustomers table
DELETE FROM TestCustomers WHERE CustomerID='AROUT';
DELETE FROM TestCustomers WHERE CustomerID='LAZYK';
SELECT * FROM CustomerLogs;
4. Comment out the code and save your changes.
5. For the Update, if we just wanted to display 'Updated' for the status, the code would be similar to the insert and delete triggers. However, if we want to indicate what was updated, we will need to use some if statements. We are making the assumption that users will only update 1 column in a table rather than all the columns.
The beginning of the trigger looks like the Insert and Delete triggers:
CREATE TRIGGER TestCustomers_UPDATE
ON TestCustomers
AFTER
UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerID nchar(5)
The rest is a little different because we need to create a variable to store the different update status values and we need to check each column to see if it was updated. You will notice that we are checking the INSERTED.CustomerID value (UPDATE and INSERT both use INSERTED to store temporary data) If you wanted UPDATE and INSERT to have the same set of actions, you could code them together.
DECLARE @Action varchar(50)
SELECT @CustomerId = INSERTED.CustomerId
FROM
INSERTED
IF UPDATE(CustomerID)
SET @Action = 'Updated ID'
IF UPDATE(CompanyName)
SET @Action = 'Updated Company'
IF UPDATE(Country)
SET @Action = 'Updated Country'
INSERT INTO CustomerLogs
VALUES(@CustomerId, @Action)
END
Here's the entire update trigger code:
6. Execute the trigger and comment out the code.
7. To test the trigger, you need to update some values:
UPDATE TestCustomers SET CustomerID='RAINB' WHERE CustomerID =
'RAALO';
UPDATE TestCustomers SET CompanyName='Taste the Rainbow'
WHERE CustomerID = 'RAINB';
UPDATE TestCustomers SET Country =
'England' WHERE CustomerID='ABCWA';
8. Comment out the code and run a select to see what is in CustomerLogs
SELECT * FROM CustomerLogs;
9. Save your changes and comment out the code.
1. This can be done in a single trigger:
CREATE TRIGGER TestCustomers_INSERT_UPDATE ON TestCustomers
AFTER
INSERT,UPDATE
AS
UPDATE TestCustomers SET CustomerID =
UPPER(CustomerID)
WHERE CustomerID IN (SELECT CustomerID FROM
INSERTED);
2. After executing the code, comment it out and test the code with the following insert and update statements:
UPDATE TestCustomers SET CustomerID='Folk' WHERE CustomerID =
'FRANS';
UPDATE TestCustomers SET CustomerID='Shopp' WHERE CustomerID
= 'LETSS';
INSERT INTO TestCustomers(CustomerID,CompanyName,Country)
VALUES ('gamer','Gaming Emporium','USA');
GO
SELECT * FROM
CustomerLogs;
SELECT * FROM TestCustomers;
3. Scroll down through the results and look for the codes you changed
4. Save all your changes and comment out the code.
1. We need to create a new empty table based on the Investors table in the Examples database and then we need to make a few adjustments to the columns. Enter the code shown below and then comment it out when you are done.
USE Examples;
SELECT * INTO InvestorTransactions
FROM Investors
WHERE 1=0;
2. The InvestorID column is an identity column which will not work in our transaction table. We don't want a value automatically generated, we need to place the ID that was inserted, deleted or updated into this column. The only way to fix this is to delete the column and then add a new InvestorID column that is NOT an identity column. Enter the code shown below and then comment it out.
ALTER TABLE InvestorTransactions
DROP COLUMN InvestorID;
3. We need to add 3 columns to our table:
InvestorID as an integer
TransDate as a Date
Status as varchar(30)
Enter the code shown below and then comment it out.
ALTER TABLE InvestorTransactions
ADD InvestorID int;
GO
ALTER TABLE InvestorTransactions
ADD TransDate Date;
GO
ALTER TABLE InvestorTransactions
ADD status varchar(30);
4. Create the following triggers:
a) When a new investor is added to Investors, it should automatically be added InvestorTransactions with a status of Inserted
b) When an investor is updated in the Investors table, it should be added to InvestorTransaction with a status of Updated
c) When an investor is deleted from the Investors table, it should be added to the InvestorTransaction table with a status of Deleted
After you have entered and executed, the code, comment it out.
NOTE: The @TransDate variable is set to GETDATE() which retrieves the current date and @status is set to 'Inserted'. All other variables are set to values that the user entered when the row was added.
--Insert Trigger
GO
CREATE TRIGGER Investors_INSERT ON Investors
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FirstName varchar(50)
DECLARE @LastName varchar(50)
DECLARE @Address varchar(50)
DECLARE @City varchar(50)
DECLARE @State char(2)
DECLARE @Zip varchar(10)
DECLARE @Phone varchar(20)
DECLARE @Investments money
DECLARE @NetGain money
DECLARE @InvestorID int
DECLARE @TransDate Date
DECLARE @status varchar(30)
SELECT @LastName = INSERTED.LastName, @FirstName = INSERTED.FirstName, @Address = INSERTED.Address, @City = INSERTED.City,
@Zip = INSERTED.ZipCode, @Phone = INSERTED.Phone, @Investments = INSERTED.Investments,
@NetGain = INSERTED.NetGain, @InvestorID =INSERTED.InvestorID, @TransDate=GETDATE(),@status='Inserted'
FROM INSERTED
INSERT INTO InvestorTransactions VALUES ( @LastName, @FirstName, @Address, @City, @State, @Zip, @Phone, @Investments,
@NetGain, @InvestorID, @TransDate, @status)
END
GO
--Delete Trigger
CREATE TRIGGER Investors_Delete ON Investors
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FirstName varchar(50)
DECLARE @LastName varchar(50)
DECLARE @Address varchar(50)
DECLARE @City varchar(50)
DECLARE @State char(2)
DECLARE @Zip varchar(10)
DECLARE @Phone varchar(20)
DECLARE @Investments money
DECLARE @NetGain money
DECLARE @InvestorID int
DECLARE @TransDate Date
DECLARE @status varchar(30)
SELECT @LastName = DELETED.LastName, @FirstName = DELETED.FirstName, @Address = DELETED.Address, @City = DELETED.City,
@Zip = DELETED.ZipCode, @Phone = DELETED.Phone, @Investments = DELETED.Investments,
@NetGain = DELETED.NetGain, @InvestorID =DELETED.InvestorID, @TransDate=GETDATE(),@status='Deleted'
FROM DELETED
INSERT INTO InvestorTransactions VALUES ( @LastName, @FirstName, @Address, @City, @State, @Zip, @Phone, @Investments,
@NetGain, @InvestorID, @TransDate, @status)
END
--Update Trigger
CREATE TRIGGER Investors_Update ON Investors
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FirstName varchar(50)
DECLARE @LastName varchar(50)
DECLARE @Address varchar(50)
DECLARE @City varchar(50)
DECLARE @State char(2)
DECLARE @Zip varchar(10)
DECLARE @Phone varchar(20)
DECLARE @Investments money
DECLARE @NetGain money
DECLARE @InvestorID int
DECLARE @TransDate Date
DECLARE @status varchar(30)
SELECT @LastName = INSERTED.LastName, @FirstName = INSERTED.FirstName, @Address = INSERTED.Address, @City = INSERTED.City,
@Zip = INSERTED.ZipCode, @Phone = INSERTED.Phone, @Investments = INSERTED.Investments,
@NetGain = INSERTED.NetGain, @InvestorID =INSERTED.InvestorID, @TransDate=GETDATE(),@status='Updated'
FROM INSERTED
INSERT INTO InvestorTransactions VALUES ( @LastName, @FirstName, @Address, @City, @State, @Zip, @Phone, @Investments,
@NetGain, @InvestorID, @TransDate, @status)
END
5. The final step is to test the triggers. We will be adding a row to Investors, updating a column in the new row, and then deleting the new row. All 3 transactions should show up in the InvestorTransactions table and the testing should not have a lasting effect on the Investors table since we are inserting, changing and then deleting one row. The original rows will remain in tact.
--Testing Triggers
-- Original table before changes
SELECT * FROM Investors;
GO
INSERT INTO Investors VALUES('Balbach','Lisa','900 Oak Avenue','Traverse City', 'MI','49696','231-123-1234',400000000,200000000);
UPDATE Investors
SET Address='123 Willow Creek'
WHERE LastName='Balbach' AND FirstName='Lisa';
DELETE FROM Investors WHERE LastName='Balbach' AND FirstName='Lisa';
-- View transactions
SELECT * FROM InvestorTransactions;
-- Original table should be the same as before the changes
SELECT * FROM Investors;
6. Save all your changes and close the file. Upload it to the dropbox in Moodle.