Question

Increase all of the listing prices by 5% for all listings under $500,000 and 10% for all listings $500,000 and higher. Update the listings table with the new prices.

update LISTING
set LISTING_PRICE = LISTING_PRICE +
case
  when LISTING_PRICE < 500000 then (LISTING_PRICE*5)/100
  when LISTING_PRICE >= 500000 then (LISTING_PRICE*10)/100
  else 0
end

-- Add 30 days to the date expires for all listings.

update LISTING set DATE_EXPIRES = DATEADD(dd, 30, DATE_EXPIRES)

-- Add "Listing updated on [current date]." to the remarks. Replace [current date] with the current systems date. Do not replace the remarks currently in the table. Add these remarks to the remarks already in the table.

update LISTING set REMARKS=CONCAT(REMARKS ," ","Listing updated on ",GETDATE())

-- Return the following information from the listings table from the stored procedure to display the information in the new real estate app: address, city, state, zip, updated listing price, updated date expires, and updated remarks.

CREATE PROCEDURE DISPLAYLISTING
AS
BEGIN
select ADDRESS,CITY,STATE,ZIP,LISTING_PRICE,DATE_EXPIRES,REMARKS from LISTING;
END;

-- Call and run the stored procedure to make the appropriate updates and return the proper results.

In order to execute the stored procedures we use the following SQL script :

EXEC DISPLAYLISTING;

We can update and display the information from the listings table using following stored procedure as :

CREATE PROCEDURE DISPLAYLISTING
AS
BEGIN

update LISTING
set LISTING_PRICE = LISTING_PRICE +
case
  when LISTING_PRICE < 500000 then (LISTING_PRICE*5)/100
  when LISTING_PRICE >= 500000 then (LISTING_PRICE*10)/100
  else 0
end;

update LISTING set DATE_EXPIRES = DATEADD(dd, 30, DATE_EXPIRES);
  
update LISTING set REMARKS=CONCAT(REMARKS ," ","Listing updated on ",GETDATE());

select ADDRESS,CITY,STATE,ZIP,LISTING_PRICE,DATE_EXPIRES,REMARKS from LISTING;

END;

For this lab exercise you will be working with and modifying the stored procedure you created in the last module. In your stored procedure, the first three requirements performed updates to your database and the last two requirements returned the data that was updated. Perform the following:

1. Enclose the code for the first requirement in its own single transaction with the proper commit and rollback functions.

2. Enclose the code for both the second and third requirements in one single transaction with the proper commit and rollback functions.

3. Call and run the stored procedure to make the appropriate updates and return the proper results. Take the appropriate screenshots to show this working and insert into your screenshot document.

4. In the second transaction you created that included the code for the second and third requirements - create an error in the middle of the transaction between the code for the two requirements. For example, you can add an insert or an update statement that uses a field that does not exist. This would cause an error. When you run it, the error should cause the entire transaction to rollback. Because of this error, no changes should be made by this transaction. This is one way of testing your rollback code.

5. Call and run the stored procedure a second time to make the appropriate updates and return the proper results. The first transaction should run without any issues but the second transaction should rollback any changes it made.

You are going to take the stored procedure you created in the lab exercise from the last module and create two transactions within it. The first transaction will contain the first requirement from that module's lab exercise. The second transaction will contain the second and third requirements from that module's lab exercise. You will run it and take screenshots to show it working properly. Then you will purposely insert code to create an error in the middle of the second transaction, in between the two items to cause the transaction to fail and rollback any changes. You will run it again and take screenshots to show your rollback working properly.

Answer 1

Initial entry in database:

STORED PROCEDURE:

CREATE PROCEDURE DISPLAYLISTING
AS
BEGIN
begin try
        begin transaction;
                update LISTING
                set LISTING_PRICE = LISTING_PRICE +
                case
                  when LISTING_PRICE < 500000 then (LISTING_PRICE*5)/100
                  when LISTING_PRICE >= 500000 then (LISTING_PRICE*10)/100
                  else 0
                end;
        commit transaction;
end try 
begin catch  
        if @@trancount > 0
        begin
                rollback transaction;
        end
end catch 

begin try
        begin transaction;
                update LISTING set DATE_EXPIRES = DATEADD(dd, 30, DATE_EXPIRES);  
                update LISTING set REMARKS=CONCAT(REMARKS ,' ','Listing updated on ',GETDATE());
        commit transaction;
end try 
begin catch  
        if @@trancount > 0
        begin
                rollback transaction;
        end
end catch 
select ADDRESS,CITY,STATE,ZIP,LISTING_PRICE,DATE_EXPIRES,REMARKS from LISTING;
END;

Snippet of SP:

Execution Result/Output:

Stored Procedure with error:

ALTER PROCEDURE DISPLAYLISTING
AS
BEGIN
begin try
        begin transaction;
                update LISTING
                set LISTING_PRICE = LISTING_PRICE +
                case
                  when LISTING_PRICE < 500000 then (LISTING_PRICE*5)/100
                  when LISTING_PRICE >= 500000 then (LISTING_PRICE*10)/100
                  else 0
                end;
        commit transaction;
end try 
begin catch  
        if @@trancount > 0
        begin
                rollback transaction;
        end
end catch 

begin try
        begin transaction;
                update LISTING set DATE_EXPIRES = DATEADD(dd, 30, DATE_EXPIRES);  
                declare @SqlQuery nvarchar(max);
                set @SqlQuery = N'update LISTING set TIME = GETDATE()';
                EXEC @SqlQuery;
                update LISTING set REMARKS=CONCAT(REMARKS ,' ','Listing updated on ',GETDATE());
        commit transaction;
end try 
begin catch  
        if @@trancount > 0
        begin
                rollback transaction;
        end
end catch 
select ADDRESS,CITY,STATE,ZIP,LISTING_PRICE,DATE_EXPIRES,REMARKS from LISTING;
END;

Snippet of code:

Execution Result/Output: