You work as a SQL Server 2012 database developer at ABC.com. ABC.com has a database
named SalesDB with tables named Customer and Orders. The Customer and Orders tables were
created using the following Transact-SQL code:
CREATE TABLE SalesDB.Customers
(
CustomerID int NOT NULL PRIMARY KEY,
CustomerName nvarchar (250) NOT NULL,
Address1 nvarchar (100) NOT NULL,
Address2 nvarchar (100) NULL,
City nvarchar (50) NOT NULL,
State nvarchar (50) NOT NULL,
Zip varchar (5) NOT NULL,
Phone varchar (10) NOT NULL
)
GO
CREATE TABLE SalesDB.Orders
(
OrderID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
ShipDate datetime NOT NULL,
CustomerID int NOT NULL,
SalesRepID int NOT NULL
)
GO
You are developing a stored procedure named OrdersByDate that returns the OrderID,

CustomerID, CustomerName and OrderDate. The stored procedure will take a parameter named
@date that uses the int datatype. The @date parameter will be used to filter the result set based
on the OrderDate column in the Orders table.
How would you create the stored procedure?

A.
CREATE PROCEDURE OrdersByDate
@date int
AS
SELECT OrderID, CustomerID, CustormerName, OrderDate
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE OrderDate = CONVERT(datetime,@date)

B.
CREATE PROCEDURE OrdersByDate
@date int
AS
SELECT OrderID, CustomerID, CustormerName, OrderDate
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE OrderDate =@date

C.
CREATE PROCEDURE OrdersByDate
@date int
AS
SELECT OrderID, CustomerID, CustormerName, OrderDate
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE OrderDate = CAST(@date AS datetime)

D.
CREATE PROCEDURE OrdersByDate
@date int
AS
SELECT OrderID, CustomerID, CustormerName, OrderDate
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId
WHERE OrderDate = PARSE(@date AS datetime)

Explanation: