You work as the database administrator at an investment company named Domain.com. Domain.com has its headquarters in New York and branch offices in Boston, Atlanta, Miami, and New Orleans. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that is located at headquarters. Certkiller -DB01 contains a large database named CK_Investments. The CK_Investments database stores investment data for each office. You need to implement a parameterized stored procedure that will decrement the AvailableInvestments table when a real estate investment is secured. The stored procedure must accept the OfficeID, InvestmentTypeID, StartDate and EndDate as input parameters.
What stored procedure should you use?
A.
UPDATE AvailableInvestments
SET NumRealEstate = NumRealEstate – 1
WHERE OfficeID = @officeid
AND InvestmentTypeID = @investmenttypeid
AND date BETWEEN @startdate and @enddate
B.
UPDATE AvailableInvestments
SET NumRealEstate = NumRealEstate – 1
WHERE OfficeID = officeid
AND InvestmentTypeID = investmenttypeid
AND date BETWEEN startdate and enddate
C.
UPDATE AvailableInvestments
SET NumRealEstate = NumRealEstate – 1
WHERE OfficeID = @officeid
AND InvestmentTypeID = @investmenttypeid
AND date >= @startdate
AND date <= @enddate
D.
DECLARE @date
WHILE @date < EndDate
UPDATE AvailableInvestments
SET NumRealEstate = NumRealEstate – 1
WHERE OfficeID = @officeid
AND InvestmentTypeID = @investmenttypeid
AND date = @date
BREAK
Explanation:
You need to use the BETWEEN keyword to update records that fall between dates.
Incorrect Answers:
B: Input parameters require the ‘at’ symbol (@) as the first character.
C: You cannot use the compare operators (> < =) with a datetime or smalldatetime column.
D: Executing the change in a loop is not as efficient as executing that changes in a single statement.